相信大家在工作中經常要遇到一些導入導出Execl操作。學習貴在分享，分享使人快樂，園子里的前輩已經有很多好的文章，鄙人也是能力有限，在這里把這些好的文章總結，方便以后再工作中使用。
NPOI：是 POI 項目的 .NET 版本。POI是一個開源的java讀寫Excel、Word等微軟OLE2組件文檔的項目。
NPOI是構建在POI 3.x版本之上的，它可以在沒有安裝Office的情況下對Word/Excel文檔進行讀寫操作。
它不使用Office COM組件(Microsoft.Office.Interop.XXX.dll)，不需要安裝Microsoft Office，支持對Office 97-2003的文件格式，功能比較強大。
能夠讀寫幾乎所有的Office 97-2003文件格式，至少能夠支持Word, Powerpoint, Excel, Visio的格式。
NPOI官方網站【點擊這里】
1、整個Excel表格叫做工作表：WorkBook（工作?。?，包含的叫頁（工作表）：Sheet；行：Row；單元格Cell。
2、NPOI是POI的C#版本，NPOI的行和列的index都是從0開始
3、POI讀取Excel有兩種格式一個是HSSF，另一個是XSSF。
HSSF和XSSF的區別如下：
HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，擴展名是.xls
XSSFWorkbook:是操作Excel2007的版本，擴展名是.xlsx
即：HSSF適用2007以前的版本,XSSF適用2007版本及其以上的。
HSSFWorkbook對應的就是Excel文件 工作簿，
HSSFSheet對應的就是Excel中sheet 工作表，
HSSFCell對應的就是Excel的單元格，
HSSFRow對應的就是Excel的行
.NET調用NPOI組件導入導出Excel的操作類
此NPOI操作類的優點如下：
（1）支持web及winform從DataTable導出到Excel；
（2）生成速度很快；
（3）準確判斷數據類型，不會出現身份證轉數值等問題；
（4）如果單頁條數大于65535時會新建工作表；
（5）列寬自適應；
namespace 導入導出{ public partial class Form1 : Form { public Form1() { InitializeComponent(); } PRivate void btnInput_Click(object sender, EventArgs e) { #region 導入到DataTable using (FileStream stream = File.OpenRead("huangjinfeng.xls")) { IWorkbook workbook = new HSSFWorkbook(stream); ISheet sheet = workbook.GetSheet("員工捐款信息表"); DataTable table = new DataTable(); IRow headerRow = sheet.GetRow(0); int cellCount = headerRow.LastCellNum; int rowCount = sheet.LastRowNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); DataRow dataRow = table.NewRow(); if (row != null) { for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) dataRow[j] = row.GetCell(j); } } table.Rows.Add(dataRow); } this.dataGridView1.DataSource = table; MessageBox.Show("導入數據成功"); } #endregion #region 導入到數據庫 //using (FileStream stream = File.OpenRead("huangjinfeng.xls")) //{ // string sql="INSERT INTO [dbo].[DonationDetail]([dUserName],[dcID],[dAmount],[dDate],[disdelete],[dCreateTime])"; // RenderToDb(stream,sql); // MessageBox.Show("SQL"); //} #endregion } private void btnOut_Click(object sender, EventArgs e) { DonationEntities2 db = new DonationEntities2(); var lists = db.CreateObjectSet<DonationDetail>().Where(c => c.disdelete == 0).ToList(); #region 自由導出 HSSFWorkbook workbook = new HSSFWorkbook(); //2.創建工作表 ISheet sheet = workbook.CreateSheet("員工捐款信息表"); IRow rowHeader = sheet.CreateRow(0); rowHeader.CreateCell(0, CellType.String).SetCellValue("DId"); rowHeader.CreateCell(1, CellType.String).SetCellValue("員工姓名"); rowHeader.CreateCell(2, CellType.String).SetCellValue("DcId"); rowHeader.CreateCell(3, CellType.String).SetCellValue("捐款金額"); rowHeader.CreateCell(4, CellType.String).SetCellValue("捐款日期"); rowHeader.CreateCell(5, CellType.String).SetCellValue("是否刪除"); rowHeader.CreateCell(6, CellType.String).SetCellValue("創建日期"); for (int i = 0; i < lists.Count; i++) { IRow row = sheet.CreateRow(i + 1); //為指定的行添加列 row.CreateCell(0, CellType.String).SetCellValue(lists[i].dId); row.CreateCell(1, CellType.String).SetCellValue(lists[i].dUserName); row.CreateCell(2, CellType.String).SetCellValue(lists[i].dcID); row.CreateCell(3, CellType.String).SetCellValue(lists[i].dAmount.ToString()); row.CreateCell(4, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dDate.ToString())); row.CreateCell(5, CellType.String).SetCellValue(lists[i].disdelete); row.CreateCell(6, CellType.String).SetCellValue(Convert.ToDateTime(lists[i].dCreateTime.ToString())); } //使用文件流做數據的寫入 using (FileStream fss = new FileStream("huangjinfeng.xls", FileMode.Create)) { workbook.Write(fss); } MessageBox.Show("導出數據成功"); #endregion }
#region 導入到數據庫 public static void RenderToDb(Stream excelFileStream, string insertSql) { using (excelFileStream) { IWorkbook workbook = new HSSFWorkbook(excelFileStream); ISheet sheet = workbook.GetSheetAt(0);//取第一個工作表 StringBuilder builder = new StringBuilder(); IRow headerRow = sheet.GetRow(0);//第一行為標題行 int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1 for (int i = (sheet.FirstRowNum + 1); i <= rowCount; i++) { IRow row = sheet.GetRow(i); if (row != null) { builder.Append(insertSql); builder.Append(" values ("); for (int j = row.FirstCellNum; j < cellCount; j++) { builder.AppendFormat("'{0}',", row.GetCell(j)).Replace("'", "''"); } builder.Length = builder.Length - 1; builder.Append(");"); } if ((i % 50 == 0 || i == rowCount) && builder.Length > 0) { //每50條記錄一次批量插入到數據庫 //rowAffected += dbAction(builder.ToString()); SqlHelper.ExecuteNonQuery(builder.ToString()); builder.Length = 0; } } } } #endregion
#region 是否有數據 /// <summary> /// 是否有數據 /// </summary> /// <param name="excelFileStream"></param> /// <returns></returns> public static bool HasData(Stream excelFileStream) { using (excelFileStream) { IWorkbook workbook = new HSSFWorkbook(excelFileStream); if (workbook.NumberOfSheets > 0) { ISheet sheet = workbook.GetSheetAt(0); return sheet.PhysicalNumberOfRows > 0; } } return false; } #endregion
} }工作中我一般主要是用到的MVC，在這里就說說我們項目中一般的處理過程。先看看我自己寫的一個NPOIBase父類。
public class NPOIBase : ActionResult { public IWorkbook _workbook { get; set; } public ISheet _sheet { get; set; } public ICellStyle _titleStyle { get; set; } public ICellStyle _leftStyle { get; set; } public ICellStyle _centerStyle { get; set; } public ICellStyle _rightStyle { get; set; } public ICellStyle _headStyle { get; set; } public ICellStyle _leftborderStyle { get; set; } public ICellStyle _rightborderStyle { get; set; } public ICellStyle _noneRightBorderStyle { get; set; } public ICellStyle _noneLeftBorderStyle { get; set; } public ICellStyle _noneLeftAndRightBorderStyle { get; set; } public ICellStyle _borderStyle { get; set; }
public override void ExecuteResult(ControllerContext context) { }
public void IniNPOI(bool isHeadBorder = false, string sheetName = "") { _workbook = new HSSFWorkbook(); _sheet = string.IsNullOrWhiteSpace(sheetName) ? _workbook.CreateSheet() : _workbook.CreateSheet(sheetName); IniStyle(isHeadBorder); }
public void IniStyle(bool isHeadBorder = false) { IFont font12 = _workbook.CreateFont(); font12.FontHeightInPoints = 12; font12.Boldweight = 700; _titleStyle = _workbook.CreateCellStyle(); _titleStyle.Alignment = HorizontalAlignment.Center; _titleStyle.VerticalAlignment = VerticalAlignment.Top; _titleStyle.SetFont(font12); _leftStyle = _workbook.CreateCellStyle(); _leftStyle.Alignment = HorizontalAlignment.Left; _leftStyle.VerticalAlignment = VerticalAlignment.Top; _centerStyle = _workbook.CreateCellStyle(); _centerStyle.Alignment = HorizontalAlignment.Center; _centerStyle.VerticalAlignment = VerticalAlignment.Top; _rightStyle = _workbook.CreateCellStyle(); _rightStyle.Alignment = HorizontalAlignment.Right; _rightStyle.VerticalAlignment = VerticalAlignment.Top; _headStyle = _workbook.CreateCellStyle(); _headStyle.Alignment = HorizontalAlignment.Center; _headStyle.VerticalAlignment = VerticalAlignment.Top; if (isHeadBorder) { _headStyle.BorderBottom = BorderStyle.Thin; _headStyle.BorderLeft = BorderStyle.Thin; _headStyle.BorderRight = BorderStyle.Thin; _headStyle.BorderTop = BorderStyle.Thin; } _leftborderStyle = _workbook.CreateCellStyle(); _leftborderStyle.Alignment = HorizontalAlignment.Left; _leftborderStyle.VerticalAlignment = VerticalAlignment.Top; _leftborderStyle.BorderBottom = BorderStyle.Thin; _leftborderStyle.BorderLeft = BorderStyle.Thin; _leftborderStyle.BorderRight = BorderStyle.Thin; _leftborderStyle.BorderTop = BorderStyle.Thin; _rightborderStyle = _workbook.CreateCellStyle(); _rightborderStyle.Alignment = HorizontalAlignment.Right; _rightborderStyle.VerticalAlignment = VerticalAlignment.Top; _rightborderStyle.BorderBottom = BorderStyle.Thin; _rightborderStyle.BorderLeft = BorderStyle.Thin; _rightborderStyle.BorderRight = BorderStyle.Thin; _rightborderStyle.BorderTop = BorderStyle.Thin; _noneRightBorderStyle = _workbook.CreateCellStyle(); _noneRightBorderStyle.Alignment = HorizontalAlignment.Left; _noneRightBorderStyle.VerticalAlignment = VerticalAlignment.Top; _noneRightBorderStyle.BorderBottom = BorderStyle.Thin; _noneRightBorderStyle.BorderLeft = BorderStyle.Thin; _noneRightBorderStyle.BorderTop = BorderStyle.Thin; _noneLeftBorderStyle = _workbook.CreateCellStyle(); _noneLeftBorderStyle.Alignment = HorizontalAlignment.Right; _noneLeftBorderStyle.VerticalAlignment = VerticalAlignment.Top; _noneLeftBorderStyle.BorderBottom = BorderStyle.Thin; _noneLeftBorderStyle.BorderRight = BorderStyle.Thin; _noneLeftBorderStyle.BorderTop = BorderStyle.Thin; _noneLeftAndRightBorderStyle = _workbook.CreateCellStyle(); _noneLeftAndRightBorderStyle.Alignment = HorizontalAlignment.Center; _noneLeftAndRightBorderStyle.VerticalAlignment = VerticalAlignment.Top; _noneLeftAndRightBorderStyle.BorderBottom = BorderStyle.Thin; _noneLeftAndRightBorderStyle.BorderTop = BorderStyle.Thin; _borderStyle = _workbook.CreateCellStyle(); _borderStyle.Alignment = HorizontalAlignment.Center; _borderStyle.VerticalAlignment = VerticalAlignment.Top; _borderStyle.BorderBottom = BorderStyle.Thin; _borderStyle.BorderLeft = BorderStyle.Thin; _borderStyle.BorderRight = BorderStyle.Thin; _borderStyle.BorderTop = BorderStyle.Thin; IFont font = _workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; _headStyle.SetFont(font); }
public void FillHeadCell(IRow row, int colIndex, string value, ICellStyle cellStyle = null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null) { if (_sheet == null || row == null) return; if (cellStyle == null) cellStyle = _headStyle; FillCell(row, colIndex, value, cellStyle, mergedCellRangeAddress); _sheet.SetColumnWidth(colIndex, (Encoding.Default.GetBytes(value.Trim()).Length + 4) * 256); }
public void FillCell(IRow row, int colIndex, string value, ICellStyle cellStyle = null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null) { if (_sheet == null || row == null) return; ICell titleSum = row.CreateCell(colIndex); titleSum.SetCellValue(value); if (cellStyle != null) titleSum.CellStyle = cellStyle; else if (_centerStyle != null) titleSum.CellStyle = _centerStyle; if (mergedCellRangeAddress != null) _sheet.AddMergedRegion(mergedCellRangeAddress); }
public void FillCell(IRow row, int colIndex, double value, ICellStyle cellStyle = null,
NPOI.SS.Util.CellRangeAddress mergedCellRangeAddress = null) { if (_sheet == null || row == null) return; ICell titleSum = row.CreateCell(colIndex); titleSum.SetCellValue(value); if (cellStyle != null) titleSum.CellStyle = cellStyle; else if (_centerStyle != null) titleSum.CellStyle = _centerStyle; if (mergedCellRangeAddress != null) _sheet.AddMergedRegion(mergedCellRangeAddress); }
public void ResponSEOutPutExcelStream(string fildName) { if (string.IsNullOrWhiteSpace(fildName)) fildName = DateTime.Now.ToString("yyyyMMddHHmmss.xls"); if (fildName.ToLower().IndexOf(".xls") == -1) fildName += ".xls"; HttpContext.Current.Response.ContentType = "application/vnd.ms-excel"; HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", fildName)); HttpContext.Current.Response.Clear(); MemoryStream file = new MemoryStream(); _workbook.Write(file); file.WriteTo(HttpContext.Current.Response.OutputStream); HttpContext.Current.Response.End(); }
public void SetPrint(bool isLandscape = false, bool isFitToPage = false, double topMargin = 0, double rightMargin = 0,
double bottomMargin = 0.5, double leftMargin = 0, short scale = 100) { _sheet.PrintSetup.Landscape = isLandscape; _sheet.SetMargin(MarginType.TopMargin, topMargin); _sheet.SetMargin(MarginType.RightMargin, rightMargin); _sheet.SetMargin(MarginType.LeftMargin, leftMargin); _sheet.SetMargin(MarginType.BottomMargin, bottomMargin); _sheet.PrintSetup.PaperSize = 9; _sheet.PrintSetup.Scale = scale; _sheet.FitToPage = isFitToPage; if (isFitToPage) { _sheet.PrintSetup.FitWidth = 1; _sheet.PrintSetup.FitHeight = 0; } } }
這個由于父類是繼承的ActionResult，我們用起來就比較方便，在Action中，直接使用就可以了。代碼示例如下...
public class BaseMaterialsExcelResult : NPOIBase { string[] __headers = null; IList<BaseMaterials> __BaseMaterialsList; public BaseMaterialsExcelResult(IList<BaseMaterials> BaseMaterialsList) { __BaseMaterialsList = BaseMaterialsList; __headers = new string[] { "序號", "材料", "型號", "推薦供應商", "出庫數量", "入庫數量", "結存" }; }
public override void ExecuteResult(ControllerContext context) { if (__BaseMaterialsList == null || __BaseMaterialsList.Count() == 0) return; IniNPOI(); int rowIndex = 0; foreach (var item in __BaseMaterialsList) { #region 新建表，填充列頭，樣式 int colIndex = 0; if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) _sheet = _workbook.CreateSheet(); IRow headerRow = _sheet.CreateRow(rowIndex); foreach (var head in __headers) FillHeadCell(headerRow, colIndex++, head); rowIndex = 1; } #endregion #region 填充內容 IRow dataRow = _sheet.CreateRow(rowIndex); colIndex = 0; FillCell(dataRow, colIndex++, rowIndex); FillCell(dataRow, colIndex++, item.Name); FillCell(dataRow, colIndex++, item.Type); FillCell(dataRow, colIndex++, item.ProviderName); FillCell(dataRow, colIndex++, item.OutStorageCount.ToString()); FillCell(dataRow, colIndex++, item.StorageCount.ToString()); FillCell(dataRow, colIndex++, item.StockCount.ToString()); #endregion rowIndex++; } _sheet.CreateFreezePane(1, 1, 1, 1); ResponseOutPutExcelStream("BaseMaterials.xls"); } }
控制器中的代碼如下:
public class HomeController : Controller { BluedonStockEntities db = new BluedonStockEntities(); public List<BaseMaterials> GetList() { return db.CreateObjectSet<BaseMaterials>().Where(c => true).ToList(); } public ActionResult Index() { var list = GetList(); return View(list); } public ActionResult Execl() { var list = GetList(); return new BaseMaterialsExcelResult(list); } }
簡單用法的源碼【點擊下載】
MVC版的源碼【點擊下載】
感謝你的支持，為我點個贊吧！
新聞熱點
疑難解答
