What is? NPOI?
NPOI It's built on POI 3.x A program above version ,NPOI Can be installed without Office In this case, yes Word or Excel Read and write the document .
NPOI It's an open source C# Reading and writing Excel,WORD Waiting for Microsoft OLE2 Component document project .
use NuGet install NPOI
NuGet Direct search NPOI, The current version is v2.4.1, Just install it to the project .
After installation , The project will automatically add this to us 4 References
At the same time, it also needs to be introduced into the program NPOI.SS.UserModel;NPOI.XSSF.UserModel;NPOI.HSSF.UserModel; Three namespace
No more nonsense , Code directly
DataTable export Excel
/// <summary> /// Datable Export to Excel /// </summary> /// <param
name="dt"></param> /// <param name="file"> export path ( Include file name and extension )</param> public static
void TableToExcel(DataTable dt, string file) { IWorkbook workbook; string
fileExt = Path.GetExtension(file).ToLower(); if (fileExt == ".xlsx") { workbook
= new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new
HSSFWorkbook(); } else { workbook = null; } if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ?
workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName); // Header IRow
row = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { ICell
cell = row.CreateCell(i); cell.SetCellValue(dt.Columns[i].ColumnName); } // data
for (int i = 0; i < dt.Rows.Count; i++) { IRow row1 = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++) { ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString()); } } // Convert to byte array MemoryStream stream =
new MemoryStream(); workbook.Write(stream); var buf = stream.ToArray();
// Save as Excel file using (FileStream fs = new FileStream(file, FileMode.Create,
FileAccess.Write)) { fs.Write(buf, 0, buf.Length); fs.Flush(); } }
Excel Import DataTable
/// <summary> /// Excel Import into Datable /// </summary> /// <param
name="file"> import path ( Include file name and extension )</param> /// <returns></returns> public static
DataTable ExcelToTable(string file) { DataTable dt = new DataTable(); IWorkbook
workbook; string fileExt = Path.GetExtension(file).ToLower(); using (FileStream
fs = new FileStream(file, FileMode.Open, FileAccess.Read)) { //XSSFWorkbook
apply XLSX format ,HSSFWorkbook apply XLS format if (fileExt == ".xlsx") { workbook = new
XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new
HSSFWorkbook(fs); } else { workbook = null; } if (workbook == null) { return
null; } ISheet sheet = workbook.GetSheetAt(0); // Header IRow header =
sheet.GetRow(sheet.FirstRowNum); List<int> columns = new List<int>(); for (int
i = 0; i < header.LastCellNum; i++) { object obj =
GetValueType(header.GetCell(i)); if (obj == null || obj.ToString() ==
string.Empty) { dt.Columns.Add(new DataColumn("Columns" + i.ToString())); }
else dt.Columns.Add(new DataColumn(obj.ToString())); columns.Add(i); } // data for
(int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++) { DataRow dr =
dt.NewRow(); bool hasValue = false; foreach (int j in columns) { dr[j] =
GetValueType(sheet.GetRow(i).GetCell(j)); if (dr[j] != null && dr[j].ToString()
!= string.Empty) { hasValue = true; } } if (hasValue) { dt.Rows.Add(dr); } } }
return dt; } /// <summary> /// Get cell type /// </summary> /// <param
name="cell"></param> /// <returns></returns> private static object
GetValueType(ICell cell) { if (cell == null) return null; switch
(cell.CellType) { case CellType.Blank: //BLANK: return null; case
CellType.Boolean: //BOOLEAN: return cell.BooleanCellValue; case
CellType.Numeric: //NUMERIC: return cell.NumericCellValue; case
CellType.String: //STRING: return cell.StringCellValue; case CellType.Error:
//ERROR: return cell.ErrorCellValue; case CellType.Formula: //FORMULA: default:
return "=" + cell.CellFormula; } }
Technology