public void AddDropDownListToCell(ISheet sheet, ICell cell, string[] list) { CellRangeAddressList cellRange = new CellRangeAddressList(cell.RowIndex, cell.RowIndex, cell.ColumnIndex, cell.ColumnIndex); DVConstraint constraint = null; if (string.Join("", list).Length < 200) { constraint = DVConstraint.CreateExplicitListConstraint(list); } else { var workBook = sheet.Workbook; var hiddenSheet = workBook.GetSheet("hidden") ?? workBook.CreateSheet("hidden"); workBook.SetSheetHidden(workBook.GetSheetIndex("hidden"), SheetState.Hidden); var rowsCount = hiddenSheet.PhysicalNumberOfRows; for (int i = 0; i < list.Length; i++) { hiddenSheet.CreateRow(rowsCount + i).CreateCell(0).SetCellValue(list[i]); } var formula = string.Format("hidden!$A{0}:$A{1}", rowsCount + 1, rowsCount + list.Length); constraint = DVConstraint.CreateFormulaListConstraint(formula); } HSSFDataValidation validation = new HSSFDataValidation(cellRange, constraint); ((HSSFSheet)sheet).AddValidationData(validation); }
private DVConstraint CreateConstraint(int operatorType, String firstFormula, String secondFormula, String[] explicitListValues) { if (_validationType == DVConstraint.ValidationType.LIST) { if (explicitListValues != null) { return(DVConstraint.CreateExplicitListConstraint(explicitListValues)); } return(DVConstraint.CreateFormulaListConstraint(firstFormula)); } if (_validationType == DVConstraint.ValidationType.TIME) { return(DVConstraint.CreateTimeConstraint(operatorType, firstFormula, secondFormula)); } if (_validationType == DVConstraint.ValidationType.DATE) { return(DVConstraint.CreateDateConstraint(operatorType, firstFormula, secondFormula, null)); } if (_validationType == DVConstraint.ValidationType.FORMULA) { return(DVConstraint.CreateCustomFormulaConstraint(firstFormula)); } return(DVConstraint.CreateNumericConstraint(_validationType, operatorType, firstFormula, secondFormula)); }
/// <summary> /// 生成老师导入表格 /// </summary> /// <param name="columnName"></param> /// <param name="col"></param> /// <returns></returns> public static string BuildTchExcel(List <string> columnName, List <string> col) { MemoryStream ms = new MemoryStream(); IWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); IRow headerRow = sheet.CreateRow(0); for (int i = 0; i < columnName.Count; i++) { headerRow.CreateCell(i).SetCellValue(columnName[i]); } //设置生成下拉框的行和列 var cellRegions = new CellRangeAddressList(1, 65535, 2, 2); //设置科目 下拉框内容 DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(col.ToArray()); //绑定下拉框和作用区域,并设置错误提示信息 HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate); //设置生成性别下拉框的行和列 var cellRegions1 = new CellRangeAddressList(1, 65535, 4, 4); //设置 下拉框内容 DVConstraint constraint1 = DVConstraint.CreateExplicitListConstraint(new String[] { "男", "女" }); //绑定下拉框和作用区域,并设置错误提示信息 HSSFDataValidation dataValidate1 = new HSSFDataValidation(cellRegions1, constraint1); dataValidate1.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate1.ShowPromptBox = true; sheet.AddValidationData(dataValidate1); //设置生成职称下拉框的行和列 var cellRegions2 = new CellRangeAddressList(1, 65535, 5, 5); //设置 下拉框内容 DVConstraint constraint2 = DVConstraint.CreateExplicitListConstraint(new String[] { "正高级教师", "高级教师", "一级教师", "二级教师", "三级教师" }); //绑定下拉框和作用区域,并设置错误提示信息 HSSFDataValidation dataValidate2 = new HSSFDataValidation(cellRegions2, constraint2); dataValidate2.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate2.ShowPromptBox = true; sheet.AddValidationData(dataValidate2); workbook.Write(ms); string fileName = "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd") + "\\" + "教师" + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls"; if (!Directory.Exists(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd"))) { Directory.CreateDirectory(System.AppDomain.CurrentDomain.BaseDirectory + "Excel\\" + DateTime.Now.ToString("yyyy-MM-dd")); } var f = File.Create(System.AppDomain.CurrentDomain.BaseDirectory + fileName); ms.WriteTo(f); ms.Close(); f.Close(); return(fileName); }
public static byte[] ModifyTemplete(string fileName, string[] deptList, string[] dutiesList) { IWorkbook workbook = null; //全局workbook ISheet sheet; //sheet try { FileInfo fileInfo = new FileInfo(fileName);//判断文件是否存在 if (fileInfo.Exists) { FileStream fileStream = fileInfo.OpenRead();//打开文件,得到文件流 switch (fileInfo.Extension) { //xls是03,用HSSFWorkbook打开,.xlsx是07或者10用XSSFWorkbook打开 case ".xls": workbook = new HSSFWorkbook(fileStream); break; case ".xlsx": workbook = new XSSFWorkbook(fileStream); break; default: break; } fileStream.Close();//关闭文件流 } if (workbook == null) { return(null); } sheet = workbook.GetSheetAt(0); if (deptList != null && deptList.Length > 0) { CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 2, 2); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(deptList); HSSFDataValidation validate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(validate); } if (dutiesList != null && dutiesList.Length > 0) { CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 4, 4); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(dutiesList); HSSFDataValidation validate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(validate); } MemoryStream ms = new MemoryStream(); workbook.Write(ms); byte[] buf = ms.ToArray(); return(buf); } catch { } return(null); }
public static void SetCellDropdownList(ISheet sheet, int firstcol, int lastcol, string[] vals) { //设置生成下拉框的行和列 var cellRegions = new CellRangeAddressList(1, 65535, firstcol, lastcol); //设置 下拉框内容 DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(vals); //绑定下拉框和作用区域,并设置错误提示信息 HSSFDataValidation dataValidate = new HSSFDataValidation(cellRegions, constraint); dataValidate.CreateErrorBox("输入不合法", "请输入或选择下拉列表中的值。"); dataValidate.ShowPromptBox = true; sheet.AddValidationData(dataValidate); }
private void CellDataWriter1(int row, int col, string FilePath, string SheetName) { var fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite); var templateWorkbook = new HSSFWorkbook(fs); var sheet = (HSSFSheet)templateWorkbook.GetSheet(SheetName); int r = row; string result1 = TempData["res1"].ToString(); string result2 = TempData["res2"].ToString(); string result = TempData["res"].ToString(); int c = col; CellRangeAddressList addressList1 = new CellRangeAddressList(1, 50, 1, 1); DVConstraint dvConstraint1 = DVConstraint.CreateExplicitListConstraint( new String[] { result1 }); HSSFDataValidation dataValidation1 = new HSSFDataValidation(addressList1, dvConstraint1); dataValidation1.SuppressDropDownArrow = false; ((HSSFSheet)sheet).AddValidationData(dataValidation1); CellRangeAddressList addressList2 = new CellRangeAddressList(1, 50, 2, 2); DVConstraint dvConstraint2 = DVConstraint.CreateExplicitListConstraint( new String[] { result2 }); HSSFDataValidation dataValidation2 = new HSSFDataValidation(addressList2, dvConstraint2); dataValidation2.SuppressDropDownArrow = false; ((HSSFSheet)sheet).AddValidationData(dataValidation2); CellRangeAddressList addressList = new CellRangeAddressList(1, 50, 4, 4); DVConstraint dvConstraint = DVConstraint.CreateExplicitListConstraint( new String[] { result }); HSSFDataValidation dataValidation = new HSSFDataValidation(addressList, dvConstraint); dataValidation.SuppressDropDownArrow = false; ((HSSFSheet)sheet).AddValidationData(dataValidation); fs = new FileStream(FilePath, FileMode.Open, FileAccess.ReadWrite); templateWorkbook.Write(fs); fs.Close(); }
public void ListTest() { var path = @"C:\1.xls"; var Workbook = new HSSFWorkbook(); var Sheet = Workbook.CreateSheet("Sheet1"); CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" }); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); Sheet.AddValidationData(dataValidate); Sheet.GetRow(0).GetCell(0).SetCellValue("itemB"); using (FileStream fs = new FileStream(path, FileMode.OpenOrCreate)) { Workbook.Write(fs); } }
private string d2003() { string fileName = "G:/" + DateTime.Now.Ticks.ToString() + ".xls"; HSSFWorkbook wk = new HSSFWorkbook(); ISheet sheet = wk.CreateSheet(); CellRangeAddressList regions = new CellRangeAddressList(0, 100, 2, 2); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA", "itemB", "itemC" }); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); using (FileStream fs = File.OpenWrite(fileName)) { wk.Write(fs); } return(fileName); }
public void CreateDataValidation(string filePath, int sheetIndex, string[] lstContrains, int fromRow, int fromColumn, int toRow, int toColumn) { FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); if (hssfworkbook != null) { CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0 DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(lstContrains); HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex - 1); if (sheet != null) { sheet.AddValidationData(dataValidation); file = new FileStream(filePath, FileMode.Create); hssfworkbook.Write(file); file.Close(); } } }
/// <summary> /// 导出Excel数据填充 /// </summary> /// <param name="dt"></param> /// <param name="workBook"></param> private static void DataTableFillWorkBook(DataTable dt, IWorkbook workBook, Dictionary <int, List <string> > dic = null) { var sheetName = dt.TableName; if (string.IsNullOrWhiteSpace(sheetName)) { sheetName = string.Format("sheet{0}", workBook.NumberOfSheets + 1); } ISheet sheet = workBook.CreateSheet(sheetName); if (dic != null && dic.Count > 0) { foreach (var item in dic.Keys) { if (dic[item] != null && dic[item].Count > 0) { CellRangeAddressList regions = new CellRangeAddressList(1, 65535, item, item); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(dic[item].ToArray()); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); } } } IRow firstRow = sheet.CreateRow(0); for (int i = 0; i < dt.Columns.Count; i++) { firstRow.CreateCell(i, CellType.String).SetCellValue(dt.Columns[i].ColumnName); } for (int i = 0; i < dt.Rows.Count; i++) { IRow row = sheet.CreateRow(i + 1); DataRow dtRow = dt.Rows[i]; for (int j = 0; j < dt.Columns.Count; j++) { row.CreateCell(j, CellType.String).SetCellValue(dtRow[j].ToString()); } } }
public static void test2() { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); HSSFSheet sheet1 = hssfworkbook.CreateSheet("Sheet1") as HSSFSheet; CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 0, 0); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "itemA111", "itemB22", "itemC33" }); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet1.AddValidationData(dataValidate); MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); string workbookFile = @"D:\\wulei1111.xls"; hssfworkbook = null; FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write); byte[] data = ms.ToArray(); fs.Write(data, 0, data.Length); fs.Flush(); fs.Close(); }
/// <summary> /// 設定匯出下拉選單功能 /// </summary> private ISheet setCellExplicitList(ISheet sheet) { //發送系統 DVConstraint sendSystemConstraint = DVConstraint.CreateExplicitListConstraint(MSPViewModel.export_ddl_sendSystem_arr); CellRangeAddressList sendSystemRegion = new CellRangeAddressList(3, 655365, 5, 5); HSSFDataValidation sendSystemDataValidate = new HSSFDataValidation(sendSystemRegion, sendSystemConstraint); //發送等級 DVConstraint sendLevelConstraint = DVConstraint.CreateExplicitListConstraint(MSPViewModel.export_ddl_sendLevel_arr); CellRangeAddressList sendLevelRegion = new CellRangeAddressList(3, 655365, 6, 6); HSSFDataValidation sendLevelDataValidate = new HSSFDataValidation(sendLevelRegion, sendLevelConstraint); //訊息分類 DVConstraint messageCategoryConstraint = DVConstraint.CreateExplicitListConstraint(MSPViewModel.export_ddl_msgCategory_arr); CellRangeAddressList messageCategoryRegion = new CellRangeAddressList(3, 655365, 7, 7); HSSFDataValidation messageCategoryDataValidate = new HSSFDataValidation(messageCategoryRegion, messageCategoryConstraint); //名單類型 DVConstraint listTypeConstraint = DVConstraint.CreateExplicitListConstraint(MSPViewModel.export_ddl_messageType_arr); CellRangeAddressList listTypeRegion = new CellRangeAddressList(3, 655365, 10, 10); HSSFDataValidation listTypeDataValidate = new HSSFDataValidation(listTypeRegion, listTypeConstraint); //來源名單 DVConstraint listSourceConstraint = DVConstraint.CreateExplicitListConstraint(MSPViewModel.export_ddl_listSource_arr); CellRangeAddressList listSourceRegion = new CellRangeAddressList(3, 655365, 23, 23); HSSFDataValidation listSourceDataValidate = new HSSFDataValidation(listSourceRegion, listSourceConstraint); //發送種類 DVConstraint sendTypeConstraint = DVConstraint.CreateExplicitListConstraint(MSPViewModel.export_ddl_sendType_arr); CellRangeAddressList sendTypeRegion = new CellRangeAddressList(3, 655365, 25, 25); HSSFDataValidation sendTypeDataValidate = new HSSFDataValidation(sendTypeRegion, sendTypeConstraint); sheet.AddValidationData(sendSystemDataValidate); sheet.AddValidationData(sendLevelDataValidate); sheet.AddValidationData(messageCategoryDataValidate); sheet.AddValidationData(listTypeDataValidate); sheet.AddValidationData(listSourceDataValidate); sheet.AddValidationData(sendTypeDataValidate); return(sheet); }
/// <summary> /// Create data validation /// Hungnd 04/04/2013 /// </summary> /// <param name="sheetIndex"></param> /// <param name="fromRow"></param> /// <param name="fromCol"></param> /// <param name="toRow"></param> /// <param name="toCol"></param> /// <param name="validationType">The value in VTValidationType</param> /// <param name="fromValue"></param> /// <param name="toValue"></param> /// <param name="errorTitle">Default "Error value"</param> /// <param name="errorMessage">Default "Error input value"</param> /// <param name="errorStyle">Default ErrorStyle.STOP</param> /// <param name="lstContrains">Default null</param> public void CreateCellValidation(int sheetIndex, int fromRow, int fromColumn, int toRow, int toColumn, int validationType, string fromValue, string toValue, string[] lstContrains = null, string errorTitle = "Error value", string errorMessage = "Error input value", int errorStyle = ErrorStyle.STOP ) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(sfile); if (hssfworkbook != null) { CellRangeAddressList rangeList = new CellRangeAddressList(fromRow - 1, toRow - 1, fromColumn - 1, toColumn - 1); // Vì NPOI sử dụng row và column bắt đầu từ 0 DVConstraint dvconstraint = null; if (validationType.Equals(VTValidationType.LIST)) { dvconstraint = DVConstraint.CreateExplicitListConstraint(lstContrains); } else { dvconstraint = DVConstraint.CreateNumericConstraint(validationType, OperatorType.BETWEEN, fromValue, toValue); } HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); if (!validationType.Equals(VTValidationType.LIST)) { dataValidation.CreateErrorBox(errorTitle, errorMessage); dataValidation.ErrorStyle = errorStyle; } HSSFSheet sheet = (HSSFSheet)hssfworkbook.GetSheetAt(sheetIndex); if (sheet != null) { sheet.AddValidationData(dataValidation); sfile = new FileStream(FilePath, FileMode.Create); hssfworkbook.Write(sfile); } } }
/// <summary> /// 生成备课资源表 /// </summary> /// <param name="sender"></param> /// <param name="e"></param> private void button5_Click(object sender, EventArgs e) { IWorkbook wb = new HSSFWorkbook(); #region 创建基础数据表 ISheet sh = wb.CreateSheet("基础数据"); List <string> headtitles = new List <string>(); headtitles.Add("学科"); headtitles.Add("年级"); headtitles.Add("学段"); headtitles.Add("册别"); headtitles.Add("教学环节"); headtitles.Add("适用对象"); headtitles.Add("新教学模块"); headtitles.Add("英语教学模块"); headtitles.Add("数学教学模块"); headtitles.Add("语文教学模块"); headtitles.Add("资源权限"); headtitles.Add("资源评级"); headtitles.Add("资源来源"); headtitles.Add("资源推荐"); headtitles.Add("资源类型"); headtitles.Add("版本"); headtitles.Add("教学形式"); headtitles.Add("题型"); headtitles.Add("词库"); headtitles.Add("版权信息"); headtitles.Add("课时"); headtitles.Add("课例类型");// //-----新增资源大类------- headtitles.Add("资源大类"); headtitles.Add("语文_001"); headtitles.Add("数学_002"); headtitles.Add("英语_003"); for (int i = 0; i < 500; i++) { sh.CreateRow(i); } for (int i = 0; i < metadataLists.Count - 1; i++)//前0—21列表 { List <String> subList = (List <String>)metadataLists[i]; sh.GetRow(0).CreateCell(i).SetCellValue(headtitles[i]); for (int j = 1; j <= subList.Count; j++) { sh.GetRow(j).CreateCell(i).SetCellValue(subList[j - 1]); } } sh.GetRow(0).GetCell(19).SetCellValue(""); sh.GetRow(1).GetCell(19).SetCellValue(""); sh.GetRow(2).GetCell(19).SetCellValue(""); sh.GetRow(3).GetCell(19).SetCellValue(""); sh.GetRow(4).GetCell(19).SetCellValue(""); //List<List<String>> knowledgeLists = (List<List<String>>)metadataLists[22]; List <List <String> > knowledgeLists = (List <List <String> >)metadataLists[23]; for (int i = 0; i < knowledgeLists.Count; i++) { List <String> subknowledgeList = (List <String>)knowledgeLists[i]; //sh.GetRow(0).CreateCell(22 + i).SetCellValue(headtitles[22 + i]); sh.GetRow(0).CreateCell(23 + i).SetCellValue(headtitles[23 + i]); for (int j = 1; j <= subknowledgeList.Count; j++) { //sh.GetRow(j).CreateCell(22 + i).SetCellValue(subknowledgeList[j - 1]); sh.GetRow(j).CreateCell(23 + i).SetCellValue(subknowledgeList[j - 1]); } } #endregion #region 创建教材目录表 ISheet sh1 = wb.CreateSheet("教材目录"); for (int i = 0; i < 500; i++) { sh1.CreateRow(i); } for (int i = 0; i < catologList.Count; i++) { sh1.GetRow(i).CreateCell(0).SetCellValue(catologList[i]); } //创建通用设置表(适用对象,资源评级,资源评级,资源来源,资源推荐,教学形式) ISheet sh2 = wb.CreateSheet("通用设置"); IRow r0 = sh2.CreateRow(0);//标题 r0.CreateCell(0).SetCellValue("适用对象"); r0.CreateCell(1).SetCellValue("资源评级"); r0.CreateCell(2).SetCellValue("资源权限"); r0.CreateCell(3).SetCellValue("资源来源"); r0.CreateCell(4).SetCellValue("资源推荐"); r0.CreateCell(5).SetCellValue("教学形式"); IRow r1 = sh2.CreateRow(1); //内容 r1.CreateCell(0).SetCellValue(((List <String>)metadataLists[5])[0]); //适用对象固定内容 r1.CreateCell(1).SetCellValue(((List <String>)metadataLists[11])[0]); //资源评级固定内容 r1.CreateCell(2).SetCellValue(((List <String>)metadataLists[10])[0]); //资源评级固定内容 r1.CreateCell(3).SetCellValue(((List <String>)metadataLists[12])[1]); //资源来源固定内容 r1.CreateCell(4).SetCellValue(((List <String>)metadataLists[13])[0]); //资源推荐固定内容 r1.CreateCell(5).SetCellValue(((List <String>)metadataLists[16])[1]); //教学形式固定内容 CellRangeAddressList region00 = new CellRangeAddressList(1, 1, 0, 0); CellRangeAddressList region11 = new CellRangeAddressList(1, 1, 1, 1); CellRangeAddressList region22 = new CellRangeAddressList(1, 1, 2, 2); CellRangeAddressList region33 = new CellRangeAddressList(1, 1, 3, 3); CellRangeAddressList region44 = new CellRangeAddressList(1, 1, 4, 4); CellRangeAddressList region55 = new CellRangeAddressList(1, 1, 5, 5); DVConstraint constraint00 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[5]).ToArray()); DVConstraint constraint11 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[11]).ToArray()); DVConstraint constraint22 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[10]).ToArray()); DVConstraint constraint33 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[12]).ToArray()); DVConstraint constraint44 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[13]).ToArray()); DVConstraint constraint55 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[16]).ToArray()); HSSFDataValidation dataValidate00 = new HSSFDataValidation(region00, constraint00); HSSFDataValidation dataValidate11 = new HSSFDataValidation(region11, constraint11); HSSFDataValidation dataValidate22 = new HSSFDataValidation(region22, constraint22); HSSFDataValidation dataValidate33 = new HSSFDataValidation(region33, constraint33); HSSFDataValidation dataValidate44 = new HSSFDataValidation(region44, constraint44); HSSFDataValidation dataValidate55 = new HSSFDataValidation(region55, constraint55); sh2.AddValidationData(dataValidate00); sh2.AddValidationData(dataValidate11); sh2.AddValidationData(dataValidate22); sh2.AddValidationData(dataValidate33); sh2.AddValidationData(dataValidate44); sh2.AddValidationData(dataValidate55); #endregion #region 创建U1表 ISheet sheet = wb.CreateSheet("U1"); IRow row0 = sheet.CreateRow(0); row0.CreateCell(0).SetCellValue("序号"); row0.CreateCell(1).SetCellValue("显示名称"); row0.CreateCell(2).SetCellValue("内容说明"); row0.CreateCell(3).SetCellValue("资源类型"); //二级关联 14 row0.CreateCell(4).SetCellValue("教材名称"); //bookName sheet.CreateRow(1).CreateCell(4).SetCellValue(bookName.Substring(0, bookName.LastIndexOf("_"))); //固化教材名称,一行 row0.CreateCell(5).SetCellValue("教材目录"); //cataloglist row0.CreateCell(6).SetCellValue("教学环节"); //4 row0.CreateCell(7).SetCellValue("教学模块"); //语数英 row0.CreateCell(8).SetCellValue("知识点"); //语数英 row0.CreateCell(9).SetCellValue("关键字"); row0.CreateCell(10).SetCellValue("资源描述"); row0.CreateCell(11).SetCellValue("制作者"); row0.CreateCell(12).SetCellValue("检测者"); row0.CreateCell(13).SetCellValue("上传者"); row0.CreateCell(14).SetCellValue("文件路径"); row0.CreateCell(15).SetCellValue("缩略图路径"); row0.CreateCell(16).SetCellValue("版权信息");//19 //-----------新增“资源大类”20170213 row0.CreateCell(17).SetCellValue("资源大类"); CellRangeAddressList region3 = new CellRangeAddressList(0, 65535, 3, 3); CellRangeAddressList region5 = new CellRangeAddressList(0, 65535, 5, 5); CellRangeAddressList region6 = new CellRangeAddressList(0, 65535, 6, 6); CellRangeAddressList region7 = new CellRangeAddressList(0, 65535, 7, 7); CellRangeAddressList region8 = new CellRangeAddressList(0, 65535, 8, 8); CellRangeAddressList region16 = new CellRangeAddressList(0, 65535, 16, 16); //-----------新增“资源大类”20170213 CellRangeAddressList region17 = new CellRangeAddressList(0, 65535, 17, 17); IName range3 = wb.CreateName(); int num3 = ((List <string>)metadataLists[14]).Count + 1; range3.RefersToFormula = "基础数据!$O$2:$O$" + num3.ToString(); range3.NameName = "dicRange3"; DVConstraint constraint3 = DVConstraint.CreateFormulaListConstraint("dicRange3"); IName range5 = wb.CreateName(); if (catologList.Count == 0) { MessageBox.Show("请导入教材目录!"); return; } else { int num5 = catologList.Count; range5.RefersToFormula = "教材目录!$A$1:$A$" + num5; range5.NameName = "dicRange5"; DVConstraint constraint5 = DVConstraint.CreateFormulaListConstraint("dicRange5"); DVConstraint constraint6 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[4]).ToArray()); DVConstraint constraint7; DVConstraint constraint8; String[] test = bookName.Substring(bookName.LastIndexOf("_")).Split('-'); if (test[1] == "1") { IName range8 = wb.CreateName(); int num8 = ((string[])((List <List <String> >)metadataLists[23])[0].ToArray()).Length + 1; range8.RefersToFormula = "基础数据!$X$2:$X$" + num8.ToString(); range8.NameName = "dicRange8"; constraint7 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[9]).ToArray()); constraint8 = DVConstraint.CreateFormulaListConstraint("dicRange8"); } else if (test[1] == "2") { IName range8 = wb.CreateName(); int num8 = ((string[])((List <List <String> >)metadataLists[23])[1].ToArray()).Length + 1; range8.RefersToFormula = "基础数据!$Y$2:$Y$" + num8.ToString(); range8.NameName = "dicRange8"; constraint7 = DVConstraint.CreateExplicitListConstraint(((List <string>)metadataLists[8]).ToArray()); constraint8 = DVConstraint.CreateFormulaListConstraint("dicRange8"); } else { IName range8 = wb.CreateName(); //int num8 = ((string[])((List<List<String>>)metadataLists[22])[2].ToArray()).Length + 1; int num8 = ((string[])((List <List <String> >)metadataLists[23])[2].ToArray()).Length + 1; range8.RefersToFormula = "基础数据!$Z$2:$Z$" + num8.ToString(); range8.NameName = "dicRange8"; constraint7 = DVConstraint.CreateExplicitListConstraint((string[])((List <string>)metadataLists[7]).ToArray()); constraint8 = DVConstraint.CreateFormulaListConstraint("dicRange8"); } //DVConstraint constraint16 = DVConstraint.CreateExplicitListConstraint((string[])((List<string>)metadataLists[19]).ToArray()); DVConstraint constraint16 = DVConstraint.CreateExplicitListConstraint(new string[] { "A", "B", "C", "D" }); DVConstraint constraint17 = DVConstraint.CreateExplicitListConstraint(new string[] { "同步资源", "拓展资源" }); HSSFDataValidation dataValidate3 = new HSSFDataValidation(region3, constraint3); HSSFDataValidation dataValidate5 = new HSSFDataValidation(region5, constraint5); HSSFDataValidation dataValidate6 = new HSSFDataValidation(region6, constraint6); HSSFDataValidation dataValidate7 = new HSSFDataValidation(region7, constraint7); HSSFDataValidation dataValidate8 = new HSSFDataValidation(region8, constraint8); HSSFDataValidation dataValidate16 = new HSSFDataValidation(region16, constraint16); //----------新增“资源大类”20170213 HSSFDataValidation dataValidate17 = new HSSFDataValidation(region17, constraint17); sheet.AddValidationData(dataValidate3); sheet.AddValidationData(dataValidate5); sheet.AddValidationData(dataValidate6); sheet.AddValidationData(dataValidate7); sheet.AddValidationData(dataValidate8); sheet.AddValidationData(dataValidate16); sheet.AddValidationData(dataValidate17); #endregion //表格样式 #region sh2.SetColumnWidth(0, 10 * 256); sh2.SetColumnWidth(1, 10 * 256); sh2.SetColumnWidth(2, 20 * 256); sh2.SetColumnWidth(3, 20 * 256); sh2.SetColumnWidth(4, 10 * 256); sh2.SetColumnWidth(5, 15 * 256); sheet.GetRow(0).Height = 30 * 20; sheet.SetColumnWidth(0, 10 * 256); sheet.SetColumnWidth(1, 20 * 256); sheet.SetColumnWidth(2, 40 * 256); sheet.SetColumnWidth(3, 20 * 256); sheet.SetColumnWidth(4, 40 * 256); sheet.SetColumnWidth(5, 40 * 256); sheet.SetColumnWidth(6, 10 * 256); sheet.SetColumnWidth(7, 15 * 256); sheet.SetColumnWidth(8, 40 * 256); sheet.SetColumnWidth(9, 40 * 256); sheet.SetColumnWidth(10, 20 * 256); sheet.SetColumnWidth(11, 20 * 256); sheet.SetColumnWidth(12, 10 * 256); sheet.SetColumnWidth(13, 10 * 256); sheet.SetColumnWidth(14, 40 * 256); sheet.SetColumnWidth(15, 40 * 256); sheet.SetColumnWidth(16, 10 * 256); sheet.SetColumnWidth(17, 10 * 256); #endregion String fileName = bookName.Substring(0, bookName.IndexOf("_")) + "_资源集模板.xls"; using (FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite)) { wb.Write(fs); fs.Close(); MessageBox.Show("创建成功!"); } } }
/// <summary> /// Excel 导出 /// </summary> /// <param name="dataTable">数据源</param> /// <param name="fileName">保存路径</param> /// <param name="sheetName">sheet 名称</param> /// <param name="titleName">标题</param> public static void ExportExcel(DataTable dataTable, string fileName, string sheetName, string titleName) { //创建 Excel 文件 HSSFWorkbook hssfWorkbook = new HSSFWorkbook(); //创建 Excel Sheet HSSFSheet hssfSheet = hssfWorkbook.CreateSheet(sheetName); //调色板实例 Color LevelThreeColor = Color.FromArgb(197, 217, 241); hssfSheet.DefaultColumnWidth = 20; hssfSheet.DefaultRowHeight = 10; //下拉列表 CellRangeAddressList regions = new CellRangeAddressList(0, 65535, 12, 12); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "未启动", "整改中", "已完成" }); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); hssfSheet.AddValidationData(dataValidate); #region 合并单元格 //合并单元格 , 开始行 , 结束行 ,开始列 ,结束列 List <CellRangeAddress> cellRange = new List <CellRangeAddress>(); List <CellRangeAddress> cellRange2 = new List <CellRangeAddress>(); cellRange.Add(new CellRangeAddress(0, 0, 0, 13)); cellRange.Add(new CellRangeAddress(1, 1, 0, 8)); cellRange.Add(new CellRangeAddress(1, 1, 9, 13)); cellRange2.Add(new CellRangeAddress(2, 3, 0, 0)); cellRange2.Add(new CellRangeAddress(2, 3, 1, 1)); cellRange2.Add(new CellRangeAddress(2, 3, 2, 2)); cellRange2.Add(new CellRangeAddress(2, 3, 3, 3)); cellRange2.Add(new CellRangeAddress(2, 3, 4, 4)); cellRange2.Add(new CellRangeAddress(2, 3, 5, 5)); cellRange2.Add(new CellRangeAddress(2, 3, 6, 6)); cellRange2.Add(new CellRangeAddress(2, 3, 7, 7)); cellRange2.Add(new CellRangeAddress(2, 2, 8, 10)); cellRange2.Add(new CellRangeAddress(2, 3, 11, 11)); cellRange2.Add(new CellRangeAddress(2, 3, 12, 12)); cellRange2.Add(new CellRangeAddress(2, 3, 13, 13)); cellRange.Add(new CellRangeAddress(2, 3, 0, 0)); cellRange.Add(new CellRangeAddress(2, 3, 1, 1)); cellRange.Add(new CellRangeAddress(2, 3, 2, 2)); cellRange.Add(new CellRangeAddress(2, 3, 3, 3)); cellRange.Add(new CellRangeAddress(2, 3, 4, 4)); cellRange.Add(new CellRangeAddress(2, 3, 5, 5)); cellRange.Add(new CellRangeAddress(2, 3, 6, 6)); cellRange.Add(new CellRangeAddress(2, 3, 7, 7)); cellRange.Add(new CellRangeAddress(2, 2, 8, 10)); cellRange.Add(new CellRangeAddress(2, 3, 11, 11)); cellRange.Add(new CellRangeAddress(2, 3, 12, 12)); cellRange.Add(new CellRangeAddress(2, 3, 13, 13)); foreach (CellRangeAddress cell in cellRange) { hssfSheet.AddMergedRegion(cell); } #endregion #region 南京分行信息科技工作检查问题整改跟踪信息表 //创建标题列头 xx分行信息科技工作检查问题整改跟踪信息表 HSSFRow head_1_HSSFRow = hssfSheet.CreateRow(0); head_1_HSSFRow.Height = 200 * 5; head_1_HSSFRow.CreateCell(0).SetCellValue(titleName); //创建样式 Style Header HSSFCellStyle hssfCellStyle = hssfWorkbook.CreateCellStyle(); //创建字体 Font Header HSSFFont hssfFontHead = (HSSFFont)hssfWorkbook.CreateFont(); //字体 hssfFontHead.FontName = "宋体"; hssfFontHead.FontHeightInPoints = 16; hssfFontHead.Color = HSSFColor.BLACK.index; hssfCellStyle.SetFont(hssfFontHead); head_1_HSSFRow.GetCell(0).CellStyle = hssfCellStyle; #endregion #region 检查基本信息 、整改落实情况跟踪 //创建标题列头 检查基本信息、 整改落实情况跟踪 HSSFRow head_2_HSSFRow = hssfSheet.CreateRow(1); head_2_HSSFRow.HeightInPoints = 20; head_2_HSSFRow.CreateCell(0).SetCellValue("检查基本信息"); head_2_HSSFRow.CreateCell(9).SetCellValue("整改落实情况跟踪"); //样式 HSSFCellStyle hssf_2_CellStyle = hssfWorkbook.CreateCellStyle(); HSSFFont hssf_2_FontHead = (HSSFFont)hssfWorkbook.CreateFont(); //字体 hssfFontHead.FontName = "宋体"; hssfFontHead.FontHeightInPoints = 9; hssfFontHead.Color = HSSFColor.BLACK.index; hssf_2_CellStyle.SetFont(hssf_2_FontHead); head_2_HSSFRow.GetCell(0).CellStyle = hssf_2_CellStyle; head_2_HSSFRow.GetCell(9).CellStyle = hssf_2_CellStyle; #endregion #region 表列头 、 列名 //样式 HSSFCellStyle hssf_3_CellStyle = hssfWorkbook.CreateCellStyle(); HSSFFont hssf_3_FontHead = (HSSFFont)hssfWorkbook.CreateFont(); hssf_3_CellStyle.FillPattern = CellFillPattern.SOLID_FOREGROUND; hssf_3_CellStyle.FillBackgroundColor = GetXLColour(hssfWorkbook, LevelThreeColor); hssf_3_CellStyle.BorderBottom = CellBorderType.THIN; hssf_3_CellStyle.BorderLeft = CellBorderType.THIN; hssf_3_CellStyle.BorderRight = CellBorderType.THIN; hssf_3_CellStyle.BorderTop = CellBorderType.THIN; //字体 hssf_3_FontHead.FontName = "宋体"; hssf_3_FontHead.FontHeightInPoints = 9; hssf_3_FontHead.Color = HSSFColor.BLACK.index; hssf_3_CellStyle.SetFont(hssf_3_FontHead); HSSFRow content_1_HSSFRow = hssfSheet.CreateRow(2); content_1_HSSFRow.CreateCell(0).SetCellValue("序号"); content_1_HSSFRow.CreateCell(1).SetCellValue("分行名称"); content_1_HSSFRow.CreateCell(2).SetCellValue("检查开始时间"); content_1_HSSFRow.CreateCell(3).SetCellValue("检查结束时间"); content_1_HSSFRow.CreateCell(4).SetCellValue("检查项分类"); content_1_HSSFRow.CreateCell(5).SetCellValue("问题性质"); content_1_HSSFRow.CreateCell(6).SetCellValue("问题描述"); content_1_HSSFRow.CreateCell(7).SetCellValue("检查意见"); content_1_HSSFRow.CreateCell(8).SetCellValue("整改措施计划"); content_1_HSSFRow.CreateCell(11).SetCellValue("推进情况"); content_1_HSSFRow.CreateCell(12).SetCellValue("整改状态"); content_1_HSSFRow.CreateCell(13).SetCellValue("最终完成时间"); HSSFRow content_2_HSSFRow = hssfSheet.CreateRow(3); content_2_HSSFRow.CreateCell(8).SetCellValue("整改措施"); content_2_HSSFRow.CreateCell(9).SetCellValue("整改完成标志"); content_2_HSSFRow.CreateCell(10).SetCellValue("计划完成时间"); content_2_HSSFRow.GetCell(8).CellStyle = hssf_3_CellStyle; content_2_HSSFRow.GetCell(9).CellStyle = hssf_3_CellStyle; content_2_HSSFRow.GetCell(10).CellStyle = hssf_3_CellStyle; #endregion #region 边框、背景颜色设置 //创建样式 Style Bord HSSFCellStyle hssfCellStyleBord = hssfWorkbook.CreateCellStyle(); //创建字体 Font Bord hssfCellStyleBord.Alignment = CellHorizontalAlignment.CENTER; hssfCellStyleBord.VerticalAlignment = CellVerticalAlignment.CENTER; hssfCellStyleBord.BorderBottom = CellBorderType.THIN; hssfCellStyleBord.BorderLeft = CellBorderType.THIN; hssfCellStyleBord.BorderRight = CellBorderType.THIN; hssfCellStyleBord.BorderTop = CellBorderType.THIN; HSSFFont hssfFontBord = (HSSFFont)hssfWorkbook.CreateFont(); hssfFontBord.FontName = "宋体"; hssfFontBord.FontHeightInPoints = 12; hssfFontBord.Color = HSSFColor.BLACK.index; hssfCellStyleBord.SetFont(hssfFontBord); //边框 foreach (var cell in cellRange) { for (int i = cell.FirstRow; i <= cell.LastRow; i++) { HSSFRow row = HSSFCellUtil.GetRow(i, hssfSheet); for (int j = cell.FirstColumn; j <= cell.LastColumn; j++) { HSSFCell singleCell = HSSFCellUtil.GetCell(row, (short)j); singleCell.CellStyle = hssfCellStyleBord; } } } //创建样式 Style Bord HSSFCellStyle hssfCellStyleBord_2 = hssfWorkbook.CreateCellStyle(); hssfCellStyleBord_2.Alignment = CellHorizontalAlignment.CENTER; hssfCellStyleBord_2.VerticalAlignment = CellVerticalAlignment.CENTER; hssfCellStyleBord_2.BorderBottom = CellBorderType.THIN; hssfCellStyleBord_2.BorderLeft = CellBorderType.THIN; hssfCellStyleBord_2.BorderRight = CellBorderType.THIN; hssfCellStyleBord_2.BorderTop = CellBorderType.THIN; hssfCellStyleBord_2.FillPattern = CellFillPattern.SOLID_FOREGROUND; hssfCellStyleBord_2.FillBackgroundColor = GetXLColour(hssfWorkbook, LevelThreeColor); hssfCellStyleBord_2.SetFont(hssfFontBord); foreach (var cell in cellRange2) { for (int i = cell.FirstRow; i <= cell.LastRow; i++) { HSSFRow row = HSSFCellUtil.GetRow(i, hssfSheet); for (int j = cell.FirstColumn; j <= cell.LastColumn; j++) { HSSFCell singleCell = HSSFCellUtil.GetCell(row, (short)j); singleCell.CellStyle = hssfCellStyleBord_2; } } } #endregion #region 数据内容 try { #region 内容样式 HSSFCellStyle hssf_i_CellStyle = hssfWorkbook.CreateCellStyle(); HSSFFont hssf_i_FontHead = (HSSFFont)hssfWorkbook.CreateFont(); hssf_i_CellStyle.Alignment = CellHorizontalAlignment.CENTER; hssf_i_CellStyle.VerticalAlignment = CellVerticalAlignment.CENTER; hssf_i_CellStyle.BorderBottom = CellBorderType.THIN; hssf_i_CellStyle.BorderLeft = CellBorderType.THIN; hssf_i_CellStyle.BorderRight = CellBorderType.THIN; hssf_i_CellStyle.BorderTop = CellBorderType.THIN; //字体 hssf_i_FontHead.FontName = "宋体"; hssf_i_FontHead.FontHeightInPoints = 12; hssf_i_FontHead.Color = HSSFColor.BLACK.index; hssf_i_CellStyle.SetFont(hssf_i_FontHead); hssf_i_CellStyle.IsLocked = false; #endregion if (dataTable.Rows.Count > 0) { for (int i = 0; i < dataTable.Rows.Count; i++) { HSSFRow dataHSSFRow = hssfSheet.CreateRow(i + 4); dataHSSFRow.HeightInPoints = 20; dataHSSFRow.CreateCell(0).SetCellValue(dataTable.Rows[i][0].ToString()); dataHSSFRow.CreateCell(1).SetCellValue(dataTable.Rows[i][1].ToString()); dataHSSFRow.CreateCell(2).SetCellValue(dataTable.Rows[i][2].ToString()); dataHSSFRow.CreateCell(3).SetCellValue(dataTable.Rows[i][3].ToString()); dataHSSFRow.CreateCell(4).SetCellValue(dataTable.Rows[i][4].ToString()); dataHSSFRow.CreateCell(5).SetCellValue(dataTable.Rows[i][5].ToString()); dataHSSFRow.CreateCell(6).SetCellValue(dataTable.Rows[i][6].ToString()); dataHSSFRow.CreateCell(7).SetCellValue(dataTable.Rows[i][7].ToString()); dataHSSFRow.CreateCell(8).SetCellValue(string.Empty); dataHSSFRow.CreateCell(9).SetCellValue(string.Empty); dataHSSFRow.CreateCell(10).SetCellValue(string.Empty); dataHSSFRow.CreateCell(11).SetCellValue(string.Empty); dataHSSFRow.CreateCell(12).SetCellValue(string.Empty); dataHSSFRow.CreateCell(13).SetCellValue(string.Empty); for (int j = 0; j < 14; j++) { dataHSSFRow.GetCell(j).CellStyle = hssf_i_CellStyle; } } } hssfSheet.ProtectSheet("21"); } catch (Exception ex) { throw new Exception("导出 Excel 错误 , " + ex.Message); } #endregion //保存文件 using (FileStream fileStream = new FileStream(fileName, FileMode.Create)) { hssfWorkbook.Write(fileStream); } }
/// <summary> /// 填充数据 /// </summary> /// <param name="table"></param> /// <param name="subject"></param> /// <param name="sheetName"></param> /// <param name="columnName"></param> /// <param name="columnTitle"></param> /// <returns></returns> public static HSSFWorkbook GenerateData(DataTable table, string subject, string sheetName, string[] columnName, string[] columnTitle , string[] roles = null, string[] photoStatus = null, string[] hospitals = null, string[] creators = null) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet(sheetName); if (subject == "客资导入模板") { CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 4, 4); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(roles); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); //CellRangeAddressList regions1 = new CellRangeAddressList(1, 65535, 5, 5); //DVConstraint constraint1 = DVConstraint.CreateExplicitListConstraint(hospitals); //HSSFDataValidation dataValidate1 = new HSSFDataValidation(regions1, constraint1); //sheet.AddValidationData(dataValidate1); CreateDropDownListForExcel(sheet, hospitals, 1, 500, 6); CellRangeAddressList regions4 = new CellRangeAddressList(1, 65535, 12, 12); DVConstraint constraint4 = DVConstraint.CreateExplicitListConstraint(photoStatus); HSSFDataValidation dataValidate4 = new HSSFDataValidation(regions4, constraint4); sheet.AddValidationData(dataValidate4); CellRangeAddressList regions2 = new CellRangeAddressList(1, 65535, 13, 13); DVConstraint constraint2 = DVConstraint.CreateExplicitListConstraint(new string[] { "已领取", "未领取" }); HSSFDataValidation dataValidate2 = new HSSFDataValidation(regions2, constraint2); sheet.AddValidationData(dataValidate2); CellRangeAddressList regions3 = new CellRangeAddressList(1, 65535, 14, 14); DVConstraint constraint3 = DVConstraint.CreateExplicitListConstraint(new string[] { "正常", "黑名单" }); HSSFDataValidation dataValidate3 = new HSSFDataValidation(regions3, constraint3); sheet.AddValidationData(dataValidate3); //CellRangeAddressList regions5 = new CellRangeAddressList(1, 65535, 8, 8); //DVConstraint constraint5 = DVConstraint.CreateExplicitListConstraint(creators); //HSSFDataValidation dataValidate5 = new HSSFDataValidation(regions5, constraint5); //sheet.AddValidationData(dataValidate5); CreateDropDownListForExcel(sheet, creators, 1, 500, 9); CellRangeAddressList regions5 = new CellRangeAddressList(1, 65535, 15, 15); DVConstraint constraint5 = DVConstraint.CreateExplicitListConstraint(new string[] { "是", "否" }); HSSFDataValidation dataValidate5 = new HSSFDataValidation(regions5, constraint5); sheet.AddValidationData(dataValidate5); CellRangeAddressList regions6 = new CellRangeAddressList(1, 65535, 2, 2); DVConstraint constraint6 = DVConstraint.CreateExplicitListConstraint(new string[] { "备孕中", "孕妈妈", "宝妈妈" }); HSSFDataValidation dataValidate6 = new HSSFDataValidation(regions6, constraint6); sheet.AddValidationData(dataValidate6); } ICellStyle dateStyle = hssfworkbook.CreateCellStyle(); IDataFormat format = hssfworkbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss"); #region 取得列宽 int[] colWidth = new int[columnName.Length]; for (int i = 0; i < columnName.Length; i++) { colWidth[i] = Encoding.GetEncoding(936).GetBytes(columnTitle[i]).Length; } if (table != null && table.Rows.Count > 0) { for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < columnName.Length; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(table.Rows[i][columnName[j]].ToString()).Length; if (intTemp > colWidth[j]) { colWidth[j] = intTemp; } } } } #endregion int rowIndex = 0; if (table == null || table.Rows.Count == 0) { IRow headerRow; headerRow = sheet.CreateRow(0); ICellStyle headStyle = hssfworkbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); for (int i = 0; i < columnName.Length; i++) { headerRow.CreateCell(i).SetCellValue(columnTitle[i]); headerRow.GetCell(i).CellStyle = headStyle; //设置列宽 sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256 + 2 * 256); } } else { foreach (DataRow row in table.Rows) { #region 新建表,填充表头,填充列头,样式 if (rowIndex == 65535 || rowIndex == 0) { if (rowIndex != 0) { sheet = hssfworkbook.CreateSheet(sheetName + ((int)rowIndex / 65535).ToString()); } #region 列头及样式 { IRow headerRow; headerRow = sheet.CreateRow(0); ICellStyle headStyle = hssfworkbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = hssfworkbook.CreateFont(); font.FontHeightInPoints = 12; font.Boldweight = 700; headStyle.SetFont(font); for (int i = 0; i < columnName.Length; i++) { headerRow.CreateCell(i).SetCellValue(columnTitle[i]); headerRow.GetCell(i).CellStyle = headStyle; //设置列宽 if (columnName[i].Contains("ItemPictureURL")) { sheet.SetColumnWidth(i, 60 * 256); } else { sheet.SetColumnWidth(i, (colWidth[i] + 1) * 256 + 2 * 256); } } } #endregion rowIndex = 1; } #endregion #region 填充数据 IRow dataRow = sheet.CreateRow(rowIndex); for (int i = 0; i < columnName.Length; i++) { ICell newCell = dataRow.CreateCell(i); string drValue = row[columnName[i]].ToString(); #region 赋值 switch (table.Columns[columnName[i]].DataType.ToString()) { case "System.String": //字符串类型 if (drValue.ToUpper() == "TRUE") { newCell.SetCellValue("是"); } else if (drValue.ToUpper() == "FALSE") { newCell.SetCellValue("否"); } newCell.SetCellValue(drValue); break; case "System.DateTime": //日期类型 if (string.IsNullOrWhiteSpace(drValue)) { newCell.SetCellValue(""); break; } DateTime dateV; bool flag = DateTime.TryParse(drValue, out dateV); if (flag) { newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化显示 } break; case "System.Boolean": //布尔型 bool boolV = false; bool.TryParse(drValue, out boolV); if (boolV) { newCell.SetCellValue("是"); } else { newCell.SetCellValue("否"); } break; case "System.Int16": //整型 case "System.Int32": if (string.IsNullOrWhiteSpace(drValue)) { newCell.SetCellValue(""); break; } int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case "System.Int64": newCell.SetCellValue(drValue); break; case "System.Byte": newCell.SetCellValue(drValue); break; case "System.Decimal": //浮点型 case "System.Double": case "System.Single": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case "System.DBNull": //空值处理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(drValue); break; } #endregion } #endregion rowIndex++; } } return(hssfworkbook); }
//数据有效性以及下拉框的设置 public static HSSFDataValidation CreateDataValidation(int index, Tk5FieldInfoEx fieldInfo, ICellStyle styleContent, HSSFWorkbook workbook) { IDataFormat format = workbook.CreateDataFormat(); CellRangeAddressList region = new CellRangeAddressList(1, 65535, index, index); DVConstraint constraint = null; HSSFDataValidation dataValidation = null; if (fieldInfo.Decoder != null && fieldInfo.Decoder.Type == DecoderType.CodeTable) { IEnumerable <IDecoderItem> data = GetDecoderItem(fieldInfo); if (data != null) { List <string> optionList = new List <string>(); foreach (IDecoderItem item in data) { if (item != null) { TkDebug.AssertArgumentNullOrEmpty(item.Name, "item.Name", null); optionList.Add(item.Name); } } constraint = DVConstraint.CreateExplicitListConstraint(optionList.ToArray()); dataValidation = DisplayMsg(region, constraint, "请从下拉框选项中选择"); } } else { if (fieldInfo.InternalControl != null && fieldInfo.InternalControl.SrcControl == ControlType.CheckBox) { constraint = DVConstraint.CreateExplicitListConstraint(new string[] { "√" }); dataValidation = DisplayMsg(region, constraint, "请在下拉框选项中进行选择"); } else { switch (fieldInfo.DataType) { case TkDataType.DateTime: case TkDataType.Date: if (fieldInfo.DataType == TkDataType.DateTime) { styleContent.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm"); } else { styleContent.DataFormat = format.GetFormat("yyyy-MM-dd"); } constraint = DVConstraint.CreateDateConstraint(6, "1900-01-01", null, "yyyy-MM-dd"); dataValidation = DisplayMsg(region, constraint, "请输入一个日期类型的值"); break; case TkDataType.Double: case TkDataType.Decimal: case TkDataType.Money: constraint = DVConstraint.CreateNumericConstraint(2, 1, "1", "0"); dataValidation = DisplayMsg(region, constraint, "请输入数值类型的值"); styleContent.DataFormat = format.GetFormat("0"); break; case TkDataType.Long: case TkDataType.Int: case TkDataType.Short: case TkDataType.Byte: case TkDataType.Bit: constraint = DVConstraint.CreateNumericConstraint(1, 1, "1", "0"); dataValidation = DisplayMsg(region, constraint, "请输入一个整数"); styleContent.DataFormat = format.GetFormat("0"); break; default: styleContent.DataFormat = format.GetFormat("@"); break; } } } return(dataValidation); }
/// <summary> /// 实体类集合导出到EXCLE2003 /// </summary> /// <param name="cellHeard">单元头的Key和Value:{ { "UserName", "姓名" }, { "Age", "年龄" } };</param> /// <param name="enList">数据源</param> /// <param name="sheetName">工作表名称</param> /// <returns>文件的下载地址</returns> public static string EntityListToExcel(Dictionary <string, string> cellHeard, IList enList, string sheetName, string[] items = null) { try { string fileName = sheetName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称 string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址,供给前台下载 string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径 // 1.检测是否存在文件夹,若不存在就建立个文件夹 string directoryName = Path.GetDirectoryName(filePath); if (!Directory.Exists(directoryName)) { Directory.CreateDirectory(directoryName); } // 2.解析单元格头部,设置单元头的中文名称 HSSFWorkbook workbook = new HSSFWorkbook(); // 工作簿 ISheet sheet = workbook.CreateSheet(sheetName); // 工作表 IRow row = sheet.CreateRow(0); List <string> keys = cellHeard.Keys.ToList(); for (int i = 0; i < keys.Count; i++) { row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值 } //有下拉选项,增加下拉表头 if (items != null) { var regions = new CellRangeAddressList(1, 65535, 0, 0); var constraint = DVConstraint.CreateExplicitListConstraint(items); //这是下拉选项值 var dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); } // 3.List对象的值赋值到Excel的单元格里 int rowIndex = 1; // 从第二行开始赋值(第一行已设置为单元头) foreach (var en in enList) { IRow rowTmp = sheet.CreateRow(rowIndex); for (int i = 0; i < keys.Count; i++) // 根据指定的属性名称,获取对象指定属性的值 { string cellValue = ""; // 单元格的值 object properotyValue = null; // 属性的值 PropertyInfo properotyInfo = null; // 属性的信息 // 3.1 若属性头的名称包含'.',就表示是子类里的属性,那么就要遍历子类,eg:UserEn.UserName if (keys[i].IndexOf(".") >= 0) { // 3.1.1 解析子类属性(这里只解析1层子类,多层子类未处理) string[] properotyArray = keys[i].Split(new string[] { "." }, StringSplitOptions.RemoveEmptyEntries); string subClassName = properotyArray[0]; // '.'前面的为子类的名称 string subClassProperotyName = properotyArray[1]; // '.'后面的为子类的属性名称 PropertyInfo subClassInfo = en.GetType().GetProperty(subClassName); // 获取子类的类型 if (subClassInfo != null) { // 3.1.2 获取子类的实例 var subClassEn = en.GetType().GetProperty(subClassName).GetValue(en, null); // 3.1.3 根据属性名称获取子类里的属性类型 properotyInfo = subClassInfo.PropertyType.GetProperty(subClassProperotyName); if (properotyInfo != null) { properotyValue = properotyInfo.GetValue(subClassEn, null); // 获取子类属性的值 } } } else { // 3.2 若不是子类的属性,直接根据属性名称获取对象对应的属性 properotyInfo = en.GetType().GetProperty(keys[i]); if (properotyInfo != null) { properotyValue = properotyInfo.GetValue(en, null); } } // 3.3 属性值经过转换赋值给单元格值 if (properotyValue != null) { cellValue = properotyValue.ToString(); // 3.3.1 对时间初始值赋值为空 if (cellValue.Trim() == "0001/1/1 0:00:00" || cellValue.Trim() == "0001/1/1 23:59:59") { cellValue = ""; } } // 3.4 填充到Excel的单元格里 rowTmp.CreateCell(i).SetCellValue(cellValue); } rowIndex++; } // 4.生成文件 FileStream file = new FileStream(filePath, FileMode.Create); workbook.Write(file); file.Close(); // 5.返回下载路径 return(urlPath); } catch (Exception ex) { throw ex; } }
/// <summary> /// 下载带下拉选项的模板 /// </summary> /// <param name="cellHeard"></param> /// <param name="items">下拉选项</param> /// <param name="sheetName">sheet名称</param> /// <param name="excelName">excel名称</param> /// <param name="description">描述</param> /// <returns></returns> public static string CreateDropdownExcel(Dictionary <string, string> cellHeard, string[] items, string sheetName, string excelName, string description) { HSSFWorkbook wk = new HSSFWorkbook(); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = "启明合创"; wk.DocumentSummaryInformation = dsi; //创建一个名称为mySheet的表 ISheet tb = wk.CreateSheet(sheetName); ICellStyle cellStyle = wk.CreateCellStyle(); //导入说明sheet try { IDataFormat textFormat = wk.CreateDataFormat(); cellStyle.DataFormat = textFormat.GetFormat("text"); IRow row = tb.CreateRow(0); List <string> keys = cellHeard.Keys.ToList(); for (int i = 0; i < keys.Count; i++) { row.CreateCell(i).SetCellValue(cellHeard[keys[i]]); // 列名为Key的值 //第一列选择框除外,其余都设为文本 todo 根据header的类型来设置 if (i > 0) { tb.SetDefaultColumnStyle(i, cellStyle); } } CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 0, 0); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(items);//这是下拉选项值 HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); tb.AddValidationData(dataValidate); //设置 } catch (Exception e) { throw e; } string fileName = excelName + "-" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls"; // 文件名称 string urlPath = "UpFiles/ExcelFiles/" + fileName; // 文件下载的URL地址,供给前台下载 string filePath = HttpContext.Current.Server.MapPath("\\" + urlPath); // 文件路径 // 1.检测是否存在文件夹,若不存在就建立个文件夹 string directoryName = Path.GetDirectoryName(filePath); if (!Directory.Exists(directoryName)) { Directory.CreateDirectory(directoryName); } FileStream file = new FileStream(filePath, FileMode.Create); wk.Write(file); file.Close(); return(filePath); }
/// <summary> /// 设置每列数据 /// </summary> /// <param name="option"></param> /// <param name="cell0"></param> /// <param name="row0"></param> /// <param name="sheet1"></param> /// <param name="patr"></param> /// <param name="RowNuber"></param> private void SetColumn(List <excle_option> option, ref ICell cell0, ref IRow row0, ref ISheet sheet1, ref HSSFPatriarch patr, ref int RowNuber) { int ColumnNum = 0; if (option != null) { for (int i = 0; i < option.Count; i++) { if (option[i].index != null) { ColumnNum = int.Parse(option[i].index.ToString()); } else { ColumnNum = i; } cell0 = row0.CreateCell(ColumnNum); //设置批注和 if (option[i].Description == null || option[i].Description == "") { switch (option[i].TypeOfValue) { case 0: cell0.SetCellValue(option[i].value); break; case 1: bool result = false; if (bool.TryParse(option[i].value, out result)) { cell0.SetCellValue(result); } else { cell0.SetCellValue(option[i].value); } break; case 2: DateTime DateResult = new DateTime(); if (DateTime.TryParse(option[i].value, out DateResult)) { cell0.SetCellValue(DateResult); } else { cell0.SetCellValue(option[i].value); } break; case 3: double DoubleResult = new double(); if (double.TryParse(option[i].value, out DoubleResult)) { cell0.SetCellValue(DoubleResult); } else { cell0.SetCellValue(option[i].value); } break; case 4: cell0.SetCellFormula(option[i].value); break; default: cell0.SetCellValue(option[i].value); break; } } else { cell0.SetCellValue(new HSSFRichTextString(option[i].value)); cell0.CellComment = (addPiZhu(patr, option[i].Description, "")); } //赋值单元格样式 if (option[i].ICellStyle != null) { cell0.CellStyle = option[i].ICellStyle; } if (option[i].option != null) { CellRangeAddressList rangeList = new CellRangeAddressList(); rangeList.AddCellRangeAddress(new CellRangeAddress(1, 100, ColumnNum, ColumnNum)); DVConstraint dvconstraint = DVConstraint.CreateExplicitListConstraint(option[i].option); HSSFDataValidation dataValidation = new HSSFDataValidation(rangeList, dvconstraint); //add the data validation to sheet1 ((HSSFSheet)sheet1).AddValidationData(dataValidation); } //ColumnNum++; } } RowNuber++; }
/// <summary> /// 导出模板 /// </summary> /// <param name="SourceTable">数据源</param> /// <param name="propertiesList">属性集合</param> /// <param name="botName">bot名称</param> /// <returns>返回文件二进制</returns> public byte[] DownExcelTemplate(DataTable SourceTable, List <BOTPropertyDefinition> propertiesList, string botName) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet = hssfworkbook.CreateSheet("sheet1"); #region 标题头 int i = -1; int rowIndex = 0; //创建第1行 IRow headerRow = sheet.CreateRow(rowIndex); //根据读取数据库字段写标题名字 foreach (DataRow drC in SourceTable.Rows) { i++; string field = drC["field"] != null ? drC["field"].ToString() : ""; if (string.IsNullOrEmpty(field)) { continue; } ICell cell = headerRow.CreateCell(i); // cell.SetCellValue(field); //输入列设置宽度 int w = Convert.ToInt32(150 * 26.54); sheet.SetColumnWidth(i, w); if (field.ToUpper() == "BOT") { //创建下拉框序列 CellRangeAddressList regions = new CellRangeAddressList(0, 65535, i, i); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(new string[] { botName }); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); continue; } var tmpList = propertiesList.Where(p => p.Name.ToUpper() == field.ToUpper()).ToList(); if (tmpList.Any() && tmpList[0].Options != null && tmpList[0].Options.Count > 0) { //如果是日期类型不做下拉框 if (tmpList[0].Type == MetadataTagType.ISODate) { continue; } //获取对应属性的配置信息,默认仅查询到一个对象 var optionsStr = tmpList[0].Options.ToArray(); //判断所配置的选项数组是否为空,如果为空就不添加选项卡 if (optionsStr.Length == 0 || string.IsNullOrEmpty(optionsStr[0])) { continue; } //创建下拉框序列 CellRangeAddressList regions = new CellRangeAddressList(0, 65535, i, i); DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(optionsStr); HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(dataValidate); } } #endregion //写入内存流获取二进制数组 MemoryStream streamMemory = new MemoryStream(); hssfworkbook.Write(streamMemory); byte[] data = streamMemory.ToArray(); return(data); }
/// <summary> /// 生成导入模版Excel信息 /// </summary> /// <param name="templateModels">模板定义信息</param> /// <param name="title">Sheet名称</param> /// <returns></returns> public static HSSFWorkbook ExportTemplate(List <ExcelTemplate> templateModels, string title) { HSSFWorkbook workbook = new HSSFWorkbook(); ISheet sheet = workbook.CreateSheet(); workbook.SetSheetName(0, title); DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation(); dsi.Company = Company; workbook.DocumentSummaryInformation = dsi; SummaryInformation si = PropertySetFactory.CreateSummaryInformation(); si.Author = Author; si.ApplicationName = ApplicationName; si.Title = title; si.CreateDateTime = DateTime.Now; workbook.SummaryInformation = si; //取得列宽 int[] arrColWidth = new int[templateModels.Count]; int columnIndex = 0; foreach (var templateModel in templateModels) { arrColWidth[columnIndex] = templateModel.CellLength > 0 ? templateModel.CellLength * 2 : Encoding.UTF8.GetBytes(templateModel.Name.ToString()).Length; columnIndex++; } var headerRow = sheet.CreateRow(0); columnIndex = 0; foreach (var templateModel in templateModels) { var cell = headerRow.CreateCell(columnIndex); if (!string.IsNullOrEmpty(templateModel.ExportComments)) { HSSFPatriarch patr = (HSSFPatriarch)sheet.CreateDrawingPatriarch(); HSSFComment comment = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 16)); comment.String = new HSSFRichTextString(templateModel.ExportComments); comment.Author = ApplicationName; cell.CellComment = comment; } if (templateModel.DictionaryItems != null && templateModel.DictionaryItems.Count > 0) { DVConstraint constraint = DVConstraint.CreateExplicitListConstraint(templateModel.DictionaryItems.ToArray()); CellRangeAddressList regions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); IDataValidation validation = new HSSFDataValidation(regions, constraint); sheet.AddValidationData(validation); } cell.SetCellValue(templateModel.Name); if (templateModel.IsRequred) { var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.Color = HSSFColor.Red.Index; font.FontHeightInPoints = 10; font.IsBold = true; headStyle.SetFont(font); cell.CellStyle = headStyle; } else { var headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; var font = workbook.CreateFont(); font.Color = HSSFColor.Black.Index; font.FontHeightInPoints = 10; font.IsBold = true; headStyle.SetFont(font); cell.CellStyle = headStyle; } //设置列宽 sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256); columnIndex++; } return(workbook); }