Introduction to the various methods of exporting in C #

This article mainly introduced the C # various export method related knowledge, has the very good reference value. Let's take a look at the little series.

The first type: using Microsoft.Office.Interop.Excel.dll

You first need to install Office Excel, and then find the Microsoft.Office.Interop.Excel.dll component that you added to the reference.

public void Exportexcel (DataTable dt) {if (dt! = NULL) {Microsoft.Office.Interop.Excel.Application E        xcel = new Microsoft.Office.Interop.Excel.Application ();        if (Excel = = null) {return; }//set to invisible, operation is executed in the background, and True will open Excel Excel.        Visible = false; Set to full-screen explicit//excel when open.        Displayfullscreen = true; Initialize workbook Microsoft.Office.Interop.Excel.Workbooks Workbooks = Excel.        Workbooks; Adding a new workbook, the Add () method can also pass directly to the parameter true Microsoft.Office.Interop.Excel.Workbook Workbook = workbooks.        ADD (Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet); A new workbook is also added, but the Save dialog box will pop up//microsoft.office.interop.excel.workbook Workbook = Excel.        APPLICATION.WORKBOOKS.ADD (TRUE); Add a new Excel table (sheet) Microsoft.Office.Interop.Excel.Worksheet Worksheet = (Microsoft.Office.Interop.Excel.Workshe ET) workbook.        WORKSHEETS[1]; Sets 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 for the line is 1 int colindex = 1; The starting subscript for the column is 1//Set column name for (int i = 0; i < dt. Columns.count; i++) {//Set the first row, which is the column name worksheet. Cells[rowindex, Colindex + i] = dt. Columns[i].            ColumnName; Gets the first row of each cell, range = worksheet.            Cells[rowindex, Colindex + i]; Sets the internal color range of the cell.            Interior.ColorIndex = 33; Font Bold range.            Font.Bold = true; Set to black range.            Font.Color = 0; Set to Arial range.            Font.Name = "Arial"; Sets the font size range.            Font.Size = 12; Center the range horizontally.            HorizontalAlignment = Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter; Centers the range vertically.          VerticalAlignment = Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;          }//Skips the first line, the first line is written to 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 be auto-width//worksheet.          Columns.AutoFit (); Set all cells Grekon to automatic column width worksheet.          Cells.Columns.AutoFit (); Worksheet.          Cells.EntireColumn.AutoFit ();          If prompted, if you want to delete a sheet page, first set this to Fasle. Excel.          DisplayAlerts = false; Save the written data, which is not yet saved to disk workbook.          Saved = true;          Set export file path string path = HttpContext.Current.Server.MapPath ("export/");          Set the new file path and name string Savepath = path + DateTime.Now.ToString ("yyyy-mm-dd-hh-mm-ss") + ". xlsx";          Create files FileStream file = new FileStream (Savepath, filemode.createnew); Close the release stream, or you won't be able to write to the data file.          Close (); File.          Dispose (); Save to the specified path, workbook. SaveCopyAs (SavepaTH);          You can also add the following method output to the browser download FileInfo FileInfo = new FileInfo (Savepath);        Outputclient (FileInfo); } catch (Exception ex) {} finally {workbook.          Close (False, Type.Missing, Type.Missing); Workbooks.          Close (); Close Exit 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 first method of performance is not flattering, and there are too many limitations. You must first install Office (if it is not on your computer), and you need to specify the path to save the file when you export. You can also export to the browser to download, of course, if you have saved the write data.

The second type: using Aspose.Cells.dll

This aspose.cells is a aspose company launched by the export of Excel controls, does not rely on Office, commercial software, fees.

public void Exportexcel (DataTable dt) {try {//Get 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);        Registered 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 table Aspose.Cells.Worksheet Worksheet = workbook.        Worksheets[0]; Set the sheet Table name worksheet. Name = dt.        TableName;        Aspose.Cells.Cell Cell;  int rowIndex = 0;  The starting subscript for the line is 0 int colindex = 0; The starting subscript for the column is 0//Set column name for (int i = 0; i < dt. Columns.count; i++) {//Gets the first row of each cell = worksheet.          Cells[rowindex, Colindex + i]; Set column name cell. Putvalue (dt. Columns[i].          ColumnName); Sets the font cell.          Style.Font.Name = "Arial"; Set Font Bold cell.          Style.Font.IsBold = true; Sets the font size of the cell.          Style.Font.Size = 12; Sets the font color of cell.          Style.Font.Color = System.Drawing.Color.Black; Sets the background color of the cell.        Style.backgroundcolor = System.Drawing.Color.LightGreen;        }//Skips the first line, the first line is written to 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 ();        Set the path of the export file paths = HttpContext.Current.Server.MapPath ("export/");        Set the new file path and name string Savepath = path + DateTime.Now.ToString ("yyyy-mm-dd-hh-mm-ss") + ". xlsx";        Create files FileStream file = new FileStream (Savepath, filemode.createnew); Close the release stream, or you won't be able to write to the data file.        Close (); File.        Dispose (); Save to the specified path workbook. Save (Savepath);        Or use the following method to output the download to the browser. 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 second method performs well, and the operation is not complex, you can set the path to save the file when exporting, and it can be saved as stream output to the browser download.

The third type: Microsoft.Jet.OLEDB

This method of manipulating Excel is similar to manipulating the database. Here's a look at the connection string:

Excel 2003 version Connection string strconn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=c:/xxx.xls; Extended properties= ' Excel 8.0; hdr=yes;imex=2; ' "; /Excel 2007 version connection 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: Specifies the path of the Excel file

Extended Properties: Excel 8.0 is for Excel 2000 and later, and Excel 12.0 is for Excel 2007 and above.

HDR: Yes indicates that the first row contains column names and does not include the first row when calculating the number of rows. NO is the exact opposite.

IMEX:0 Write mode, 1 read mode, 2 read-write mode. If the error is "cannot modify the design of table Sheet1. It's in a read-only database, "then remove this and solve the problem."