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 }