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);
}
