[C#] 利用NPOI進行EXCEL套表(樞紐分析表)

首先下載NPOI套件
https://npoi.codeplex.com/releases

 

   1:  

   2:         private DataTable ProductInfo;

   3:         private void createDataTable() {

   4:  

   5:             ProductInfo = new DataTable();

   6:  

   7:             ProductInfo.Clear();

   8:             ProductInfo.Columns.Add("_Product");

   9:             ProductInfo.Columns.Add("_WH");

  10:             ProductInfo.Columns.Add("_Qty");

  11:  

  12:             DataRow ProductRow = ProductInfo.NewRow();

  13:             ProductRow["_Product"] = "AS133";

  14:             ProductRow["_WH"] = "W101";

  15:             ProductRow["_Qty"] = "10";

  16:             ProductInfo.Rows.Add(ProductRow);

  17:  

  18:             ProductRow = ProductInfo.NewRow();

  19:             ProductRow["_Product"] = "AS133";

  20:             ProductRow["_WH"] = "W102";

  21:             ProductRow["_Qty"] = "7";

  22:             ProductInfo.Rows.Add(ProductRow);

  23:  

  24:             ProductRow = ProductInfo.NewRow();

  25:             ProductRow["_Product"] = "AS133";

  26:             ProductRow["_WH"] = "W103";

  27:             ProductRow["_Qty"] = "5";

  28:             ProductInfo.Rows.Add(ProductRow);

  29:  

  30:             ProductRow = ProductInfo.NewRow();

  31:             ProductRow["_Product"] = "AS156";

  32:             ProductRow["_WH"] = "W101";

  33:             ProductRow["_Qty"] = "6";

  34:             ProductInfo.Rows.Add(ProductRow);

  35:  

  36:             ProductRow = ProductInfo.NewRow();

  37:             ProductRow["_Product"] = "TS156";

  38:             ProductRow["_WH"] = "W101";

  39:             ProductRow["_Qty"] = "8";

  40:             ProductInfo.Rows.Add(ProductRow);

  41:  

  42:             ProductRow = ProductInfo.NewRow();

  43:             ProductRow["_Product"] = "TS156";

  44:             ProductRow["_WH"] = "W102";

  45:             ProductRow["_Qty"] = "8";

  46:             ProductInfo.Rows.Add(ProductRow);

  47:         }

   1:  

   2:         private void button1_Click(object sender, EventArgs e)

   3:         {

   4:             createDataTable();

   5:  

   6:             string strFilePath = string.Format("template.xlt");

   7:             HSSFWorkbook workbook;

   8:             using (FileStream fs = new FileStream(strFilePath, FileMode.Open, FileAccess.ReadWrite))

   9:             {

  10:                 workbook = new HSSFWorkbook(fs);

  11:                 fs.Close();

  12:             }

  13:  

  14:             if (workbook != null)

  15:             {

  16:                 //load template

  17:                 HSSFSheet RawData = (HSSFSheet)workbook.GetSheet("RawData");

  18:  

  19:                 //data to newSheet 

  20:                 HSSFCell hc;

  21:                 HSSFRow hr;

  22:                 HSSFSheet hst = RawData;

  23:  

  24:                 for (int i = 0; i < ProductInfo.Rows.Count; i++)

  25:                 {

  26:                     hr = (HSSFRow)hst.CreateRow(i + 1);

  27:                     for (int j = 0; j < ProductInfo.Columns.Count; j++)

  28:                     {

  29:                         hc = (HSSFCell)hr.CreateCell(j);

  30:                         //Notice!!! Qty is Int,other String.

  31:                         if (ProductInfo.Columns[j].Caption == "_Qty")

  32:                         {

  33:                             hc.SetCellType(CellType.Numeric);

  34:                             if (!string.IsNullOrEmpty(ProductInfo.Rows[i][j].ToString()))

  35:                             {

  36:                                 int number = Convert.ToInt32(ProductInfo.Rows[i][j].ToString());

  37:                                 hc.SetCellValue(number);

  38:                             }

  39:                             else

  40:                             {

  41:                                 hc.SetCellValue(ProductInfo.Rows[i][j].ToString());

  42:                             }

  43:                         }

  44:                         else

  45:                         {

  46:                             hc.SetCellValue(ProductInfo.Rows[i][j].ToString());

  47:                         }

  48:                     }

  49:                 }

  50:  

  51:                 //export new EXCEL file

  52:                 String filename = "P_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xls";

  53:                 FileStream fsExcelNew = new FileStream(filename, FileMode.Create);

  54:                 workbook.Write(fsExcelNew);

  55:  

  56:                 //remove sheet

  57:                 workbook.RemoveSheetAt(0);

  58:                 workbook = null;

  59:  

  60:                 fsExcelNew.Close();

  61:  

  62:                 //Open excel

  63:                 //System.Windows.Forms.Application.StartupPath + "\\" + filename;

  64:                  string file = @"C:\Windows\explorer.exe";

  65:                  System.Diagnostics.Process.Start(file, filename);

  66:             }

  67:         }

https://github.com/xxzeng/NPOIdemo/