ASP.NET_NPOI使用時遇到的陷阱_CreateSheet不要傳入完整路徑
最近於工作專案中遇到的一個坑
由於要進行Excel檔案的匯出,使用到C# NPOI的第三方庫
出現如下錯誤
System.ArgumentException: 'Invalid char (:) found at index (1)
由於之前實作過的function是將DataGridView中資料匯出
對應直接採用SaveFileDialog方式設置副檔名、路徑、檔名等資訊沒報錯
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 | public void GridToExcel(string fileName, DataGridView dgv) { if (dgv.Rows.Count == 0) { return; } SaveFileDialog sfd = new SaveFileDialog(); sfd.Filter = "Excel 2003格式|*.xls"; sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMdd_HHmmss"); if (sfd.ShowDialog() != DialogResult.OK) { return; } HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(fileName); HSSFRow headRow = (HSSFRow)sheet.CreateRow(0); for (int i = 0; i < dgv.Columns.Count; i++) { HSSFCell headCell = (HSSFCell)headRow.CreateCell(i, CellType.String); headCell.SetCellValue(dgv.Columns[i].HeaderText); } for (int i = 0; i < dgv.Rows.Count; i++) { HSSFRow row = (HSSFRow)sheet.CreateRow(i + 1); for (int j = 0; j < dgv.Columns.Count; j++) { HSSFCell cell = (HSSFCell)row.CreateCell(j); if (dgv.Rows[i].Cells[j].Value == null) { cell.SetCellType(CellType.Blank); } else { if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32")) { cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value)); } else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String")) { cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString()); } else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single")) { cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value)); } else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double")) { cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value)); } else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal")) { cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value)); } else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime")) { cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd HH:mm:ss")); } } } } for (int i = 0; i < dgv.Columns.Count; i++) { sheet.AutoSizeColumn(i); } using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create)) { wb.Write(fs); } MessageBox.Show("Export Successfully!", "Export Tips", MessageBoxButtons.OK, MessageBoxIcon.Information); } |
但是這次是程式直接寫死設置完整路徑
才發現這個function有一些細節,並不是傳入一個excel(xls)檔案的完整路徑
而是只有針對那個excel檔案名稱
有錯誤的function
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 | public void ExportExcelFile(string strFilePath,List<string> lsColumnName, List<List<string>> lsRowDataCollection) { string FileName = strFilePath + @"\" + "PersonData_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(FileName); HSSFRow headRow = (HSSFRow)sheet.CreateRow(0); for (int idxColumn = 0; idxColumn < lsColumnName.Count; idxColumn++) { HSSFCell headCell = (HSSFCell)headRow.CreateCell(idxColumn, CellType.String); string strColName = lsColumnName[idxColumn]; headCell.SetCellValue(strColName); } for (int idxRow = 0; idxRow < lsRowDataCollection.Count; idxRow++) { HSSFRow row = (HSSFRow)sheet.CreateRow(idxRow + 1); for (int idxColData = 0; idxColData < lsRowDataCollection[idxRow].Count; idxColData++) { HSSFCell cell = (HSSFCell)row.CreateCell(idxColData); string cellData = string.IsNullOrEmpty(lsRowDataCollection[idxRow][idxColData]) ? "" : lsRowDataCollection[idxRow][idxColData]; if (cellData == string.Empty) cell.SetCellType(CellType.Blank); else { cell.SetCellValue(cellData); } } } for (int i = 0; i < lsColumnName.Count; i++) { sheet.AutoSizeColumn(i); } using (FileStream fs = new FileStream(FileName, FileMode.Create)) { wb.Write(fs); } } |
經修正後
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 | public void ExportExcelFile(string strFilePath, List<string> lsColumnName, List<List<string>> lsRowDataCollection) { string FileName = "PersonData_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls"; HSSFWorkbook wb = new HSSFWorkbook(); HSSFSheet sheet = (HSSFSheet)wb.CreateSheet(FileName); HSSFRow headRow = (HSSFRow)sheet.CreateRow(0); for (int idxColumn = 0; idxColumn < lsColumnName.Count; idxColumn++) { HSSFCell headCell = (HSSFCell)headRow.CreateCell(idxColumn, CellType.String); string strColName = lsColumnName[idxColumn]; headCell.SetCellValue(strColName); } for (int idxRow = 0; idxRow < lsRowDataCollection.Count; idxRow++) { HSSFRow row = (HSSFRow)sheet.CreateRow(idxRow + 1); for (int idxColData = 0; idxColData < lsRowDataCollection[idxRow].Count; idxColData++) { HSSFCell cell = (HSSFCell)row.CreateCell(idxColData); string cellData = string.IsNullOrEmpty(lsRowDataCollection[idxRow][idxColData]) ? "" : lsRowDataCollection[idxRow][idxColData]; if (cellData == string.Empty) cell.SetCellType(CellType.Blank); else { cell.SetCellValue(cellData); } } } for (int i = 0; i < lsColumnName.Count; i++) { sheet.AutoSizeColumn(i); } //strFilePath + @"\" FileName = strFilePath + @"\" + FileName; using (FileStream fs = new FileStream(FileName, FileMode.Create)) { wb.Write(fs); } } |
在透過FileStream寫檔案時在串完整路徑
切記不能一開始就包完整路徑給NPOI CreateSheet的函數
因為有些像是斜線、冒號的符號並非合法Sheet名稱該出現的字元
留言
張貼留言