Miles MS.HelloWorld

NPOI 是一套.NET 版本的POI Java，也是一套open source project。即使系統上沒有安裝office也能用簡單的方式讀寫 word,excel,ppt檔案。

 

以下Demo從無到有簡單產生Excel，並寫入memorystream的方法

/*----------Result----------
    ---------------------
    |ID	|Name	|Course |
    |1	|Miles1	|Math1  |
    |2	|Miles2	|Math2  |
    ---------------------
---------------------------*/

// Create Workbook and Specify Excel Version
// [.xlsx => Excel 2007+ (new XSSFWorkbook)], [.xls => Excel 97-2003 (new HSSFWorkbook)]
IWorkbook workbook = new XSSFWorkbook();

// Use workbook to Create Sheet
ISheet sheet = workbook.CreateSheet("sheetname");

// Use sheet to Create Row
sheet.CreateRow(0);

// Use sheet to Create Cell and Set Cell Value at top row of created
sheet.GetRow(0).CreateCell(0).SetCellValue("ID");
sheet.GetRow(0).CreateCell(1).SetCellValue("Name");
sheet.GetRow(0).CreateCell(2).SetCellValue("Course");

// Use sheet to Create New Row and Set Cell Value at each row of created
for (int i = 1; i < 3; i++)
{
    sheet.CreateRow(i);
    sheet.GetRow(i).CreateCell(0).SetCellValue(i);
    sheet.GetRow(i).CreateCell(1).SetCellValue("Miles" + i);
    sheet.GetRow(i).CreateCell(2).SetCellValue("Math" + i);
}

// Call MemoryStream to Write it
MemoryStream ms = new MemoryStream();

// note : Inside workbook.Write() had called ms.close(), so you can't read it from MemoryStream
//        if you want to output file, you should make MemoryStream's content Convert to Array
workbook.Write(ms);

當然，如你想套用現有的Excel template也是可以的，只要先讀取template，再Set Value 就好

// Load Exist Excel Template
string path = Server.MapPath("~/App_Data/ExcelTemplate/SampleExcel.xlsx");

FileStream fileStream = new FileStream(path, FileMode.Open, FileAccess.Read);

// Create Workbook and Specify Excel Version From Exist Excel file
// [.xlsx => Excel 2007+ (new XSSFWorkbook)], [.xls => Excel 97-2003 (new HSSFWorkbook)]
IWorkbook workbook = new XSSFWorkbook(fileStream);

// Use workbook to Get Exist Sheet , inside sheet that there are exist top row in common
ISheet sheet = workbook.GetSheetAt(0);

// Use sheet to Create New Row and Set Cell Value at each row of created
for (int i = 1; i < 3; i++)
{
    sheet.CreateRow(i);
    sheet.GetRow(i).CreateCell(0).SetCellValue("Mary" + i);
    sheet.GetRow(i).CreateCell(1).SetCellValue("female" + i);
    sheet.GetRow(i).CreateCell(2).SetCellValue("2" + i);
}

// Call MemoryStream to Write it
MemoryStream ms = new MemoryStream();

// note : Inside workbook.Write() had called ms.close(), so you can't read it from MemoryStream
//        if you want to output file, you should make MemoryStream's content Convert to Array
workbook.Write(ms);

 

在這邊附上範例Code Snippet​，點擊下載。

使用方法請參考 https://dotblogs.com.tw/mileslin/2015/12/05/003448

 

 

一天一分享，身體好健康。

該追究的不是過去的原因，而是現在的目的。