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名稱該出現的字元
















留言

這個網誌中的熱門文章

何謂淨重(Net Weight)、皮重(Tare Weight)與毛重(Gross Weight)

Architecture(架構) 和 Framework(框架) 有何不同?_軟體設計前的事前規劃的藍圖概念

(2021年度)駕訓學科筆試準備題庫歸納分析_法規是非題