Use the Workbook class to generate Excel export data, and workbookexcel

Use the Workbook class to generate Excel export data, and workbookexcel

Requirements:

An Excel file is generated to save the error information to a local file for you to view.

Microsoft's built-in Microsoft. Office. Interop. Excel class library,

Microsoft. office. interop. excel. application excel = new Microsoft. office. interop. excel. application (); excel. visible = true; // Add a new Workbook. The Workbook is saved directly. The save dialog box is not displayed. The save dialog box is displayed when the Application is added. If the value is false, an error occurs in excel. application. workbooks. add (true); // generate the name of the column header in Excel. cells [1, 1] = "name"; excel. cells [1, 2] = "employee ID"; excel. cells [1, 3] = "company"; excel. cells [1, 4] = "department"; excel. cells [1, 5] = "cause of failure"; // Save the data on the current newdt page in Excel for (int I = 0; I <newdt. rows. count; I ++) {excel. cells [I + 2, 1] = "'" + newdt. rows [I] ["Emp_Name"]; excel. cells [I + 2, 2] = "'" + newdt. rows [I] ["Emp_Code"]; excel. cells [I + 2, 3] = "'" + newdt. rows [I] ["CompanyName"]; excel. cells [I + 2, 4] = "'" + newdt. rows [I] ["DeptName"]; excel. cells [I + 2, 5] = "'" + newdt. rows [I] ["Error"];} // sets to prohibit the pop-up of the SAVE and overwrite query prompt box excel. displayAlerts = false; excel. alertBeforeOverwriting = false; // Save the workbook in excel. application. workbooks. add (true ). save (); // Save the excel file. save ("D:" + "\ error message .xls"); // make sure that the Excel process is disabled. quit (); excel = null;

Although the function is implemented, the COM + component cannot be called because Excel is not installed on the server.

In addition, the COM + component is not recommended by Microsoft. I heard that the server may be down if a problem occurs during the export process.

After reading this information, modify it to call the Aspose. Cells component.

/// <Summary> /// export data to the local device /// </summary> /// <param name = "dt"> data to be exported </param>/ // <param name = "Name"> title </param> /// <param name = "path"> Save path </param> public void OutFileToDisk (DataTable dt, string Name, string path) {Workbook workbook = new Workbook ();/* workbook */Worksheet sheet = Workbook. worksheets [0];/* worksheet */Cells = sheet. cells;/* cell // set the Style for the title */Style styleTitle = workbook. styles [workbook. styles. add ()];/* Add Style */Style style2 = workbook. styles [workbook. styles. add ()];/* Add style */style2.HorizontalAlignment = TextAlignmentType. center;/* Text Center */style2.Font. name = "";/* text font */style2.Font. size = 12;/* text Size */style2.Font. isBold = true;/* Bold */style2.IsTextWrapped = true;/* Automatic line feed of cell content */style2.Borders [BorderType. leftBorder]. lineStyle = CellBorderType. thin; style2.Borders [BorderType. rightBorder]. lineStyle = CellBorderType. thin; style2.Borders [BorderType. topBorder]. lineStyle = CellBorderType. thin; style2.Borders [BorderType. bottomBorder]. lineStyle = CellBorderType. thin;/* Style 3 */Style style3 = workbook. styles [workbook. styles. add ()];/* Add style */style3.HorizontalAlignment = TextAlignmentType. center;/* Text Center */style3.Font. name = "";/* text font */style3.Font. size = 10;/* text Size */style3.Borders [BorderType. leftBorder]. lineStyle = CellBorderType. thin; style3.Borders [BorderType. rightBorder]. lineStyle = CellBorderType. thin; style3.Borders [BorderType. topBorder]. lineStyle = CellBorderType. thin; style3.Borders [BorderType. bottomBorder]. lineStyle = CellBorderType. thin; int Colnum = dt. columns. count;/* Number of table columns */int Rownum = dt. rows. count;/* generate Row 2 column name row */for (int I = 0; I <Colnum; I ++) {cells [0, I]. putValue (dt. columns [I]. columnName); cells [0, I]. setStyle (style2); cells. setRowHeight (0, 20); cells. setColumnWidth (I, 20);}/* generate data rows */for (int I = 0; I <Rownum; I ++) {for (int k = 0; k <Colnum; k ++) {cells [1 + I, k]. putValue (dt. rows [I] [k]. toString (); cells [1 + I, k]. setStyle (style3);} cells. setRowHeight (1 + I, 18);} // workbook. save (path); // you can Save MemoryStream MS = workbook. saveToStream (); byte [] bt = ms. toArray (); string fileName = Name + DateTime. now. toString ("yyyyMMddHHmmssfff") + ". xls "; // file name saved by the client // download the file Response in the form of a Response stream. contentType = "application/vnd. ms-excel "; // notify the browser to download the file instead of opening Response. addHeader ("Content-Disposition", "attachment; filename =" + HttpUtility. urlEncode (fileName, System. text. encoding. UTF8); Response. binaryWrite (bt); Response. flush (); Response. end ();}

Share for everyone to learn,