First: Use Microsoft.Office.Interop.Excel.dll
First you need to install excel for office, and then find the Microsoft.Office.Interop.Excel.dll component to add to the reference.
public void ExportExcel(DataTable dt) { if (dt != null) { Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application(); if (excel == null) { return; } //Set to invisible, the operation is executed in the background. true If it does, it will open. Excel excel.Visible = false; //Set to full screen display when open //excel.DisplayFullScreen = true; //Initialization Workbook Microsoft.Office.Interop.Excel.Workbooks workbooks = excel.Workbooks; //Add a new workbook. Add()Method can also pass in parameters directly true Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); //Also add a new workbook, but the save dialog will pop up //Microsoft.Office.Interop.Excel.Workbook workbook = excel.Application.Workbooks.Add(true); //Add a new one Excel surface(sheet) Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1]; //Set the name of the table worksheet.Name = dt.TableName; try { //Create a cell Microsoft.Office.Interop.Excel.Range range; int rowIndex = 1; //The starting subscript of a row is 1 int colIndex = 1; //The starting subscript of a column is 1 //Setting column names for (int i = 0; i < dt.Columns.Count; i++) { //Set the first row, column name worksheet.Cells[rowIndex, colIndex + i] = dt.Columns[i].ColumnName; //Get each cell of the first row range = worksheet.Cells[rowIndex, colIndex + i]; //Set the internal color of the cell range.Interior.ColorIndex = 33; //font-weight range.Font.Bold = true; //Set it to black range.Font.Color = 0; //Set to Song Style range.Font.Name = "Arial"; //Set font size range.Font.Size = 12; //horizontally range.HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; //Vertical Centralization range.VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter; } //Skip the first line, which writes the column name rowIndex++; //Write data for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { worksheet.Cells[rowIndex + i, colIndex + j] = dt.Rows[i][j].ToString(); } } //Set all column widths to automatic column widths //worksheet.Columns.AutoFit(); //Set all cell column widths to automatic column widths worksheet.Cells.Columns.AutoFit(); //worksheet.Cells.EntireColumn.AutoFit(); //Whether to prompt, if you want to delete a sheet Page, first set this to fasle. excel.DisplayAlerts = false; //Save the written data, which has not yet been saved to disk workbook.Saved = true; //Setting Export File Path string path = HttpContext.Current.Server.MapPath("Export/"); //Set the path and name of the new file string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; //create a file FileStream file = new FileStream(savePath, FileMode.CreateNew); //Close the release stream or you will not be able to write data file.Close(); file.Dispose(); //Save to the specified path workbook.SaveCopyAs(savePath); //You can also add the following methods to output to the browser to download FileInfo fileInfo = new FileInfo(savePath); OutputClient(fileInfo); } catch(Exception ex) { } finally { workbook.Close(false, Type.Missing, Type.Missing); workbooks.Close(); //Close and quit excel.Quit(); //release COM object Marshal.ReleaseComObject(worksheet); Marshal.ReleaseComObject(workbook); Marshal.ReleaseComObject(workbooks); Marshal.ReleaseComObject(excel); worksheet = null; workbook = null; workbooks = null; excel = null; GC.Collect(); } } }
public void OutputClient(FileInfo file) { HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; //Export to .xlsx If the format is not available, try this. //HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xlsx", DateTime.Now.ToString("yyyy-MM-dd-HH-mm"))); HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString()); HttpContext.Current.Response.WriteFile(file.FullName); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.Close(); }
The performance of the first method is really not complimentary, and there are too many limitations. First, you must install office (if not on your computer), and you need to specify the path to save the file when you export it. It can also be exported to the browser for download, provided that the written data has been saved.
Second: Use Aspose.Cells.dll
This Aspose.Cells is an Excel-oriented control launched by Aspose, which does not rely on Office, commercial software and fees.
Reference: http://www.cnblogs.com/xiaofengfeng/archive/2012/09/27/2706211.html#top
public void ExportExcel(DataTable dt) { try { //Gets the physical path of the specified virtual path string path = HttpContext.Current.Server.MapPath("DLL/") + "License.lic"; //read License file Stream stream = (Stream)File.OpenRead(path); //register License Aspose.Cells.License li = new Aspose.Cells.License(); li.SetLicense(stream); //Create A Workbook Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(); //Create a sheet surface Aspose.Cells.Worksheet worksheet = workbook.Worksheets[0]; //Set up sheet Table Name worksheet.Name = dt.TableName; Aspose.Cells.Cell cell; int rowIndex = 0; //The starting subscript of a row is 0 int colIndex = 0; //The starting subscript of a column is 0 //Setting column names for (int i = 0; i < dt.Columns.Count; i++) { //Get each cell of the first row cell = worksheet.Cells[rowIndex, colIndex + i]; //Setting column names cell.PutValue(dt.Columns[i].ColumnName); //Setting fonts cell.Style.Font.Name = "Arial"; //Set font bold cell.Style.Font.IsBold = true; //Set font size cell.Style.Font.Size = 12; //Setting font color cell.Style.Font.Color = System.Drawing.Color.Black; //Setting Background Colors cell.Style.BackgroundColor = System.Drawing.Color.LightGreen; } //Skip the first line, which writes the column name rowIndex++; //Write data for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { cell = worksheet.Cells[rowIndex + i, colIndex + j]; cell.PutValue(dt.Rows[i][j]); } } //Automatic Column Width worksheet.AutoFitColumns(); //Setting Export File Path path = HttpContext.Current.Server.MapPath("Export/"); //Set the path and name of the new file string savePath = path + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xlsx"; //create a file FileStream file = new FileStream(savePath, FileMode.CreateNew); //Close the release stream or you will not be able to write data file.Close(); file.Dispose(); //Save to the specified path workbook.Save(savePath); //Or use the following method to output to the browser to download. //byte[] bytes = workbook.SaveToStream().ToArray(); //OutputClient(bytes); worksheet = null; workbook = null; } catch(Exception ex) { } }
public void OutputClient(byte[] bytes) { HttpContext.Current.Response.Buffer = true; HttpContext.Current.Response.Clear(); HttpContext.Current.Response.ClearHeaders(); HttpContext.Current.Response.ClearContent(); HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", DateTime.Now.ToString("yyyy-MM-dd-HH-mm"))); HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("GB2312"); HttpContext.Current.Response.BinaryWrite(bytes); HttpContext.Current.Response.Flush(); HttpContext.Current.Response.Close(); }
The performance of the second method is good, and the operation is not complicated. It can set the path to save files when exporting, and also can be saved as stream output to browser to download.
Third: Microsoft.Jet.OLEDB
This method operates on Excel similar to operating on a database. Let's start with the connection string:
// Excel 2003 Version Connection String string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=C:/xxx.xls;Extended Properties='Excel 8.0;HDR=Yes;IMEX=2;'"; // Excel 2007 Connection string of the above version string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:/xxx.xlsx;Extended Properties='Excel 12.0;HDR=Yes;IMEX=2;'";
Provider: Driver name
Data Source: Specify the path to the Excel file
Extended Properties: Excel 8.0 for Excel 2000 and above; Excel 12.0 for Excel 2007 and above.
HDR: Yes means that the first row contains the column name, but not the first row when counting the number of rows. NO is the opposite.
IMEX: 0 write mode; 1 read mode; 2 read and write mode. If the error is "the design of table sheet1 cannot be modified". It's in a read-only database."So get rid of this and solve the problem.
There are still some unsolved problems to be continued...