[點晴永久免費OA]C# 使用NPOI 實現Excel的簡單導入導出
當前位置：點晴教程→點晴OA辦公管理信息系統
→『 經驗分享&問題答疑 』
private void btnImport_Click(object sender, EventArgs e) { DataSet ds = new DataSet(); DataTable dt = null; OpenFileDialog sflg = new OpenFileDialog(); sflg.Filter = "Excel(*.xls)│*.xls│Excel(*.xlsx)│*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } FileStream fs = new FileStream(sflg.FileName, FileMode.Open, FileAccess.Read); NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook(fs); int sheetCount = book.NumberOfSheets; for (int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex++) { NPOI.SS.UserModel.ISheet sheet = book.GetSheetAt(sheetIndex); if (sheet == null) continue; NPOI.SS.UserModel.IRow row = sheet.GetRow(0); if (row == null) continue; int firstCellNum = row.FirstCellNum; int lastCellNum = row.LastCellNum; if (firstCellNum == lastCellNum) continue; dt = new DataTable(sheet.SheetName); for (int i = firstCellNum; i < lastCellNum; i++) { dt.Columns.Add(row.GetCell(i).StringCellValue, typeof(string)); } for (int i = 1; i <= sheet.LastRowNum; i++) { DataRow newRow = dt.Rows.Add(); for (int j = firstCellNum; j < lastCellNum; j++) { newRow[j] = sheet.GetRow(i).GetCell(j).StringCellValue; } } ds.Tables.Add(dt); } } private void btnExport_Click(object sender, EventArgs e) { SaveFileDialog sflg = new SaveFileDialog(); sflg.Filter = "Excel(*.xls)│*.xls│Excel(*.xlsx)│*.xlsx"; if (sflg.ShowDialog() == System.Windows.Forms.DialogResult.Cancel) { return; } //this.gridView1.ExportToXls(sflg.FileName); //NPOI.xs book = new NPOI.HSSF.UserModel.HSSFWorkbook(); NPOI.SS.UserModel.IWorkbook book = null; if (sflg.FilterIndex == 1) { book = new NPOI.HSSF.UserModel.HSSFWorkbook(); } else { book = new NPOI.XSSF.UserModel.XSSFWorkbook(); } NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("test_001"); // 添加表頭 NPOI.SS.UserModel.IRow row = sheet.CreateRow(0); int index = 0; foreach (GridColumn item in this.gridView1.Columns) { if (item.Visible) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(item.Caption); index++; } } // 添加數據 for (int i = 0; i < this.gridView1.DataRowCount; i++) { index = 0; row = sheet.CreateRow(i + 1); foreach (GridColumn item in this.gridView1.Columns) { if (item.Visible) { NPOI.SS.UserModel.ICell cell = row.CreateCell(index); cell.SetCellType(NPOI.SS.UserModel.CellType.String); cell.SetCellValue(this.gridView1.GetRowCellValue(i, item).ToString()); index++; } } } // 寫入 System.IO.MemoryStream ms = new System.IO.MemoryStream(); book.Write(ms); book = null; using (FileStream fs = new FileStream(sflg.FileName, FileMode.Create, FileAccess.Write)) { byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); } ms.Close(); ms.Dispose(); }
該文章在 2020/4/3 22:37:31 編輯過 |
關鍵字查詢
相關文章
正在查詢... 
|
400 186 1886
