使用NPOI隨意建立Excel(含下拉式清單)

標籤：

 1   //建立活頁簿 2             HSSFWorkbook ssfworkbook = new HSSFWorkbook(); 3             //建立工作表(頁) 4             HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1"); 5             //建立一行 6             HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0); 7             //設定表頭 8             headerRow.CreateCell(0).SetCellValue("ID"); 9             //設定表頭的寬度 10             sheet1.SetColumnWidth(0, 15 * 256);11 #region     添加顯示下拉式清單12             HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary");13             ssfworkbook.SetSheetHidden(1, true);//隱藏14             sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA");//列資料15             sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");16             sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");17             HSSFName range = ssfworkbook.CreateName();//建立名稱18             range.Reference = "ShtDictionary!$A$1:$A$3";//格式19             range.NameName = "dicRange";20             #endregion21 headerRow.CreateCell(1).SetCellValue("Selected");22             sheet1.SetColumnWidth(1, 15 * 256);23             //將下拉式清單添加24             CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1);25             DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");26             HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);27             sheet1.AddValidationData(dataValidate);28 29             headerRow.CreateCell(2).SetCellValue("VALUE");30             sheet1.SetColumnWidth(2, 15 * 256);31 32             //寫入資料33             //建立資料行34             HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);35             //填充資料36             dataRow.CreateCell(0).SetCellValue("1");//id37             dataRow.CreateCell(1).SetCellValue("");//選擇框38             dataRow.CreateCell(2).SetCellValue("值");//選擇框39        System.IO.MemoryStream ms = new System.IO.MemoryStream();40             ssfworkbook.Write(ms);41             string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";42             Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));43             Response.BinaryWrite(ms.ToArray());44             ms.Close();45             ms.Dispose();

 

使用NPOI隨意建立Excel(含下拉式清單)