For some reason we have a user where there are records that are duplicated. We are exporting about 500,000 records with 40 columns.
The user is exporting to XLXS and we are using version 8
let me know if you have any ideas why this is happening.
Below is the code I used:
public void ExportToExcel(object parameter, string sFileName)
{
if (GLList == null || GLList.Count == 0)
return;
sFileName = “GL_Search” + SelectedExportFormat;
string sPath = System.Environment.GetFolderPath(System.Environment.SpecialFolder.Personal) + “\”;
sPath = Path.Combine(sPath, “GL_Search”);
if (!System.IO.Directory.Exists(sPath ))
{
System.IO.Directory.CreateDirectory(sPath);
}
base.MessageString = “Generating file”;
sPath = FileHelper.GetUniqueFileName(sFileName, sPath);// Path.Combine(sPath, sFile);
ArrayList visibleColumns = new ArrayList();
GridColumnCollection gridColumns = ((GridControl)parameter).Columns;
foreach(GridColumn column in gridColumns)
{
if ( column.Visible )
visibleColumns.Add(column);
}
string[] clmAry = new string[visibleColumns.Count];
string[] headerAry = new string[visibleColumns.Count];
int columnCount = 0;
foreach( GridColumn column in visibleColumns )
{
clmAry[columnCount] = column.FieldName;
headerAry[columnCount] = column.Header.ToString();
columnCount++;
}
string[] dtArry = new string[]
{
“JOURNAL_DATE”,
“system_date”,
“POSTED_DATE”
};
string[] amtArry = new string[]
{
“Amount”,
“AbsAmount”
};
string[] strArry = new string[]
{
“operating_unit”,
“Account_No”
};
Workbook workbook = new Workbook();
workbook.Settings.Region = Aspose.Cells.CountryCode.USA;
Worksheet worksheet = workbook.Worksheets[0];
worksheet.Name = “Data”;
ImportTableOptions tableOptions = new ImportTableOptions();
tableOptions.ConvertNumericData = false;
worksheet.Cells.ImportCustomObjects(this.GLList, clmAry, true, 0, 0, ((System.Collections.ICollection)this.GLList).Count, true, “MM/dd/yyyy”, false);
string sourceData = “=Data!A1:AN” + this.GLList.Count.ToString();
Aspose.Cells.Style style = new Aspose.Cells.Style();
string currencySymbol = System.Threading.Thread.CurrentThread.CurrentCulture.NumberFormat.CurrencySymbol.ToString();
string[] array = amtArry;
for (int i = 0; i < array.Length; i++)
{
string column = array[i];
int x = System.Array.FindIndex(clmAry, (string r) => r.ToString() == column);
Range range = worksheet.Cells.CreateRange(1, x, this.GLList.Count, 1);
style.Custom = “#,##0.00;[Red]-#,##0.000”;
range.SetStyle(style);
}
array = dtArry;
for (int i = 0; i < array.Length; i++)
{
string column = array[i];
int x = System.Array.FindIndex(clmAry, (string r) => r.ToString() == column);
Range range = worksheet.Cells.CreateRange(1, x, this.GLList.Count, 1);
style.Number = 14;
range.SetStyle(style);
}
//Set the Header values
for (int i = 0; i < headerAry.Length; i++)
{
Cell cell = worksheet.Cells[CellsHelper.CellIndexToName(0, i)];
cell.PutValue(headerAry[i]);
}
string[] textArry = new string[]
{
“Account_No”, “account_no_92”, “operating_unit”, “index_code”, “BUSINESS_UNIT”, “business_unit_92”, “Ref_No”, “index_code”,“Affiliate_BU”,“Affiliate_BU_92”,“pymt_memo”,“TJ_GL_KEY”,“dc_batch”,“ci_batch”,“pa_batch”,“deposit_no”
};
if (this.SelectedExportFormat.ToLower() == “.xlsx”)
{
workbook.Save(sPath, SaveFormat.Xlsx);
}
else
{
workbook.Save(sPath, SaveFormat.CSV);
}