private HSSF.UserModel.HSSFWorkbook V1() { HSSF.UserModel.HSSFWorkbook wb = new HSSF.UserModel.HSSFWorkbook(); SS.UserModel.ISheet sheet = wb.CreateSheet("Sheet1"); SS.UserModel.IRow row = sheet.CreateRow(0); row.CreateCell(0); row.CreateCell(1); row.Cells[0].SetCellValue("t"); row.Cells[1].SetCellValue("p"); //row.CreateCell(0).SetCellValue("t");也可以這樣寫 row = sheet.CreateRow(1); row.CreateCell(0); row.CreateCell(1); row.Cells[0].SetCellValue("1"); row.Cells[1].SetCellValue("0"); //不用再add回sheet. row = sheet.CreateRow(2); row.CreateCell(0); row.CreateCell(1); row.Cells[0].SetCellValue("b"); row.Cells[1].SetCellValue("w"); //不用再add回sheet. return(wb); }
public void show(string sfile) { FileStream file = new FileStream(sfile, FileMode.Open, FileAccess.Read); // 開啟讀取樣版檔 HSSFWorkbook HssfWorkbook = new HSSFWorkbook(file); //.先取得Excel檔案的所有Stream資料,這邊取得Stream資料是包含EXCEL檔案中所有Sheet的資料流。 //透過HSSFWorkbook類別抓取整份Excel檔案抓取Stream 物件,其中fs為stream 物件 SS.UserModel.ISheet Sheet = HssfWorkbook.GetSheetAt(0); // 取得Index為0的Sheet//在NPOI每個Sheet都是一個陣列中的物件,故可以用Index去取 得相關Sheet SS.UserModel.ICellStyle CellStyle = HssfWorkbook.CreateCellStyle(); // 產生欄位樣式設定 SS.UserModel.IFont Font = HssfWorkbook.CreateFont(); // 產生字體樣式設定 }
private System.Data.DataTable ReadSheet(SS.UserModel.ISheet sheet) { //IRow row = sheet.GetRow(i);//get row DataTable oDT = new DataTable(); //由第一列取標題做為欄位名稱 SS.UserModel.IRow headerRow = sheet.GetRow(0); //get head int cellCount = headerRow.LastCellNum; //get last col for (int i = headerRow.FirstCellNum; i < cellCount; i++) { //以欄位文字為名新增欄位,此處全視為字串型別以求簡化 oDT.Columns.Add( new DataColumn(headerRow.GetCell(i).StringCellValue)); } //略過第零列(標題列),一直處理至最後一列 for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) { SS.UserModel.IRow row = sheet.GetRow(i);//get row if (row == null) { continue; } DataRow dataRow = oDT.NewRow(); //依先前取得的欄位數逐一設定欄位內容 for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //如要針對不同型別做個別處理,可善用.CellType判斷型別 //再用.StringCellValue, .DateCellValue, .NumericCellValue...取值 //此處只簡單轉成字串 dataRow[j] = row.GetCell(j).ToString(); } } oDT.Rows.Add(dataRow); } return(oDT); }