/// <summary>
/// 将DataTable数据写入到Excel
/// </summary>
/// <param name="sheetName">工作薄名称</param>
/// <param name="table">数据</param>
/// <param name="rowTitle">标题数组</param>
/// <param name="hidColIndexs"></param>
private void DTExcel(string sheetName, DataTable table, List <ColumnValidItem> rowTitle, params int[] hidColIndexs)
{
var sheet1 = hssfworkbook.CreateSheet(sheetName);
sheet1.CreateFreezePane(0, 1, 0, 1);
foreach (int index in hidColIndexs)
{
sheet1.SetColumnHidden(index, true);
}
var sheet2 = hssfworkbook.CreateSheet("ShtDictionary");
//隐藏ShtDictionary工作薄
hssfworkbook.SetSheetHidden(1, true);
//创建标题行且赋值
this.CreateTitleRow(sheet1, rowTitle);
var cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.0");
if (table != null)
{
//填充数据项
for (int xcount = 0; xcount < table.Rows.Count; xcount++)
{
DataRow row = table.Rows[xcount];
var hsBodyRow = sheet1.CreateRow(xcount + 1);//数据行+1(第0行是标题)
for (int ycBody = 0; ycBody < rowTitle.Count; ycBody++)
{
if (rowTitle[ycBody] == null)
{
continue;
}
var value = string.Empty;
if (table.Columns.Contains(rowTitle[ycBody].DataModelName))
{
value = row[rowTitle[ycBody].DataModelName].ToString();
}
var cell = hsBodyRow.CreateCell(ycBody);
if (!string.IsNullOrEmpty(value))
{
cell.SetCellValue(value);
}
if (rowTitle[ycBody].ValidType == EnumValidType.Decimal)
{
cell.CellStyle = cellStyle;
}
}
}
}
}
private void setSheet2(HSSFWorkbook workBook, ISheet sheet)
{
//创建表
ISheet sheet2 = workBook.CreateSheet("岗位数据");
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
using (var db = DbFactory.Open())
{
var builder = db.From <Model.Post.Post>().Where(w => w.PostType == ZZTXEnums.行政村防汛防台工作组.ToString());
var rlist = db.Select <PostViewModel>(builder);
for (int iRowIndex = 0; iRowIndex < rlist.Count; iRowIndex++)
{
sheet2.CreateRow(iRowIndex).CreateCell(0).SetCellValue(rlist[iRowIndex].PostName);
}
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = "岗位数据!$A:$A";
range.NameName = "PostDataName";
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(3, 65535, 1, 1);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("PostDataName");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
public static void SetDropDownList(this HSSFSheet sheet, string[] datas, HSSFWorkbook workbook,
CellRangeAddressList addressList, string formulaName)
{
var hiddenSheetName = "HiddenDataSource" + DateTime.Now.ToString("yyyyMMddHHmmss");
ISheet CourseSheet = workbook.CreateSheet(hiddenSheetName);
workbook.SetSheetHidden(workbook.GetSheetIndex(hiddenSheetName), true);
//CourseSheet.CreateRow(0).CreateCell(0).SetCellValue("");
IRow row = null;
ICell cell = null;
for (int i = 0; i < datas.Length; i++)
{
row = CourseSheet.CreateRow(i);
cell = row.CreateCell(0);
cell.SetCellValue(datas[i]);
}
IName range = workbook.CreateName();
range.RefersToFormula = string.Format("{0}!$A$1:$A${1}", hiddenSheetName, datas.Length);
range.NameName = formulaName;
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(formulaName);
HSSFDataValidation dataValidate = new HSSFDataValidation(addressList, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// 设置下拉选项
/// </summary>
/// <param name="workBook"></param>
/// <param name="sheet"></param>
/// <param name="cellName"></param>
/// <param name="cellNo"></param>
/// <param name="list"></param>
public void setSheet2(HSSFWorkbook workBook, ISheet sheet, string cellName, int cellNo, List <string> list)
{
//创建表
ISheet sheet2 = workBook.CreateSheet(cellName);
//隐藏
workBook.SetSheetHidden(1, true);
//取数据
for (int i = 0; i < list.Count; i++)
{
sheet2.CreateRow(i).CreateCell(0).SetCellValue(list[i]);
}
//设计表名称
IName range = workBook.CreateName();
range.RefersToFormula = cellName + "!$A:$A";
range.NameName = cellName;
//定义下拉框范围
CellRangeAddressList regions = new CellRangeAddressList(2, 65535, cellNo, cellNo);
//设置数据引用
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint(cellName);
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet.AddValidationData(dataValidate);
}
/// <summary>
/// WriterExcel
/// </summary>
/// <param name="hssfworkbookDown"></param>
/// <param name="sheetIndex"></param>
/// <param name="DT"></param>
public static void WriterExcelP(HSSFWorkbook hssfworkbookDown, int sheetIndex, DataTable DT)
{
try
{
#region 设置单元格样式
//字体
HSSFFont fontS9 = FontS9(hssfworkbookDown);
//表格
ICellStyle tableS1 = TableS1(hssfworkbookDown);
tableS1.SetFont(fontS9);
ICellStyle tableS2 = TableS2(hssfworkbookDown);
tableS2.SetFont(fontS9);
ICellStyle tableS3 = TableS3(hssfworkbookDown);
tableS3.SetFont(fontS9);
ICellStyle tableS4 = TableS4(hssfworkbookDown);
tableS4.SetFont(fontS9);
#endregion
HSSFSheet sheet = (HSSFSheet)hssfworkbookDown.GetSheetAt(sheetIndex);
hssfworkbookDown.SetSheetHidden(sheetIndex, false);
hssfworkbookDown.SetActiveSheet(sheetIndex);
int n = 1;//因为模板有表头，所以从第2行开始写
for (int j = 0; j < DT.Rows.Count; j++)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(j + n);
dataRow.CreateCell(0);
dataRow.Cells[0].SetCellValue(j + 1);
for (int i = 1; i < project_excel.Length; i++)
{
dataRow.CreateCell(i);
dataRow.Cells[i].SetCellValue(DT.Rows[j][project_excel[i]].ToString());
dataRow.Cells[i].CellStyle = tableS2;
if (project_excel[i].ToString() == "content")
{
dataRow.Cells[i].CellStyle = tableS3;
}
}
dataRow.CreateCell(project_excel.Length);
dataRow.Cells[project_excel.Length].SetCellValue(DT.Rows[j]["progress"].ToString() == "100" ? "已完成" : "");
if (DT.Rows[j]["progress"].ToString() == "100")
{
for (int i = 0; i <= project_excel.Length; i++)
{
dataRow.Cells[i].CellStyle = tableS1;
if (i != project_excel.Length && project_excel[i].ToString() == "content")
{
dataRow.Cells[i].CellStyle = tableS4;
}
}
}
}
}
catch (Exception ex)
{
return;
}
}
//public static void test1()
//{
// 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[] { "itemA", "itemB", "itemC" });
// HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
// sheet1.AddValidationData(dataValidate);
// MemoryStream ms = new MemoryStream();
// hssfworkbook.Write(ms);
// string workbookFile = @"D:\\wulei22.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();
//}
public static void setdownlist()
{
//创建工作簿
HSSFWorkbook ssfworkbook = new HSSFWorkbook();
//创建工作表(页)
HSSFSheet sheet1 = ssfworkbook.CreateSheet("Sheet1") as HSSFSheet;
//创建一行
HSSFRow headerRow = (HSSFRow)sheet1.CreateRow(0);
//设置表头
headerRow.CreateCell(0).SetCellValue("ID");
//设置表头的宽度
sheet1.SetColumnWidth(0, 15 * 256);
#region 添加显示下拉列表
HSSFSheet sheet2 = ssfworkbook.CreateSheet("ShtDictionary") as HSSFSheet;
ssfworkbook.SetSheetHidden(1, true); //隐藏
sheet2.CreateRow(0).CreateCell(0).SetCellValue("itemA"); //列数据
sheet2.CreateRow(1).CreateCell(0).SetCellValue("itemB");
sheet2.CreateRow(2).CreateCell(0).SetCellValue("itemC");
HSSFName range = ssfworkbook.CreateName() as HSSFName;//创建名称
// range.Reference = "ShtDictionary!$A$1:$A$3";//格式
range.NameName = "dicRange";
#endregion
headerRow.CreateCell(1).SetCellValue("Selected");
sheet1.SetColumnWidth(1, 15 * 256);
//将下拉列表添加
CellRangeAddressList regions = new CellRangeAddressList(1, 65535, 1, 1);
DVConstraint constraint = DVConstraint.CreateFormulaListConstraint("dicRange");
HSSFDataValidation dataValidate = new HSSFDataValidation(regions, constraint);
sheet1.AddValidationData(dataValidate);
headerRow.CreateCell(2).SetCellValue("VALUE");
sheet1.SetColumnWidth(2, 15 * 256);
//写入数据
//创建数据行
HSSFRow dataRow = (HSSFRow)sheet1.CreateRow(1);
//填充数据
dataRow.CreateCell(0).SetCellValue("1"); //id
dataRow.CreateCell(1).SetCellValue(""); //选择框
dataRow.CreateCell(2).SetCellValue("值"); //选择框
System.IO.MemoryStream ms = new System.IO.MemoryStream();
ssfworkbook.Write(ms);
string filename = "Sheet1" + DateTime.Now.Year.ToString() + DateTime.Now.Month.ToString() + DateTime.Now.Day.ToString() + DateTime.Now.Hour.ToString() + DateTime.Now.Minute.ToString() + DateTime.Now.Second.ToString() + ".xls";
object Response = null;
string workbookFile = @"D:\\wulei.xls";
FileStream fs = new FileStream(workbookFile, FileMode.Create, FileAccess.Write);
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
fs.Close();
//Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + filename + ""));
//Response.BinaryWrite(ms.ToArray());
ms.Close();
ms.Dispose();
}
/// <summary>
/// Excel数据列表
/// </summary>
/// <param name="dt"></param>
/// <param name="strfileName"></param>
/// <param name="dCheck"></param>
public int WriteWorkSheetException(DataSet ds, string strfileName, string sheetName)
{
try
{
int count = 0;
using (FileStream fs = new FileStream(strfileName, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
ISheet sheet = null;
sheet = workbook.GetSheet(sheetName);
//工作簿解密
sheet.ProtectSheet(null);
ICellStyle style = GetCellBorderException(workbook);
using (FileStream fsm = new FileStream(strfileName, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
for (int iRow = 0; iRow < ds.Tables[0].Rows.Count; iRow++)
{
int RowCount = sheet.GetRow(0).LastCellNum - 1;
for (int i = 0; i < RowCount; i++)
{
ICell Cell = sheet.GetRow(iRow + 1).GetCell(i);
if (Cell == null)
{
Cell = sheet.GetRow(iRow + 1).CreateCell(i);
}
Cell.SetCellValue(ds.Tables[0].Rows[iRow][i].ToString());
}
}
for (int iRow = 0; iRow < ds.Tables[1].Rows.Count; iRow++)
{
ICell Cell = sheet.GetRow(iRow + 1).GetCell(4);
if (Cell == null)
{
Cell = sheet.GetRow(iRow + 1).CreateCell(4);
}
Cell.SetCellValue(ds.Tables[1].Rows[iRow][0].ToString());
}
//工作簿加密
sheet.ProtectSheet(EXCELPWD);
workbook.SetSheetHidden(1, SheetState.VeryHidden);
workbook.Write(fsm);
fsm.Close();
}
}
return(count);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 引用另一个工作表的形式 得到下拉
/// </summary>
/// <param name="book"></param>
/// <param name="columnIndex"></param>
/// <param name="values"></param>
/// <param name="sheetName"></param>
/// <returns></returns>
public static void CreateListConstaint(this HSSFWorkbook book, Int32 columnIndex, IEnumerable <String> values, string sheetName = "")
{
if (values == null)
{
return;
}
if (string.IsNullOrEmpty(sheetName))
{
sheetName = "_constraintSheet_";
}
//创建下拉数据到新表中
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);;
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = string.Empty;
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果无可选值的话，则增加一个空选项，防止用户填写内容
if (values.Count() == 0)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(" ");
rowIndex++;
}
//给该列所有单元格加上下拉选择
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), SheetState.HIDDEN);
//创建验证
HSSFDataValidation valid = new HSSFDataValidation(cellRegions, constraint);
//关联验证
HSSFSheet v = book.GetSheetAt(0) as HSSFSheet;
v.AddValidationData(valid);
}
private void WriteReportWithData()
{
var reportdata = _data.ReportData.ToList().AsParallel();
InitializeWorkbook();
var sheet = _hssfworkbook.CreateSheet("request");
_hssfworkbook.CreateSheet("hidden");
var style = _hssfworkbook.CreateCellStyle();
style.FillForegroundColor = HSSFColor.DarkBlue.Index;
style.FillPattern = FillPattern.SolidForeground;
var font = _hssfworkbook.CreateFont();
font.Color = HSSFColor.White.Index;
style.SetFont(font);
var rows = reportdata.Count() + 1;
for (var j = 0; j < rows; j++)
{
var row = sheet.CreateRow(j);
for (var i = 0; i < _data.Columns.Count; i++)
{
var header = _data.Columns.ElementAt(i);
if (j == 0)
{
var cell = row.CreateCell(i);
cell.SetCellValue(header);
cell.CellStyle = style;
}
else
{
var dictionary = reportdata.ElementAt(j - 1);
row.CreateCell(i).SetCellValue(dictionary[header]);
}
}
sheet.AutoSizeColumn(j);
}
sheet.SetColumnHidden(0, true);
var namedcell = _hssfworkbook.CreateName();
namedcell.NameName = "hidden";
var constraint = DVConstraint.CreateFormulaListConstraint("hidden");
var addressList = new CellRangeAddressList(1, reportdata.Count(), _data.Columns.Count,
_data.Columns.Count);
var validation = new HSSFDataValidation(addressList, constraint);
_hssfworkbook.SetSheetHidden(1, true);
}
public void TestHide()
{
wbU.SetSheetHidden(0, true);
Assert.IsTrue(wbU.IsSheetHidden(0));
Assert.IsFalse(wbU.IsSheetHidden(1));
MemoryStream out1 = new MemoryStream();
wbU.Write(out1);
out1.Close();
HSSFWorkbook wb2 = new HSSFWorkbook(new MemoryStream(out1.ToArray()));
Assert.IsTrue(wb2.IsSheetHidden(0));
Assert.IsFalse(wb2.IsSheetHidden(1));
}
public void SetSheetHidden(string sheetName)
{
int i = _workbook.GetSheetIndex(_workbook.GetSheet(sheetName));
if (i > -1)
{
_workbook.SetSheetHidden(i, true);
}
if (i < _workbook.NumberOfSheets - 1)
{
_workbook.SetActiveSheet(i + 1);
}
else
{
_workbook.SetActiveSheet(i - 1);
}
}
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values)
{
var sheetName = "_constraintSheet_";
ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName);
var firstRow = sheet.GetRow(0);
var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells;
var rowIndex = 0;
var lastValue = "";
foreach (var value in values)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(value);
rowIndex++;
lastValue = value;
}
//如果只有一个可选值，则增加一个相同的选项
if (values.Count() == 1)
{
var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex);
row.CreateCell(conColumnIndex).SetCellValue(lastValue);
rowIndex++;
}
IName range = book.CreateName();
range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}",
(Char)('A' + conColumnIndex),
rowIndex.ToString(), sheetName);
string rangeName = "dicRange" + columnIndex;
range.NameName = rangeName;
var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
var constraint = DVConstraint.CreateFormulaListConstraint(rangeName);
book.SetSheetHidden(book.GetSheetIndex(sheet), true);
return(new HSSFDataValidation(cellRegions, constraint));
}
/// <summary>
/// 生成模板
/// </summary>
/// <param name="displayName">文件名</param>
/// <returns>生成的模版文件</returns>
public byte[] GenerateTemplate(out string displayName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
InitExcelData();
CreateDataTable(); //add by dufei
SetTemplateDataValus(); //add by dufei
if (!string.IsNullOrEmpty(FileDisplayName))
{
displayName = FileDisplayName + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
else
{
displayName = this.GetType().Name + "_" + DateTime.Now.ToString("yyyy-MM-dd") + "_" + DateTime.Now.ToString("hh^mm^ss") + ".xls";
}
//模板sheet页
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
workbook.SetSheetName(0, string.IsNullOrEmpty(FileDisplayName) ? this.GetType().Name : FileDisplayName);
HSSFRow row = (HSSFRow)sheet.CreateRow(0);
row.HeightInPoints = 20;
HSSFSheet enumSheet = (HSSFSheet)workbook.CreateSheet();
HSSFRow enumSheetRow1 = (HSSFRow)enumSheet.CreateRow(0);
enumSheetRow1.CreateCell(0).SetCellValue(Program._localizer?["Yes"]);
enumSheetRow1.CreateCell(1).SetCellValue(Program._localizer?["No"]);
enumSheetRow1.CreateCell(2).SetCellValue(this.GetType().Name); //为模板添加标记,必要时可添加版本号
HSSFSheet dataSheet = (HSSFSheet)workbook.CreateSheet();
#region 设置excel模板列头
//默认灰色
var headerStyle = GetCellStyle(workbook);
headerStyle.IsLocked = true;
//黄色
var yellowStyle = GetCellStyle(workbook, BackgroudColorEnum.Yellow);
yellowStyle.IsLocked = true;
//红色
var redStyle = GetCellStyle(workbook, BackgroudColorEnum.Red);
redStyle.IsLocked = true;
//取得所有ExcelPropety
var propetys = this.GetType().GetFields().Where(x => x.FieldType == typeof(ExcelPropety)).ToList();
int _currentColunmIndex = 0;
bool IsProtect = false;
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
ExcelPropety excelPropety = (ExcelPropety)propetys[porpetyIndex].GetValue(this);
ColumnDataType dateType = excelPropety.DataType;
if (excelPropety.ReadOnly)
{
IsProtect = true;
}
//给必填项加星号
string colName = excelPropety.IsNullAble ? excelPropety.ColumnName : excelPropety.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(colName);
//修改列头样式
switch (excelPropety.BackgroudColor)
{
case BackgroudColorEnum.Yellow:
row.Cells[_currentColunmIndex].CellStyle = yellowStyle;
break;
case BackgroudColorEnum.Red:
row.Cells[_currentColunmIndex].CellStyle = redStyle;
break;
default:
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
break;
}
var dataStyle = workbook.CreateCellStyle();
var dataFormat = workbook.CreateDataFormat();
if (dateType == ColumnDataType.Dynamic)
{
int dynamicColCount = excelPropety.DynamicColumns.Count();
for (int dynamicColIndex = 0; dynamicColIndex < dynamicColCount; dynamicColIndex++)
{
var dynamicCol = excelPropety.DynamicColumns.ToList()[dynamicColIndex];
string dynamicColName = excelPropety.IsNullAble ? dynamicCol.ColumnName : dynamicCol.ColumnName + "*";
row.CreateCell(_currentColunmIndex).SetCellValue(dynamicColName);
row.Cells[_currentColunmIndex].CellStyle = headerStyle;
if (dynamicCol.ReadOnly)
{
IsProtect = true;
}
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置单元格样式及数据类型
dataStyle.IsLocked = excelPropety.ReadOnly;
dynamicCol.SetColumnFormat(dynamicCol.DataType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
else
{
//设定列宽
if (excelPropety.CharCount > 0)
{
sheet.SetColumnWidth(_currentColunmIndex, excelPropety.CharCount * 256);
dataStyle.WrapText = true;
}
else
{
sheet.AutoSizeColumn(_currentColunmIndex);
}
//设置是否锁定
dataStyle.IsLocked = excelPropety.ReadOnly;
//设置单元格样式及数据类型
excelPropety.SetColumnFormat(dateType, _currentColunmIndex, sheet, dataSheet, dataStyle, dataFormat);
_currentColunmIndex++;
}
}
#endregion
#region 添加模版数据 add by dufei
if (TemplateDataTable.Rows.Count > 0)
{
for (int i = 0; i < TemplateDataTable.Rows.Count; i++)
{
DataRow tableRow = TemplateDataTable.Rows[i];
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(1 + i);
for (int porpetyIndex = 0; porpetyIndex < propetys.Count(); porpetyIndex++)
{
string colName = propetys[porpetyIndex].Name;
tableRow[colName].ToString();
dataRow.CreateCell(porpetyIndex).SetCellValue(tableRow[colName].ToString());
}
}
}
#endregion
//冻结行
sheet.CreateFreezePane(0, 1, 0, 1);
//锁定excel
if (IsProtect)
{
sheet.ProtectSheet("password");
}
workbook.SetSheetHidden(1, true);
workbook.SetSheetHidden(2, true);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms.ToArray());
}
public void CopyForm(string source, string target, string saveas, int maxcols, SqlDataReader reader)
{
List <DateTime> DateSheetCollection = new List <DateTime>();
bool TopDeptGroupStyle = (ConfigurationManager.AppSettings["TopDeptGroupStyle"] ?? "0") == "1";
if (TopDeptGroupStyle)
{
try
{
reader.GetSchemaTable().DefaultView.RowFilter = "ColumnName= 'DateSheetCollection'";
if (reader.GetSchemaTable().DefaultView.Count > 0 && reader["DateSheetCollection"] != null)
{
string[] datelist = reader["DateSheetCollection"].ToString().Split(new char[] { ',', ';', '|' });
foreach (string str in datelist)
{
DateTime dt;
if (DateTime.TryParse(str, out dt))
{
DateSheetCollection.Add(dt);
}
}
}
}
catch
{ }
}
try
{
WriteToLog(saveas);
//string savefolder = ConfigurationManager.AppSettings["savefolder"].ToString();
string uploadshipdate = ConfigurationManager.AppSettings["uploadshipdate"].ToString();
string ExclusiveStyles = ConfigurationManager.AppSettings["ExclusiveStyles"] ?? "0";
//Open a copy of the template
//OpenFile(target, "", "target");
FileStream sourceFileStream = new FileStream(source, FileMode.Open, FileAccess.Read);
NPOI.POIFS.FileSystem.POIFSFileSystem sourceFile = new NPOI.POIFS.FileSystem.POIFSFileSystem(sourceFileStream);
//FileStream sourceFile = new FileStream(source, FileMode.Open, FileAccess.Read);
HSSFWorkbook sourceBook = new HSSFWorkbook(sourceFile);
FileStream tempFileStream = new FileStream(target, FileMode.Open, FileAccess.Read);
NPOI.POIFS.FileSystem.POIFSFileSystem tempFile = new NPOI.POIFS.FileSystem.POIFSFileSystem(tempFileStream);
HSSFWorkbook tempBook = new HSSFWorkbook(tempFile);
//Migrate the generated sheets into the copy of the template
WriteToLog("begin move");
var sheetNum = tempBook.NumberOfSheets;
//tempBook.CreateSheet("WholesalePrice");
//tempBook..CopySheets((HSSFSheet)sourceBook.GetSheetAt(2), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(2)).CopyTo(tempBook, "WholesalePrice", true, true);
sheetNum++;
int tmpLogoIdx = 0;
//HSSFSheet ordTmpSheet = (HSSFSheet)tempBook.CreateSheet("Order Template");
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(0), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(0)).CopyTo(tempBook, "Order Template", true, true);
HSSFSheet ordTmpSheet = (HSSFSheet)tempBook.GetSheetAt(sheetNum);
sheetNum++;
List <HSSFSheet> dtSheets = new List <HSSFSheet>();
for (int i = 0; i < DateSheetCollection.Count; i++)
{
var logoPath = ConfigurationManager.AppSettings["LogoPath"];
byte[] bytes = System.IO.File.ReadAllBytes(logoPath);
int LogoIdx = tempBook.AddPicture(bytes, PictureType.JPEG);
//HSSFSheet dtSheet = (HSSFSheet)tempBook.CreateSheet(string.Format("{0:ddMMMyyyy}", DateSheetCollection[i]));
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(0), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(0)).CopyTo(tempBook, string.Format("{0:ddMMMyyyy}", DateSheetCollection[i]), true, true);
HSSFSheet dtSheet = (HSSFSheet)tempBook.GetSheetAt(sheetNum);
HSSFPatriarch patriarch = (HSSFPatriarch)dtSheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(50 * 1, 50 * 1, 500, 100, 0, 0, 1, 2);
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, LogoIdx);
HSSFRow r = (HSSFRow)dtSheet.GetRow(0);
var dtcell = r.GetCell(0) == null ? (HSSFCell)r.CreateCell(0) : (HSSFCell)r.GetCell(0);
dtcell.SetCellValue(DateSheetCollection[i]);
dtSheets.Add(dtSheet);
sheetNum++;
}
//tempBook.CreateSheet("WebSKU");
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(1), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(1)).CopyTo(tempBook, "WebSKU", true, true);
sheetNum++;
//ISheet skuSheet = tempBook.CreateSheet("CellFormats");
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(3), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(3)).CopyTo(tempBook, "CellFormats", true, true);
ISheet skuSheet = tempBook.GetSheetAt(sheetNum);
sheetNum++;
//HSSFSheet atpSheet = (HSSFSheet)tempBook.CreateSheet("ATPDate");
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(4), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(4)).CopyTo(tempBook, "ATPDate", true, true);
ISheet atpSheet = tempBook.GetSheetAt(sheetNum);
sheetNum++;
var upcSheetName = ConfigurationManager.AppSettings["CatalogUPCSheetName"];
var lastsheet = 4;
int exclusiveStylesSheetIndex = 5;
if (!string.IsNullOrEmpty(upcSheetName))
{
exclusiveStylesSheetIndex = 6;
//tempBook.CreateSheet(upcSheetName);
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(5), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(5)).CopyTo(tempBook, upcSheetName, true, true);
sheetNum++; lastsheet++;
}
if (!string.IsNullOrEmpty(ExclusiveStyles) && ExclusiveStyles == "1")
{
//tempBook.CreateSheet("ExclusiveStyles");
//tempBook.CopySheets((HSSFSheet)sourceBook.GetSheetAt(exclusiveStylesSheetIndex), (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
((HSSFSheet)sourceBook.GetSheetAt(exclusiveStylesSheetIndex)).CopyTo(tempBook, "ExclusiveStyles", true, true);
tempBook.SetSheetHidden(sheetNum, SheetState.HIDDEN);
sheetNum++; lastsheet++;
}
// Solo => Add validation and lock for cell on server side.
PresetForProductSheet(ordTmpSheet, skuSheet);
if (dtSheets.Count > 0)
{
tempBook.SetSheetHidden(0, SheetState.HIDDEN);
string[] ATPLevelBackColors = (ConfigurationManager.AppSettings["ATPLevelBackColors"] ?? "").Split(new char[] { ',', '|' }, StringSplitOptions.RemoveEmptyEntries);
short LockedCellBGColor = short.Parse(ConfigurationManager.AppSettings["LockedCellBGColor"] ?? "22");
bool allThinBorder = ConfigurationManager.AppSettings["ThinBorder"] == null ? false : ConfigurationManager.AppSettings["ThinBorder"].ToString() == "1" ? true : false;
for (int i = 0; i < dtSheets.Count; i++)
{
HSSFSheet dtSheet = dtSheets[i];
Dictionary <int, ICellStyle> colors = new Dictionary <int, ICellStyle>();
ICellStyle lockedStyle = tempBook.CreateCellStyle();
lockedStyle.FillForegroundColor = LockedCellBGColor;
lockedStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
lockedStyle.BorderBottom = lockedStyle.BorderLeft = lockedStyle.BorderRight = lockedStyle.BorderTop = allThinBorder ? CellBorderType.THIN : CellBorderType.MEDIUM;
colors.Add(0, lockedStyle);
for (int j = 0; j < ATPLevelBackColors.Length / 2; j++)
{
ICellStyle qtyStyle = tempBook.CreateCellStyle();
qtyStyle.IsLocked = false;
qtyStyle.FillForegroundColor = short.Parse(ATPLevelBackColors[j * 2 + 1]);
qtyStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
qtyStyle.BorderBottom = qtyStyle.BorderLeft = qtyStyle.BorderRight = qtyStyle.BorderTop = allThinBorder ? CellBorderType.THIN : CellBorderType.MEDIUM;
colors.Add(int.Parse(ATPLevelBackColors[j * 2]), qtyStyle);
}
DateTime dt;
if (DateTime.TryParse(dtSheet.SheetName, out dt))
{
PresetForProductSheet(dtSheet, skuSheet, dt, atpSheet, colors);
}
}
}
for (int i = 3; i < 10 + DateSheetCollection.Count; i++)
{
if (i > 6 && i < 7 + DateSheetCollection.Count)
{
continue;
}
tempBook.SetSheetHidden(i, SheetState.HIDDEN);
}
//tempBook.SetSheetHidden(3, SheetState.Hidden);
//tempBook.SetSheetHidden(4, SheetState.Hidden);
//tempBook.SetSheetHidden(5, SheetState.Hidden);
//tempBook.SetSheetHidden(6, SheetState.Hidden);
//tempBook.SetSheetHidden(7, SheetState.Hidden);
//tempBook.SetSheetHidden(8, SheetState.Hidden);
//tempBook.SetSheetHidden(9, SheetState.Hidden);
var sourceBookNum = sourceBook.NumberOfSheets;
for (int i = lastsheet + 1; i < sourceBookNum; i++)
{
HSSFSheet sht = (HSSFSheet)sourceBook.GetSheetAt(i);
for (int k = 0; k < 7; k++)
{
sht.AutoSizeColumn(k);
}
//tempBook.CreateSheet(sht.SheetName);
//tempBook.CopySheets(sht, (HSSFSheet)tempBook.GetSheetAt(sheetNum), true);
sht.CopyTo(tempBook, sht.SheetName, true, true);
sheetNum++;
}
WriteToLog("end move");
//Insert the hard page breaks for printing
WriteToLog("begin insert");
InsertPageBreaks(tempBook, "Order Template");
if (dtSheets.Count > 0)
{
for (int i = 0; i < dtSheets.Count; i++)
{
HSSFSheet dtSheet = dtSheets[i];
InsertPageBreaks(tempBook, dtSheet.SheetName);
}
}
WriteToLog("end insert");
//Set global variables for macro use
WriteToLog("begin set");
SetUploadShipDate(tempBook, uploadshipdate, maxcols, reader);
WriteToLog("end set");
//Set Cancel After Date
SetCancelAfterDate(tempBook);
FileStream orderFile = new FileStream(saveas, FileMode.OpenOrCreate, FileAccess.ReadWrite);
tempBook.Write(orderFile);
sourceFileStream.Close();
tempFileStream.Close();
orderFile.Close();
sourceBook = null;
tempBook = null;
System.GC.Collect();
//if (File.Exists(@savefolder + "test.xls"))
// File.Delete(@savefolder + "test.xls");
if (File.Exists(source))
{
File.Delete(source);
}
}
catch (Exception e)
{
string msg = e.Message;
throw e;
}
}
