Need to Export Duplicate column of Excel file

Hello Aspose Team,


I am using license version of AsposeCell. I am using to export excel data into DataTable.
In my excel file there are more that 50 columns and some of them are duplicates also.
I need to export all data with duplicate column headers.
AspseCell giving error message that column name already belong to data tabel.

Kindly help me.

Hi Ashish,


Thank you for contacting Aspose support.

This is actually the limitation of the System.Data.DataTable that it could not have more than one column with same name. While using the Aspose.Cells APIs to export the data in DataTable, this limitation holds. However, you can avoid it using any of the following approaches.

  • Do not pass the parameter exportColumnName as true to the ExportDataTable method. This way, the exported DataTable will not have the column header (column names).
  • If above solution is not desirable then alternative solution is to first create the DataTable with column names of your choice and then fill it with data as demonstrated below.

C#

DataTable dt = new DataTable();
dt.Columns.Add(“ID”, typeof(string));
dt.Columns.Add(“Name”, typeof(string));
dt.Columns.Add(“Class”, typeof(string));
dt.Columns.Add(“Name1”, typeof(string));

//please create columns according to the requirement

Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(“D:/book1.xlsx”);
Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0];
worksheet.Cells.ExportDataTable(dt, 1, 0, worksheet.Cells.MaxDataRow + 1, false);

I have tried to implement code suggested by you and its giving me error.


Below is the code.

protected DataTable ToDataTable(string filepath, string sheetName, string whereClause)
{
DataTable dt = new DataTable();
dt.Columns.Add(“Pop Code”, typeof(string));
dt.Columns.Add(“Bulked”, typeof(string));
dt.Columns.Add(“Exer Type”, typeof(string));
dt.Columns.Add(“Optionee ID”, typeof(string));
dt.Columns.Add(“Optionee ID”, typeof(string));
dt.Columns.Add(“First Name”, typeof(string));
dt.Columns.Add(“Last Name”, typeof(string));
dt.Columns.Add(“Tax Code”, typeof(string));
dt.Columns.Add(“Plan Id”, typeof(string));
dt.Columns.Add(“Grant Date”, typeof(string));
dt.Columns.Add(“Grant Type”, typeof(string));
dt.Columns.Add(“Grant Price”, typeof(string));
dt.Columns.Add(“Shares Granted”, typeof(string));
dt.Columns.Add(“Exercise Fmv”, typeof(string));
dt.Columns.Add(“Options Exercised”, typeof(string));
dt.Columns.Add(“Sale Price”, typeof(string));
dt.Columns.Add(“SharesSold”, typeof(string));
dt.Columns.Add(“ShareProceeds”, typeof(string));
dt.Columns.Add(“Commissions”, typeof(string));
dt.Columns.Add(“SEC Fee”, typeof(string));
dt.Columns.Add(“Grant Code 1”, typeof(string));
dt.Columns.Add(“Grant Text 1”, typeof(string));
dt.Columns.Add(“Grant Text 2”, typeof(string));
dt.Columns.Add(“Exercise Id”, typeof(string));
dt.Columns.Add(“Exercise Date”, typeof(string));
dt.Columns.Add(“Options Exercised1”, typeof(string));
dt.Columns.Add(“Shares Outstanding”, typeof(string));
dt.Columns.Add(“Vest ID”, typeof(string));
dt.Columns.Add(“Ytd Supplemental Income”, typeof(string));
dt.Columns.Add(“YTD FICA”, typeof(string));
dt.Columns.Add(“User Text 4”, typeof(string));
dt.Columns.Add(“Term Id”, typeof(string));
dt.Columns.Add(“Term date”, typeof(string));
dt.Columns.Add(“Shares Exercisable”, typeof(string));

int startRow = 0;
string range = string.Empty;

//Header for data is at 5th row
range = “A5:AG”;
startRow = 4;

//Instantiate License class and call its SetLicense method to use the license
var lic = new License();
lic.SetLicense(“EYSMS.Gateway.Aspose.Cells.lic”);

//Creating a file stream containing the Excel file to be opened
FileStream fstream = File.OpenRead(filepath); //new FileStream(fileName, FileMode.Open);

//Instantiating a Workbook object
//Opening the Excel file through the file stream
Workbook workbook = new Workbook(fstream);

//Accessing the first worksheet in the Excel file
Worksheet worksheet = workbook.Worksheets[sheetName];

//Exporting the contents of rows and columns starting from 1st cell to DataTable
DataTable datatable= worksheet.Cells.ExportDataTable(dt, 1, 0, worksheet.Cells.MaxDataRow + 1, false);
//Closing the file stream to free all resources
fstream.Close();

//Applying filter criteria.
if (!string.IsNullOrEmpty(whereClause))
{
DataView dvData = new DataView(dt);
dvData.RowFilter = whereClause;
dt = dvData.ToTable();
}
return dt;
}

And Error message is : Cannot implicitly convert type ‘void’ to System.Data.DataTable.

Kindly look into that.

Attaching screen shot of code in which its telling that this method has been Obsolete.


Hi Ashish,


Sorry, the method suggested earlier in this thread has been depreciated. Please use the ExportDataTable method as follow.

C#

worksheet.Cells.ExportDataTable(1, 0, worksheet.Cells.MaxDataRow + 1, worksheet.Cells.MaxDataColumn + 1, new ExportTableOptions() { DataTable = dt, ExportColumnName = false});
Ashish Krishan:
//Exporting the contents of rows and columns starting from 1st cell to DataTable
DataTable datatable= worksheet.Cells.ExportDataTable(dt, 1, 0, worksheet.Cells.MaxDataRow + 1, false);

And Error message is : Cannot implicitly convert type 'void' to System.Data.DataTable.

I believe the above statement is the cause to compilation error. Please note, the depreciated method ExportDataTable with following signature does not have a return type therefore you can assign its output to any object.

ExportDataTable(DataTable table, int firstRow, int firstColumn, int totalRows, int totalColumns, bool exportColumnName)