C # Use NPOI to export an Excel file,

C # Use NPOI to export an Excel file,

NPOI can read and write Word or Excel documents without installing Office

The following describes how to use NPOI to Operate Excel.

First, we need to download the NPOI assembly

Http://npoi.codeplex.com/releases

These two files are downloaded.

Here net4.0 is used

Add the following dll to the project and reference

 

Not much nonsense about code

/// <Summary> // create by sly // import the DataTable data to excel. This method returns the file directly to the browser for download. /// </summary> // /<param name = "data"> data to be imported </param> // <param name = "isColumnWritten"> whether to import the column name of the DataTable column </param> // /<param name = "sheetName"> name of the sheet of the excel file to be imported </param> // <param name = "widthDic"> width to be set (key column subscript), val width) </param> // <returns> </returns> public int DataTableToExcel (DataTable data, string sheetName, bo Ol isColumnWritten, Dictionary <int, int> widthDic = null) {int I = 0; int j = 0; int count = 0; ISheet sheet = null; memoryStream file = new MemoryStream (); if (fileName. indexOf (". xlsx ")> 0) // workbook version 2007 = new XSSFWorkbook (); else if (fileName. indexOf (". xls ")> 0) // workbook version 2003 = new HSSFWorkbook (); try {if (workbook! = Null) {sheet = workbook. createSheet (sheetName); // set the width of all columns for (int l = 0; l <= data. rows. count; l ++) {sheet. defaultColumnWidth = 12;} if (widthDic! = Null) {// set the custom width foreach (var item in widthDic) {sheet. setColumnWidth (item. key, item. value) ;}} else {return-1;} if (isColumnWritten = true) // name of the column written to the DataTable {IRow row = sheet. createRow (0); for (j = 0; j <data. columns. count; ++ j) {row. createCell (j ). setCellValue (data. columns [j]. columnName) ;}count = 1 ;}else {count = 0 ;}int outRes =-1; for (I = 0; I <data. rows. count; ++ I) {IRow Row = sheet. createRow (count); for (j = 0; j <data. columns. count; ++ j) {if (int. tryParse (data. rows [I] [j]. toString (), out outRes) {row. createCell (j ). setCellValue (Convert. toInt32 (data. rows [I] [j]. toString ();} else {if (! String. isNullOrEmpty (data. rows [I] [j]. toString () & data. rows [I] [j]. toString (). length> 10) {if (data. rows [I] [j]. toString (). substring (0, 7) = "http: //" | data. rows [I] [j]. toString (). substring (0, 8) = "https: //") {var TCell = row. createCell (j); HSSFHyperlink link = new HSSFHyperlink (HyperlinkType. url); // create an HSSFHyperlink object, specifying the link type as URL TCell. setCellValue (data. rows [I] [j]. toString (); link. address = data. rows [I] [j]. toString (); // assign TCell to the address of HSSFHyperlink. hyperlink = link;} else {row. createCell (j ). setCellValue (data. rows [I] [j]. toString () ;}} else {row. createCell (j ). setCellValue (data. rows [I] [j]. toString () ;}}+ + count ;} workbook. write (file); // Write to excel var context = System. web. httpContext. current; context. response. contentType = "application/vnd. ms-excel "; context. response. addHeader ("Content-Disposition", string. format ("attachment; filename = {0}", fileName); context. response. clear (); context. response. binaryWrite (file. getBuffer (); context. response. end (); return count;} catch (Exception ex) {return-2 ;}}

 

/// <Summary> /// import the data in excel to the able /// </summary> /// <param name = "sheetName"> name of the sheet in the excel worksheet </param> /// <param name = "isFirstRowColumn"> whether the first row is the column name of the able </param> /// <returns> </returns> public DataTable ExcelToDataTable (string sheetName, bool isFirstRowColumn) {ISheet sheet = null; DataTable data = new DataTable (); int startRow = 0; try {fs = new FileStream (fileName, FileMode. open, File Access. read); if (fileName. indexOf (". xlsx ")> 0) // workbook version 2007 = new XSSFWorkbook (fs); else if (fileName. indexOf (". xls ")> 0) // workbook 2003 = new HSSFWorkbook (fs); if (sheetName! = Null) {sheet = workbook. getSheet (sheetName); if (sheet = null) // if the sheet corresponding to the specified sheetName is not found, try to get the first sheet {sheet = workbook. getSheetAt (0) ;}} else {sheet = workbook. getSheetAt (0);} if (sheet! = Null) {IRow firstRow = sheet. getRow (0); int cellCount = firstRow. lastCellNum; // The number of the last cell in a row, that is, the total number of columns if (isFirstRowColumn) {for (int I = firstRow. firstCellNum; I <cellCount; ++ I) {ICell cell = firstRow. getCell (I); if (cell! = Null) {string cellValue = cell. StringCellValue; if (cellValue! = Null) {DataColumn column = new DataColumn (cellValue); data. columns. add (column) ;}} startRow = sheet. firstRowNum + 1;} else {startRow = sheet. firstRowNum;} // int rowCount = sheet in the last column. lastRowNum; for (int I = startRow; I <= rowCount; ++ I) {IRow row = sheet. getRow (I); if (row = null) continue; // The default value of a row without data is null DataRow dataRow = data. newRow (); for (int j = row. firstCellNum; j <cellCount; ++ J) {if (row. GetCell (j )! = Null) // Similarly, all cells without data are null dataRow [j] = row by default. getCell (j ). toString ();} data. rows. add (dataRow) ;}} return data ;}catch (Exception ex) {return null ;}}

 

/// <Summary> /// merge cells /// </summary> /// <param name = "sheet"> the sheet of the cells to be merged </param>/ // <param name = "rowstart"> Start row index </param> // <param name = "rowend"> end row index </param> /// <param name = "colstart"> Start column index </param> // <param name = "colend"> end column index </param> public static void SetCellRangeAddress (ISheet sheet, int rowstart, int rowend, int colstart, int colend) {CellRangeAddress cellRangeAddress = new CellRangeAddress (rowstart, rowend, colstart, colend); sheet. addMergedRegion (cellRangeAddress );}

 

// Call the export method new Helper. excelHelper ("exported file name .xls "). dataTableToExcel (dt, "sheet1", true, null); new Helper. excelHelper ("exported file name .xlsx "). dataTableToExcel (dt, "sheet1", true, null );

 

The following is the import

<Input type = "file" name = "fileField" class = "file" id = "fileField" value = "import" style = "display: none; "/> <script >$ (" # fileField "). live ("change", function () {var Reg = new RegExp ("^. + \. (xls) "); var Reg2 = new RegExp (" ^. + \. (xlsx) "); if (Reg. test ($ (this ). val () | Reg. test2 ($ (this ). val () {ImportGrade (); // The background import method is called here, And the else {$. ivanDialogOpen ({"Type": "tips", "Info": "Only supported. xls, .xlsx file "," Plugins ": {CloseTime: 800 }}) ;}) </script>

/// <Summary> /// import Demo /// </summary> /// <param name = "filePath"> </param> /// <returns> </returns> public ActionResult ImportGrade (string filePath) {// obtain the path of the file on the server string path = string. concat (FileUpload. getFilePath (UploadType. importGradeFile), filePath); // obtain table data from the upload path. ExcelHelper eh = new ExcelHelper (path. replace ("\", "/"); DataTable dt = eh. excelToDataTable ("Sheet1", true); return Content ("");}

 

The above is a simple NPOI usage method.