iT邦幫忙

0

NPOI.XSSF 如何根據 原有資料庫型別，填入欄位

c# npoi.xssf 欄位型別
noway 2023-06-05 17:11:30 ‧ 1523 瀏覽

  • 分享至 

  • xImage

您好：
目前C# 使用 NPOI.XSSF，要來將DB 資料匯出成XLSX
但 1.我先處理 colmnName 在第一行
sheet.GetRow(m_SkipRow).CreateCell( h ).SetCellValue( col.ColumnName );

2.再跑回圈，抓出值，填到EXCEL
sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue( dr22[j].ToString() );

但現在只能先用 .ToString() 來填值
可是產生的檔案，數值欄位，就無法用 sum()來加總，只能 用+ 來加總
因為是 文字欄位

https://ithelp.ithome.com.tw/upload/images/20230605/20104095RDJkR64Lig.png

因該是，要動態根據 資料類型，去填入值
謝謝

登入發表討論
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

2 個回答

0
alien663
iT邦研究生 3 級 ‧ 2023-06-06 08:56:33
最佳解答

在NPOI.SS.UserModel中有一個void SetCellType(CellType cellType)可以呼叫，CellType也有定義 :

public enum CellType : int
{
    Unknown = -1,
    Numeric = 0,
    String = 1,
    Formula = 2,
    Blank = 3,
    Boolean = 4,
    Error = 5
}

我自己的套件在使用後是沒有用處，我查看產出的Excel，每個Cell都是通用格式，或許你可以研究出怎樣調整成功。

我的套件並沒有針對DataTable的資料型態自動處理，只有DataModel會用泛型去因應不同的資料型態，並用不同的CellType產出。

別人的教學 : [C#][NPOI Library] 存取 Excel 檔案利器

我自己寫的套件 : Github : Alien663

看更多先前的回應...收起先前的回應...
noway iT邦研究生 1 級 ‧ 2023-06-06 16:32:49 檢舉

您好：
有參考您的範例，先取得型別，設定

sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");
sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(Convert.ToDouble(dr22[j].ToString()));

數值，有出來了
但格式 沒已跑出來，

目前18.00 與 36.01
呈現微
18
36.01

這樣不好看

alien663 iT邦研究生 3 級 ‧ 2023-06-06 16:37:31 檢舉

你似乎少了這行

cell.SetCellType(CellType.Numeric);

另外，你說的問題我有另外修正了一下，看看有沒有符合你的預期。

noway iT邦研究生 1 級 ‧ 2023-06-06 17:40:29 檢舉

您好：
我有原來程式 有加，但沒有效過
我先用之前的邏輯 來測試

     foreach (DataRow dr22 in dt22.Rows)
                    {
                        sheet.CreateRow(m_SkipRow);
                        
                        for ( int j=0; j< dt22.Columns.Count; j++  ) {


                            typex=getCellTypeFromType(dt22.Columns[j].DataType.Name);
                            sheet.GetRow(m_SkipRow).CreateCell(j).SetCellType(typex); //--設定
                                                             
                            if (typex == CellType.Numeric)
                            {

                                //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                                sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");
                                sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(Convert.ToDouble(dr22[j].ToString()));

                                


                            }
                            else {
                                sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(dr22[j].ToString());
                            }

                            
                            sheet.SetColumnWidth(j, (dr22[j].ToString().Trim().Length +3 )  * 256);

                        }//--for

                        /*
                            //Format格式為數字
                            cs.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                            //Format格式為百分比小數兩位
                            cs.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

                        */
                        m_SkipRow += 1;  //--跳一行
                    }//--foreach (DataRow dr in dt.Rows)
noway iT邦研究生 1 級 ‧ 2023-06-06 17:41:49 檢舉 
 static private CellType getCellTypeFromType(string datatypename)
        {
            switch (datatypename)
            {
                case "Decimal":
                case "UInt16":
                case "UInt32":
                case "UInt64":
                case "Int16":
                case "Int32":
                case "Int64":
                    return CellType.Numeric;
                case "Boolean":
                    return CellType.Boolean;
                default:
                    return CellType.String;
            }
        }
alien663 iT邦研究生 3 級 ‧ 2023-06-07 08:59:36 檢舉

我有注意到你的資料用的是小數，我當時寫工具時沒注意到，所以前面提到更新的部分就是針對這件事情去做的。

for (int i = 0; i < source.Rows.Count; i++)
{
    IRow rows = sheet.CreateRow(headerIndex + 1 + i);
    for (int j = 0; j < source.Columns.Count; j++)
    {
        ICell cell = rows.CreateCell(j);
        switch (source.Columns[j].DataType.Name)
        {
            case "UInt16":
            case "UInt32":
            case "UInt64":
            case "Int16":
            case "Int32":
            case "Int64":
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(int.Parse(source.Rows[i][j].ToString()));
                break;
            case "Boolean":
                cell.SetCellType(CellType.Boolean);
                cell.SetCellValue(Convert.ToBoolean(source.Rows[i][j].ToString()));
                break;
            case "Double":
            case "Decimal":
                cell.SetCellType(CellType.Numeric);
                cell.SetCellValue(Convert.ToDouble(source.Rows[i][j].ToString()));
                break;

            default:
                cell.SetCellType(CellType.String);
                cell.SetCellValue(source.Rows[i][j].ToString());
                break;
        }
    }
}
noway iT邦研究生 1 級 ‧ 2023-06-11 14:12:39 檢舉

您好：
我加了
sheet.GetRow(m_SkipRow).CreateCell(j).SetCellType(CellType.Numeric);
sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(Convert.ToDouble(dr22[j].ToString()));

結果還是
18
36.01
19

他並不會
18.00
36.01
19.00

謝謝

alien663 iT邦研究生 3 級 ‧ 2023-06-12 10:46:47 檢舉

我測試過後，小數點的部分需要以下的程式碼更改Format。

ICellStyle _doublestyle = workbook.CreateCellStyle();
_doublestyle.DataFormat = workbook.CreateDataFormat().GetFormat("0.0000");
cell.CellStyle = _doublestyle;
noway iT邦研究生 1 級 ‧ 2023-07-04 14:36:05 檢舉

您好：
謝謝， 但我試了， 一樣沒有效@@

https://ithelp.ithome.com.tw/upload/images/20230704/201040951cRZBArcWX.png

alien663 iT邦研究生 3 級 ‧ 2023-07-05 15:40:22 檢舉

我測試過了，有可能是因為你的DataTable一開始記錄DataType就只有String，所以怎樣設定都是錯的。
我的測試資料設定如下，取出來的資料如圖。https://ithelp.ithome.com.tw/upload/images/20230705/20153982vaJBtk2rJ1.jpg

dtStudent = new DataTable();
            dtStudent.Columns.Add("Name");
            dtStudent.Columns.Add("Age", typeof(double));
            dtStudent.Columns.Add("StudentId", typeof(int));
            dtStudent.Columns.Add("Birth", typeof(DateOnly));
            dtStudent.Columns.Add("TestTime", typeof(TimeOnly));
            dtStudent.Columns.Add("UpdateTime", typeof(DateTime));
noway iT邦研究生 1 級 ‧ 2023-07-07 16:53:47 檢舉

您好：
謝謝
因為我是動態產生欄位，所已沒有一個一個設定 dtStudent.Columns.Add("Age", typeof(double));
這樣

而是

  static private void setSheet_data(DataRow dr, ISheet sheet, ICellStyle cellStyle, ICellStyle cellStyle_double)
        {
            //------------------設定Sheet 資料公用程式
            int m_SkipRow = 0; //---從哪一行開始，後續+1，跳一行

            SqlConnection connection22 = new SqlConnection();
            connection22.ConnectionString = con_str;

            //----根據傳入的 dr  來做QUERY ，設定sheet

            //----1.執行SQL，跑回圈
            //----2.設定標題
            //----3.迴圈欄位

            //------------
            string dbname = dr["DBNAME"].ToString();
            string queryX = dr["query"].ToString();
            queryX = "use " + dbname + "; " + queryX;

            //-----1.先抓取要 執行的資料
            SqlCommand command = new SqlCommand();
            command.CommandText = queryX;



            using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command.CommandText, connection22))
            {
                DataTable dt22 = new DataTable();
                dataAdapter.Fill(dt22);

          


                if (dt22.Rows.Count > 0)
                {

                    //---後面要抓欄位 型態
                    PropertyInfo[] Props = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);

                    Int32 h = 0;   //----標題

                    //建制head
                    //建立第一列，通常拿來設定標題

                    sheet.CreateRow(m_SkipRow);
                    foreach (DataColumn col in dt22.Columns)
                    {

                        sheet.GetRow(m_SkipRow).CreateCell(h).SetCellValue(col.ColumnName);
                        h += 1;
                    }//--




                    CellType typex; //--類別
                    m_SkipRow += 1; //--明細 ，先跳一行
                    //-------2.有資料，就產生workbook
                    foreach (DataRow dr22 in dt22.Rows)
                    {
                        sheet.CreateRow(m_SkipRow);

                        for (int j = 0; j < dt22.Columns.Count; j++)
                        {


                            typex = getCellTypeFromType(dt22.Columns[j].DataType.Name);

                            sheet.GetRow(m_SkipRow).CreateCell(j).SetCellType(typex); //--設定
                            if (typex == CellType.Numeric)
                            {

                                //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                                //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");


                              
                           


                                //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle = cellStyle_double;
                                //cell.setCellStyle(cellStyle);
                                sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle = cellStyle_double;
                                sheet.GetRow(m_SkipRow).CreateCell(j).SetCellType(CellType.Numeric);

                                sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(Convert.ToDouble(dr22[j].ToString()));
                                

                                /*
                           
    ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, Double.valueOf(df.format(10.251)), "#,###.##");
                                */


                            }
                            else
                            {
                                
                                sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(dr22[j].ToString());
                            }


                            sheet.SetColumnWidth(j, (dr22[j].ToString().Trim().Length + 3) * 256);

                        }//--for

                        /*
                            //Format格式為數字
                            cs.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                            //Format格式為百分比小數兩位
                            cs.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

                        */
                        m_SkipRow += 1;  //--跳一行
                    }//--foreach (DataRow dr in dt.Rows)

                }//---IF

            }//--USING


        }//--        static private void setSheet_data
alien663 iT邦研究生 3 級 ‧ 2023-07-10 09:36:58 檢舉

我個別設定欄位的Data Type，是因為我寫單元測試希望是比較pure的環境做測試，所以是直接寫固定的資料才會那樣做，模擬從資料庫拿到資料時的Table。

所以就我的觀察，你可能需要針對以下程式碼設定中斷點，看看你的資料是不是進入你想要的condition，如果每次設定CellType都失敗，那我猜可能資料都是跑到elses那邊去了。

if (typex == CellType.Numeric)
                    {
                        //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
                        //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0.00");
                        //sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle = cellStyle_double;
                        //cell.setCellStyle(cellStyle);
                        sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle = cellStyle_double;
                        sheet.GetRow(m_SkipRow).CreateCell(j).SetCellType(CellType.Numeric);
                        sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(Convert.ToDouble(dr22[j].ToString()));
                        // ExcelNumericFormat.applyNumericFormat(outWorkbook, row, cell, Double.valueOf(df.format(10.251)), "#,###.##");
                    }
                    else
                    {
                        sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue(dr22[j].ToString());
                    }
noway iT邦研究生 1 級 ‧ 2023-07-26 16:54:41 檢舉

您好：
謝謝您
但我實際測試，他都有跑進去 typex == CellType.Numeric 這一段

https://ithelp.ithome.com.tw/upload/images/20230726/20104095xs9OqS1FoK.png

alien663 iT邦研究生 3 級 ‧ 2023-07-27 08:36:54 檢舉

我後來繼續研究，發現cell.SetCellType並不是影響最終顯示在excel結果的語法，實際上要讓他以小數後二位顯示，還是要靠format，如以下程式碼:

ICellStyle _doublestyle = workbook.CreateCellStyle();
        _doublestyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");

比較完整的範例如下，我也有同步更新我的Github專案

switch (source.Columns[j].DataType.Name)
{
    case "UInt16":
    case "UInt32":
    case "UInt64":
    case "Int16":
    case "Int32":
    case "Int64":
        ICellStyle _intstyle = workbook.CreateCellStyle();
        _intstyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0");
        cell.CellStyle = _intstyle; 
        cell.SetCellType(CellType.Numeric);
        cell.SetCellValue(int.Parse(source.Rows[i][j].ToString()));
        break;
    case "Boolean":
        cell.SetCellType(CellType.Boolean);
        cell.SetCellValue(Convert.ToBoolean(source.Rows[i][j].ToString()));
        break;
    case "Double":
    case "Decimal":
        ICellStyle _doublestyle = workbook.CreateCellStyle();
        _doublestyle.DataFormat = workbook.CreateDataFormat().GetFormat("#,##0.00");
        cell.CellStyle = _doublestyle;
        cell.SetCellType(CellType.Numeric);
        cell.SetCellValue(Convert.ToDouble(source.Rows[i][j].ToString()));
        break;
    default:
        cell.SetCellType(CellType.String);
        cell.SetCellValue(source.Rows[i][j].ToString());
        break;
}
noway iT邦研究生 1 級 ‧ 2023-07-27 10:58:32 檢舉

您好：
謝謝
這我之前也有先弄了GetFormat

但我發現有一段，我每一個CELL 都用
sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle.DataFormat
sheet.GetRow(m_SkipRow).CreateCell(j).CellStyle
重複sheet.GetRow(m_SkipRow).CreateCell(j) 設定

後來改成
ICell s = sheet.GetRow(m_SkipRow).CreateCell(j);
s.SetCellType(typex);

其他設定都用
s.DataFormat
s.CellStyle

來做設定就過了
謝謝

登入發表回應
0
allenlwh
iT邦高手 1 級 ‧ 2023-06-06 00:30:26 
cell1 = sheet32.GetRow(rowIndex).CreateCell(i);
cell1.SetCellValue(Convert.ToDouble(reader[i].ToString()));

https://ithelp.ithome.com.tw/upload/images/20230606/20033493ra84bSANe1.jpghttps://ithelp.ithome.com.tw/upload/images/20230606/20033493b7f4uVz9ex.jpg

noway iT邦研究生 1 級 ‧ 2023-06-06 08:34:14 檢舉

您好：
目前應該是說，我從資料庫 撈出的資料
COL01 是 字串
COL02 是 DNMERIC(18,2)
COL03 是 DATE

以上只是某一段XLSX 內的一個SHEET，
因為目前是動態抓資料，動態放到 SEHHT
比如，另一個shet
姓名 STRING
年齡 12
成績 99.21

我要將 取得的資料，放到CELL 時，
sheet.GetRow(m_SkipRow).CreateCell(j).SetCellValue( dr22[j].ToString() );

要如何動態設定做到 CONVERT
謝謝

登入發表回應

我要發表回答

立即登入回答
iThome鐵人賽
參賽組數
1064 組
團體組數
40 組
累計文章數
22199 篇
完賽人數
602 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 16th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 javascript 2018鐵人賽 python 2017鐵人賽 windows php c# windows server linux css react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙