public static void exportsexcel(object objextoEl)
{
datatoExport dat = (datatoExport)objextoEl;
// DataTable table, string filename
DataTable dt = dat.dataGrid1;
string filename = dat.filename;
// SpreadsheetDocument spse = SpreadsheetDocument.Create(filename, SpreadsheetDocumentType.Workbook);
//Exporting to Excel
DataSet ds = new DataSet();
ds.Tables.Add(dt);
//ExcelDocument xls = new ExcelDocument();
//xls.easy_WriteXLSFile_FromDataSet("datatable.xls", ds,
// new ExcelAutoFormat(DocumentFormat.OpenXml.Wordprocessing.Styles.AUTOFORMAT_EASYXLS1), "DataTable");
//string folderPath = "C:\\Excel\\";
//if (!Directory.Exists(folderPath))
//{
// Directory.CreateDirectory(folderPath);
//}
try
{
//using (XLWorkbook wb = new XLWorkbook())
//{
ExportToExcel.ExportToExcel.ExportDataSet(ds, filename);
// wb.Worksheets.Add(ds);
// wb.SaveAs(filename);
//}
MessageBox.Show(filename + " exported !", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString(), "Thông báo không excel export được ! ", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
}
public static void exportsexcel(object objextoEl)
{
datatoExport dat = (datatoExport)objextoEl;
// DataTable dataTble = new DataTable();
// DataSet dataSet, string outputPath
// Create the Excel Application object
cExcel.ApplicationClass excelApp = new cExcel.ApplicationClass();
// Create a new Excel Workbook
cExcel.Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
System.Data.DataTable dt = dat.dataGrid1;
var tittle = dat.tittle;
var filename = dat.filename;
// Copy the DataTable to an object array
object[,] rawData = new object[dt.Rows.Count + 1, dt.Columns.Count];
// Copy the column names to the first row of the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
rawData[0, col] = dt.Columns[col].ColumnName;
}
// Copy the values to the object array
for (int col = 0; col < dt.Columns.Count; col++)
{
for (int row = 0; row < dt.Rows.Count; row++)
{
rawData[row + 1, col] = dt.Rows[row].ItemArray[col];
}
}
// Calculate the final column letter
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
// Create a new Sheet
cExcel.Worksheet excelSheet = (cExcel.Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, cExcel.XlSheetType.xlWorksheet);
// excelSheet.Name = dt.TableName;
// Fast data export to Excel
string excelRange = string.Format("A1:{0}{1}",
finalColLetter, dt.Rows.Count + 1);
excelSheet.get_Range(excelRange, Type.Missing).Value2 = rawData;
// Mark the first row as BOLD
((cExcel.Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;
// Save and Close the Workbook
excelWorkbook.SaveAs(filename, cExcel.XlFileFormat.xlWorkbookDefault, Type.Missing, Type.Missing, Type.Missing, Type.Missing, cExcel.XlSaveAsAccessMode.xlExclusive, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
excelWorkbook.Close(true, Type.Missing, Type.Missing);
// xlApp.Quit();
//excelWorkbook.SaveAs(outputPath, cExcel.XlFileFormat.xlWorkbookNormal, Type.Missing,
// Type.Missing, Type.Missing, Type.Missing, cExcel.XlSaveAsAccessMode.xlExclusive,
// Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//excelWorkbook.Close(true, Type.Missing, Type.Missing);
//excelWorkbook = null;
// Release the Application object
excelApp.Quit();
excelApp = null;
// Collect the unreferenced objects
GC.Collect();
GC.WaitForPendingFinalizers();
MessageBox.Show(filename + " exported !", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
public static void exportsexcelold(object objextoEl)
{
datatoExport dat = (datatoExport)objextoEl;
// DataTable dataTble = new DataTable();
// DataSet dataSet, string outputPath
// Create the Excel Application object
cExcel.ApplicationClass excelApp = new cExcel.ApplicationClass();
// Create a new Excel Workbook
cExcel.Workbook excelWorkbook = excelApp.Workbooks.Add();
int sheetIndex = 0;
System.Data.DataTable DataTable = dat.dataGrid1;
var tittle = dat.tittle;
var ExcelFilePath = dat.filename;
// Copy the DataTable to an object array
// object[,] Arr = new object[dt.Rows.Count, dt.Columns.Count];
cExcel.Worksheet Worksheet = (cExcel.Worksheet)excelWorkbook.Sheets.Add(
excelWorkbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, cExcel.XlSheetType.xlWorksheet);
#region
try
{
int ColumnsCount;
if (DataTable == null || (ColumnsCount = DataTable.Columns.Count) == 0)
{
throw new Exception("ExportToExcel: Null or empty input table!\n");
}
// load excel, and create a new workbook
// Microsoft.Office.Interop.Excel.Application Excel = new Microsoft.Office.Interop.Excel.Application();
// Excel.Workbooks.Add();
ColumnsCount = DataTable.Columns.Count;
int RowsCount = DataTable.Rows.Count;
// single worksheet
// Microsoft.Office.Interop.Excel._Worksheet Worksheet = Excel.ActiveSheet;
object[] Header = new object[ColumnsCount];
// column headings
for (int i = 0; i < ColumnsCount; i++)
{
Header[i] = DataTable.Columns[i].ColumnName;
}
Microsoft.Office.Interop.Excel.Range HeaderRange = Worksheet.get_Range((Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, 1]), (Microsoft.Office.Interop.Excel.Range)(Worksheet.Cells[1, ColumnsCount]));
HeaderRange.Value = Header;
HeaderRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.LightGray);
HeaderRange.Font.Bold = true;
// DataCells
int downloadtimes = 0;
int rowdown = 30000;
int totalrowcount = 0;
int maxrows = 0;
do
{
downloadtimes++;
totalrowcount = rowdown * downloadtimes;
object[,] Cells = new object[rowdown, ColumnsCount];
//for (int j = 0; j < RowsCount; j++)
// for (int i = 0; i < ColumnsCount; i++)
// Cells[j, i] = DataTable.Rows[j][i
if (RowsCount >= (downloadtimes) * rowdown)
{
maxrows = (downloadtimes) * rowdown;
}
else
{
maxrows = RowsCount;
}
for (int j = (downloadtimes - 1) * rowdown; j < maxrows; j++)
{
for (int i = 0; i < ColumnsCount; i++)
{
Cells[j, i] = DataTable.Rows[j][i];
}
}
Worksheet.get_Range("A" + GetExcelColumnName((downloadtimes - 1) * rowdown + 1) + ":" + GetExcelColumnName(ColumnsCount - 1) + (RowsCount + 1).ToString(), Type.Missing).Value2 = Cells;
} while (maxrows == RowsCount);
if (ExcelFilePath != null && ExcelFilePath != "")
{
try
{
Worksheet.SaveAs(ExcelFilePath, cExcel.XlFileFormat.xlOpenXMLWorkbook);
excelApp.Quit();
excelApp = null;
MessageBox.Show(ExcelFilePath + " exported !", "Thông báo", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: Excel file could not be saved! \n"
+ ex.Message);
}
}
else // no filepath is given
{
excelApp.Visible = true;
}
}
catch (Exception ex)
{
throw new Exception("ExportToExcel: \n" + ex.Message);
}
#endregion
}
