/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">数据源</param>
/// <param name="sHeaderText">表头文本</param>
/// <param name="columns">需要导出的属性</param>
private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = ReflectionHelper.GetProperties(type, columns);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[properties.Length];
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
//GBK对应的code page是CP936
arrColWidth[columnIndex] = properties[columnIndex].Name.Length;
}
#endregion
for (int rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
// 类属性如果有Description就用Description当做列名
DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute));
string description = properties[columnIndex].Name;
if (customAttribute != null)
{
description = customAttribute.Description;
}
headerRow.CreateCell(columnIndex).SetCellValue(description);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
}
}
#endregion
}
#endregion
#region 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
newCell.CellStyle = contentStyle;
string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString();
switch (properties[columnIndex].PropertyType.ToString())
{
case "System.String":
newCell.SetCellValue(drValue);
break;
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
newCell.SetCellValue(drValue.ParseToDateTime());
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
newCell.SetCellValue(drValue.ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
case "System.Int16":
case "System.Nullable`1[System.Int16]":
case "System.Int32":
case "System.Nullable`1[System.Int32]":
newCell.SetCellValue(drValue.ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
newCell.SetCellValue(drValue.ParseToString());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.DBNull":
newCell.SetCellValue(string.Empty);
break;
default:
newCell.SetCellValue(string.Empty);
break;
}
}
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.CENTER;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
//MemoryStream ms = new MemoryStream();
//workbook.Write(ms);
//return ms;
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public void TestOptimiseFonts()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Add 6 fonts, some duplicates
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
IFont f3 = wb.CreateFont();
f3.FontHeight = ((short)33);
f3.FontName = ("Unique");
IFont f4 = wb.CreateFont();
f4.FontHeight = ((short)11);
f4.FontName = ("Testing");
IFont f5 = wb.CreateFont();
f5.FontHeight = ((short)22);
f5.FontName = ("Also Testing");
IFont f6 = wb.CreateFont();
f6.FontHeight = ((short)66);
f6.FontName = ("Also Unique");
// Use all three of the four in cell styles
Assert.AreEqual(21, wb.NumCellStyles);
Npoi.Core.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Assert.AreEqual(5, cs1.FontIndex);
Npoi.Core.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f4);
Assert.AreEqual(8, cs2.FontIndex);
Npoi.Core.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f5);
Assert.AreEqual(9, cs3.FontIndex);
Npoi.Core.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f6);
Assert.AreEqual(10, cs4.FontIndex);
Assert.AreEqual(25, wb.NumCellStyles);
// And three in rich text
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
HSSFRichTextString rtr1 = new HSSFRichTextString("Test");
rtr1.ApplyFont(0, 2, f1);
rtr1.ApplyFont(3, 4, f2);
r.CreateCell(0).SetCellValue(rtr1);
HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");
rtr2.ApplyFont(0, 2, f3);
rtr2.ApplyFont(3, 5, f5);
rtr2.ApplyFont(6, 8, f6);
r.CreateCell(1).SetCellValue(rtr2);
// Check what we have now
Assert.AreEqual(10, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Optimise
HSSFOptimiser.OptimiseFonts(wb);
// Check font count
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// Check font use in cell styles
Assert.AreEqual(5, cs1.FontIndex);
Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
Assert.AreEqual(8, cs4.FontIndex); // two have gone
// And in rich text
// RTR 1 had f1 and f2, unchanged
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
// RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(6));
Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(7));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = workbook.CreateSheet("Sheet1");
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://bimpan.iok.la:88/";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = ""; //填加xls文件作者信息
si.ApplicationName = "由堃哥编写的Excel导出程序"; //填加xls文件创建程序信息
si.LastAuthor = ""; //填加xls文件最后保存者信息
si.Comments = "说明信息"; //填加xls文件作者信息
si.Title = "任务报表"; //填加xls文件标题信息
si.Subject = "任务报表"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
HSSFCellStyle dateStyle = workbook.CreateCellStyle();
HSSFDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
HSSFRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
// headerRow.Dispose();
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = sheet.CreateRow(1);
HSSFCellStyle headStyle = workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
HSSFFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//将英文列名转为中文
XmlDocument xmlDoc = new XmlDocument();
string xmlPath = string.Format("{0}/{1}", System.Web.HttpContext.Current.Request.PhysicalApplicationPath, "Config/TaskCHSName.xml");
xmlDoc.Load(xmlPath);
Dictionary <string, string> dicEN2CH = Common.GetNodelist(xmlDoc.InnerXml, "Root");
foreach (DataColumn column in dtSource.Columns)
{
string columnNameCH = dicEN2CH.ContainsKey(column.ColumnName.ToUpper()) ? dicEN2CH[column.ColumnName.ToUpper()] : column.ColumnName;
headerRow.CreateCell(column.Ordinal).SetCellValue(columnNameCH);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public static bool GetIndividualVisaExcel(List <TravelAgency.Model.VisaInfo> list, string remark, string groupNo)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("签证申请人名单");
//2.1创建表头
IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("签证申请人名单");
rowHeader.HeightInPoints = 50;
IRow row = sheet.CreateRow(1);
row.CreateCell(0).SetCellValue("编号");
row.CreateCell(1).SetCellValue("姓名(中文)");
row.CreateCell(2).SetCellValue("姓名(英文)");
row.CreateCell(3).SetCellValue("性别");
row.CreateCell(4).SetCellValue("护照发行地");
row.CreateCell(5).SetCellValue("居住地点");
row.CreateCell(6).SetCellValue("出生年月日");
row.CreateCell(7).SetCellValue("职业");
row.CreateCell(8).SetCellValue("出境记录");
row.CreateCell(9).SetCellValue("婚姻");
row.CreateCell(10).SetCellValue("身份确认");
row.CreateCell(11).SetCellValue("经济能力确认");
row.CreateCell(12).SetCellValue("备注");
row.CreateCell(13).SetCellValue("旅行社意见");
row.CreateCell(14).SetCellValue("护照号");
//row.CreateCell(15).SetCellValue("手机号");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256); //编号
sheet.SetColumnWidth(1, 15 * 256); //姓名(中文)
sheet.SetColumnWidth(2, 20 * 256); //姓名(英文)
sheet.SetColumnWidth(3, 5 * 256); //性别
sheet.SetColumnWidth(4, 10 * 256); //护照发行地
sheet.SetColumnWidth(5, 25 * 256); //居住地点
sheet.SetColumnWidth(6, 15 * 256); //出生年月日
sheet.SetColumnWidth(7, 10 * 256); //职业
sheet.SetColumnWidth(8, 10 * 256); //出境记录
sheet.SetColumnWidth(9, 10 * 256); //婚姻
sheet.SetColumnWidth(10, 20 * 256); //身份确认
sheet.SetColumnWidth(11, 25 * 256); //经济能力确认
sheet.SetColumnWidth(12, 10 * 256); //备注
sheet.SetColumnWidth(13, 10 * 256); //旅行社意见
sheet.SetColumnWidth(14, 15 * 256); //护照号
//sheet.SetColumnWidth(15, 15 * 256);//手机号
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 2);
//设置行高
row.HeightInPoints = 100;
//设置值
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].EnglishName);
row.CreateCell(3).SetCellValue(list[i].Sex);
row.CreateCell(4).SetCellValue(list[i].IssuePlace);
row.CreateCell(5).SetCellValue(list[i].Residence);
row.CreateCell(6).SetCellValue(DateTimeFormator.DateTimeToString(list[i].Birthday));
row.CreateCell(7).SetCellValue(list[i].Occupation);
row.CreateCell(8).SetCellValue(list[i].DepartureRecord);
row.CreateCell(9).SetCellValue(list[i].Marriaged);
row.CreateCell(10).SetCellValue(list[i].Identification);
row.CreateCell(11).SetCellValue(list[i].FinancialCapacity);
row.CreateCell(12).SetCellValue(remark);
row.CreateCell(13).SetCellValue(list[i].AgencyOpinion);
row.CreateCell(14).SetCellValue(list[i].PassportNo);
//row.CreateCell(15).SetCellValue(list[i].Phone);
}
//4.2合并单元格
sheet.AddMergedRegion(new CellRangeAddress(2, sheet.LastRowNum, 12, 12)); //备注列合并
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //表头合并
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true; //文本自动换行
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
HSSFFont font = (HSSFFont)wkbook.CreateFont();
font.FontHeightInPoints = 12;
style.SetFont(font);
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
ICellStyle headerStyle = wkbook.CreateCellStyle();
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
HSSFFont font1 = (HSSFFont)wkbook.CreateFont();
font1.FontHeightInPoints = 15;
headerStyle.SetFont(font1);
sheet.GetRow(0).GetCell(0).CellStyle = headerStyle;
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
public Stream ToExcel()
{
int rowIndex = 0;
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow row = sheet.CreateRow(rowIndex);
row.Height = 200 * 3;
//表头样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;//居中对齐
//表头单元格背景色
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//表头单元格边框
style.BorderBottom = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.VerticalAlignment = VerticalAlignment.Center;
//表头字体设置
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12; //字号
font.Boldweight = 600; //加粗
//font.Color = HSSFColor.WHITE.index;//颜色
style.SetFont(font);
//数据样式
ICellStyle datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Left;//左对齐
//数据单元格的边框
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.TopBorderColor = HSSFColor.Black.Index;
datastyle.RightBorderColor = HSSFColor.Black.Index;
datastyle.BottomBorderColor = HSSFColor.Black.Index;
datastyle.LeftBorderColor = HSSFColor.Black.Index;
//数据的字体
IFont datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
datastyle.SetFont(datafont);
//设置列宽
sheet.SetColumnWidth(0, 20 * 256);
int colWidth0 = sheet.GetColumnWidth(0);
sheet.SetColumnWidth(1, 20 * 256);
int colWidth1 = sheet.GetColumnWidth(1);
sheet.SetColumnWidth(2, 20 * 256);
int colWidth2 = sheet.GetColumnWidth(2);
sheet.SetColumnWidth(9, 40 * 256);
sheet.DisplayGridlines = false;
try {
//表头数据
for (int i = 0; i < titles.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(titles[i]);
cell.CellStyle = style;
}
for (int k = 0; k < data.Count; k++)
{
row = sheet.CreateRow(k + 1);
row.Height = 200 * 2;
T t = data[k];
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
for (int j = 0; j < propertys.Length; j++)
{
var value = propertys[j].GetValue(null, null).ToString();
ICell cell = row.CreateCell(j);
cell.SetCellValue(value);
cell.CellStyle = datastyle;
}
}
} catch (Exception ex) {
} finally {
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
}
return(ms);
}
//public void Create()
//{
// HSSFWorkbook book = new HSSFWorkbook();
// ISheet sheet = book.CreateSheet("Sheet1");
// IRow row = sheet.CreateRow(20);//index代表多少行
// row.HeightInPoints = 35;//行高
// ICell cell = row.CreateCell(0);//创建第一列
// cell.SetCellValue("设置单元格的值");
//}
/// <summary>
/// 导出基本操作示例方法
/// </summary>
public static void ExportExcel()
{
//初始化一个新的HSSFWorkbook实例
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//设置excel必须的文件属性（该属性用来存储 如作者、标题、标记、备注、主题等信息，右键可查看的属性信息）
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.DocumentSummaryInformation = dsi;
hssfworkbook.SummaryInformation = si;
//新建一个Workbook默认都会新建3个Sheet(标准的Excel文件有3个Sheet)。所以必须加入下面的创建Sheet的代码才能保证生成的文件正常
HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("new sheet");
// hssfworkbook.CreateSheet("Sheet1");
// hssfworkbook.CreateSheet("Sheet2");
// hssfworkbook.CreateSheet("Sheet3");
//建创行
Row row1 = sheet.CreateRow(0);
//建单元格，比如创建A1位置的单元格：
row1.Height = 500;
CellStyle s = hssfworkbook.CreateCellStyle();
s.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
s.FillPattern = FillPatternType.SOLID_FOREGROUND;
//第一列
Cell cell1 = row1.CreateCell(0);
cell1.CellStyle = s;
Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 20;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
cell1.CellStyle.SetFont(font);
cell1.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
cell1.SetCellValue("《大明宫保护办月报月计划》——( 行政管理部)6月工作计划");
cell1.CellStyle.BorderBottom = CellBorderType.THIN;
cell1.CellStyle.BorderLeft = CellBorderType.THIN;
cell1.CellStyle.BorderRight = CellBorderType.THIN;
cell1.CellStyle.BorderTop = CellBorderType.THIN;
sheet.SetColumnWidth(0, 4 * 256);
//第二列
Cell cell2 = row1.CreateCell(1);
cell2.CellStyle = s;
sheet.SetColumnWidth(1, 12 * 256);
//第三列
Cell cell3 = row1.CreateCell(2);
cell3.CellStyle = s;
sheet.SetColumnWidth(2, 20 * 256);
//第四列
Cell cell4 = row1.CreateCell(3);
cell4.CellStyle = s;
sheet.SetColumnWidth(3, 25 * 256);
//第五列
Cell cell5 = row1.CreateCell(4);
cell5.CellStyle = s;
sheet.SetColumnWidth(4, 35 * 256);
//第六列
Cell cell6 = row1.CreateCell(5);
cell6.CellStyle = s;
sheet.SetColumnWidth(5, 20 * 256);
//第七列
Cell cell7 = row1.CreateCell(6);
cell7.CellStyle = s;
sheet.SetColumnWidth(6, 20 * 256);
//第八列
Cell cell8 = row1.CreateCell(7);
cell8.CellStyle = s;
sheet.SetColumnWidth(7, 20 * 256);
//第9列
Cell cell9 = row1.CreateCell(8);
cell9.CellStyle = s;
sheet.SetColumnWidth(8, 20 * 256);
//第10列
Cell cell10 = row1.CreateCell(9);
cell10.CellStyle = s;
sheet.SetColumnWidth(9, 20 * 256);
//第11列
Cell cell11 = row1.CreateCell(10);
cell11.CellStyle = s;
sheet.SetColumnWidth(10, 20 * 256);
CellRangeAddress r = new CellRangeAddress(0, 0, 0, 10);
sheet.AddMergedRegion(r);
//sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, 10));
CreateRow2(hssfworkbook, sheet);
CreateRow3_4(hssfworkbook, sheet);
_1stModule _1StModule = new _1stModule()
{
_1stModuleName = "管理工作"
};
List <_2ndModule> _2NdModules = new List <_2ndModule>();
_2ndModule _2NdModule = new _2ndModule {
_2ndModuleName = "与战略地图要求相关"
};
DataItem item = new DataItem
{
Work = "保护办十年工作总结",
Result = "30日前完成保护办十年工作总结（总结部分）",
_1stWeek = "根据主要领导意见进行修改",
_2ndWeek = "进行修改",
_3rdWeek = "进行修改",
_4thWeek = "完成总结",
PersonInCharge = "雷博",
Penaty = "50"
};
_2NdModule.datas = new List <DataItem>();
_2NdModule.datas.Add(item);
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与制度、流程、标准、工具相关"
};
_2NdModule.datas = new List <DataItem>();
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与企业文化相关"
};
_2NdModule.datas = new List <DataItem>();
_2NdModules.Add(_2NdModule);
_2NdModule = new _2ndModule {
_2ndModuleName = "与团队建设相关"
};
item = new DataItem
{
Work = "组织公文写作培训",
Result = "30日前完成培训",
_1stWeek = "",
_2ndWeek = "与培训老师确定时间和内容",
_3rdWeek = "与培训老师确定时间和内容",
_4thWeek = "完成培训",
PersonInCharge = "王倩",
Penaty = "50"
};
_2NdModule.datas = new List <DataItem>();
_2NdModule.datas.Add(item);
_2NdModules.Add(_2NdModule);
_1StModule._2ndModules = _2NdModules;
CreateRowsByModules(_1StModule, sheet, hssfworkbook);
//把这个HSSFWorkbook实例写入文件
FileStream file = new FileStream("Example1.xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
}
public async Task <ActionResult> ExportToExcel(MaterialUnloadingDetailQueryViewModel model)
{
IList <MaterialUnloadingDetail> lst = new List <MaterialUnloadingDetail>();
using (MaterialUnloadingServiceClient client = new MaterialUnloadingServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime Desc,Key.UnloadingKey,Key.ItemNo",
Where = GetWhereCondition(model)
};
MethodReturnResult <IList <MaterialUnloadingDetail> > result = client.GetDetail(ref cfg);
if (result.Code == 0)
{
lst = result.Data;
}
});
}
//创建工作薄。
IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ICell cell = null;
IRow row = null;
ISheet ws = null;
for (int j = 0; j < lst.Count; j++)
{
if (j % 65535 == 0)
{
ws = wb.CreateSheet();
row = ws.CreateRow(0);
#region //列名
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_UnloadingNo); //下料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_RouteOperationName); //工序
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_ProductionLineCode); //生产线
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_EquipmentCode); //设备
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("下料日期"); //下料时间
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_UnloadingTime); //下料时间
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_Operator); //操作人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_LoadingNo); //对应上料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_LoadingItemNo); //对应上料项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_LineStoreName); //线边仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("物料名称"); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_UnloadingQty); //下料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑人"); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑时间"); //编辑时间
#endregion
font.Boldweight = 5;
}
MaterialUnloadingDetail obj = lst[j];
MaterialUnloading objMaterialUnloading = model.GetMaterialUnloading(obj.Key.UnloadingKey);
Material m = model.GetMaterial(obj.MaterialCode);
row = ws.CreateRow(j + 1);
#region //数据
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.UnloadingKey); //下料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(objMaterialUnloading.RouteOperationName); //工序
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(objMaterialUnloading.ProductionLineCode); //生产线
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(objMaterialUnloading.EquipmentCode); //设备
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd}", objMaterialUnloading.UnloadingTime)); //下料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", objMaterialUnloading.UnloadingTime)); //下料时间
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(objMaterialUnloading.Operator); //操作人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(@obj.LoadingKey); //对应上料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LoadingItemNo); //对应上料项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LineStoreName); //线边仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(m == null ? string.Empty : m.Name); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.UnloadingQty); //下料数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Editor); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "MaterialUnloadingData.xls"));
}
private static void CreateRow3_4(HSSFWorkbook hssfworkbook, HSSFSheet sheet)
{
Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
CellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
//设置单元格的样式：水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//建创行
Row row2 = sheet.CreateRow(2);
string[] strs = new string[] { "NO", "一级模块", "二级模块", "工作\n安排", "结果定义" };
for (int i = 0; i < strs.Length; i++)
{
Cell cell = row2.CreateCell(i);
//cell.CellStyle.SetFont(font);
cell.SetCellValue(strs[i]);
cell.CellStyle = style;
}
var newcell = row2.CreateCell(5);
newcell.CellStyle = style;
newcell.SetCellValue("过程节点和完成时间");
newcell = row2.CreateCell(6);
newcell = row2.CreateCell(7);
newcell = row2.CreateCell(8);
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 5, 2, 8));
newcell = row2.CreateCell(9);
newcell.CellStyle = style;
newcell.SetCellValue("责任人");
newcell = row2.CreateCell(10);
newcell.CellStyle = style;
newcell.SetCellValue("自罚承诺");
Row row3 = sheet.CreateRow(3);
string[] strs2 = new string[] { "", "", "", "", "可衡量、有价值、看得见、摸得着", "第一周", "第二周", "第三周", "第四周", "", "" };
for (int i = 0; i < strs2.Length; i++)
{
Cell cell = row3.CreateCell(i);
//cell.CellStyle.SetFont(font);
cell.SetCellValue(strs2[i]);
cell.CellStyle = style;
}
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 0, 3, 0));
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 1, 3, 1));
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 2, 3, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 3, 3, 3));
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 9, 3, 9));
sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 10, 3, 10));
}
public static void CreateRow2(HSSFWorkbook hssfworkbook, HSSFSheet sheet)
{
NPOI.SS.UserModel.Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
CellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
//设置单元格的样式：水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
//建创行
Row row2 = sheet.CreateRow(1);
row2.Height = 350;
//第一列
Cell cell = row2.CreateCell(0);
cell.CellStyle.SetFont(font);
cell.SetCellValue("报告人");
cell.CellStyle = style;
cell = row2.CreateCell(1);
cell.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(1, 0, 1, 1));
cell = row2.CreateCell(2);
cell.CellStyle = style;
cell.SetCellValue("雷博");
cell = row2.CreateCell(3);
cell.CellStyle = style;
cell.SetCellValue("部门");
cell = row2.CreateCell(4);
cell.CellStyle = style;
cell.SetCellValue("行政管理部");
cell = row2.CreateCell(5);
cell.CellStyle = style;
cell.SetCellValue("开始日期");
cell = row2.CreateCell(6);
cell.CellStyle = style;
cell.SetCellValue("2019.6.1");
cell = row2.CreateCell(7);
cell.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(1, 6, 1, 7));
cell = row2.CreateCell(8);
cell.CellStyle = style;
cell.SetCellValue("结束日期");
cell = row2.CreateCell(9);
cell.CellStyle = style;
cell.SetCellValue("2019.6.30");
cell = row2.CreateCell(10);
cell.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.Region(1, 9, 1, 10));
}
private static void CreateRowsByModules(_1stModule module, HSSFSheet sheet, HSSFWorkbook hssfworkbook)
{
int offset = 4;
for (int i = 0; i < module._2ndModules.Count; i++)
{
for (int j = 0; j < module._2ndModules[i].datas.Count; j++)
{
offset++;
Row row2 = sheet.CreateRow(offset);
Font font = hssfworkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 12;
//设置字体加粗样式
font.Boldweight = (short)FontBoldWeight.BOLD;
CellStyle style = hssfworkbook.CreateCellStyle();
//设置边框
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.WrapText = true;
//设置单元格的样式：水平对齐居中
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
//使用SetFont方法将字体样式添加到单元格样式中
style.SetFont(font);
var cell = row2.CreateCell(3);
cell.SetCellValue(module._2ndModules[i].datas[j].Work);
cell.CellStyle = style;
cell = row2.CreateCell(4);
cell.SetCellValue(module._2ndModules[i].datas[j].Result);
cell.CellStyle = style;
cell = row2.CreateCell(5);
cell.SetCellValue(module._2ndModules[i].datas[j]._1stWeek);
cell.CellStyle = style;
cell = row2.CreateCell(6);
cell.SetCellValue(module._2ndModules[i].datas[j]._2ndWeek);
cell.CellStyle = style;
cell = row2.CreateCell(7);
cell.SetCellValue(module._2ndModules[i].datas[j]._3rdWeek);
cell.CellStyle = style;
cell = row2.CreateCell(8);
cell.SetCellValue(module._2ndModules[i].datas[j]._4thWeek);
cell.CellStyle = style;
cell = row2.CreateCell(9);
cell.SetCellValue(module._2ndModules[i].datas[j].PersonInCharge);
cell.CellStyle = style;
cell = row2.CreateCell(10);
cell.SetCellValue(module._2ndModules[i].datas[j].Penaty);
cell.CellStyle = style;
}
}
}
/// <summary>
/// 生成EXECL文件，通过读取DataTable和列头映射信息
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="ColumnInfoList">列字段映射信息</param>
/// <returns>文件流</returns>
public static MemoryStream Export(DataTable dt, List <ColumnInfo> ColumnInfoList)
{
if (dt == null || ColumnInfoList == null)
{
throw new ArgumentNullException();
}
int rowHeight = 20;
//每个标签页最多行数
int sheetRow = 65536;
HSSFWorkbook workbook = new HSSFWorkbook();
//文本样式
ICellStyle centerStyle = workbook.CreateCellStyle();
centerStyle.VerticalAlignment = VerticalAlignment.CENTER;
centerStyle.Alignment = HorizontalAlignment.CENTER;
ICellStyle leftStyle = workbook.CreateCellStyle();
leftStyle.VerticalAlignment = VerticalAlignment.CENTER;
leftStyle.Alignment = HorizontalAlignment.LEFT;
ICellStyle rightStyle = workbook.CreateCellStyle();
rightStyle.VerticalAlignment = VerticalAlignment.CENTER;
rightStyle.Alignment = HorizontalAlignment.RIGHT;
//寻找列头和DataTable之间映射关系
foreach (DataColumn col in dt.Columns)
{
ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
if (info != null)
{
switch (info.Align.ToLower())
{
case "left":
info.Style = leftStyle;
break;
case "center":
info.Style = centerStyle;
break;
case "right":
info.Style = rightStyle;
break;
}
info.IsMapDT = true;
}
}
int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / MAX_ROW_INDEX);
//最多生成5个标签页的数据
sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
ICell cell = null;
object cellValue = null;
for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
{
ISheet sheet = workbook.CreateSheet();
sheet.CreateFreezePane(0, 1, 0, 1);
//输出表头
IRow headerRow = sheet.CreateRow(0);
//设置行高
headerRow.HeightInPoints = rowHeight;
//首行样式
ICellStyle HeaderStyle = workbook.CreateCellStyle();
HeaderStyle.FillPattern = FillPatternType.SOLID_FOREGROUND;
HeaderStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
IFont font = workbook.CreateFont();
font.Boldweight = short.MaxValue;
HeaderStyle.SetFont(font);
HeaderStyle.VerticalAlignment = VerticalAlignment.CENTER;;
HeaderStyle.Alignment = HorizontalAlignment.CENTER;
//输出表头信息 并设置表头样式
int i = 0;
foreach (var data in ColumnInfoList)
{
cell = headerRow.CreateCell(i);
cell.SetCellValue(data.Header.Trim());
cell.CellStyle = HeaderStyle;
i++;
}
//开始循环所有行
int iRow = 1;
int startRow = sheetIndex * (sheetRow - 1);
int endRow = (sheetIndex + 1) * (sheetRow - 1);
endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;
for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
{
IRow row = sheet.CreateRow(iRow);
row.HeightInPoints = rowHeight;
i = 0;
foreach (var item in ColumnInfoList)
{
cell = row.CreateCell(i);
if (item.IsMapDT)
{
cellValue = dt.Rows[rowIndex][item.Field];
cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
cell.CellStyle = item.Style;
}
i++;
}
iRow++;
}
//自适应列宽度
for (int j = 0; j < ColumnInfoList.Count; j++)
{
sheet.AutoSizeColumn(j);
int width = sheet.GetColumnWidth(j) + 2560;
sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(ms);
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_cusName = DTRequest.GetFormString("txtCusName");
_cid = DTRequest.GetFormString("hCusId");
_customer = DTRequest.GetFormString("txtCustomer");
_hcustomer = DTRequest.GetFormString("hCustomer");
_type = DTRequest.GetFormString("ddltype");
_sign = DTRequest.GetFormString("ddlsign");
_money1 = DTRequest.GetFormString("txtMoney1");
_nature = DTRequest.GetFormString("ddlnature");
_sdate = DTRequest.GetFormString("txtsDate");
_edate = DTRequest.GetFormString("txteDate");
_sdate1 = DTRequest.GetFormString("txtsDate1");
_edate1 = DTRequest.GetFormString("txteDate1");
_name = DTRequest.GetFormString("txtName");
_address = DTRequest.GetFormString("txtAddress");
_sign1 = DTRequest.GetFormString("ddlsign1");
_money2 = DTRequest.GetFormString("txtMoney2");
_person1 = DTRequest.GetFormString("txtPerson1");
_person2 = DTRequest.GetFormString("txtPerson2");
_person3 = DTRequest.GetFormString("txtPerson3");
_person4 = DTRequest.GetFormString("txtPerson4");
_person5 = DTRequest.GetFormString("txtPerson5");
_oid = DTRequest.GetFormString("txtOrderID");
_chk = DTRequest.GetFormString("txtChk");
_status = DTRequest.GetFormString("ddlstatus");
_lockstatus = DTRequest.GetFormString("ddllock");
_area = DTRequest.GetFormString("ddlarea");
_sdate2 = DTRequest.GetFormString("txtsDate2");
_edate2 = DTRequest.GetFormString("txteDate2");
_check = DTRequest.GetFormString("ddlcheck");
_self = DTRequest.GetFormString("self");
_detail = DTRequest.GetFormString("txtDetails");
string _where = "";
Dictionary <string, string> dict = getDict(out _where);
DataTable dt = new BLL.finance().getReconciliationDetail(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _p24, out _p25, out _p26, out _p13, out _p14, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=客户对账明细.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("对账明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("订单号");
headRow.CreateCell(1).SetCellValue("客源");
headRow.CreateCell(2).SetCellValue("活动日期");
headRow.CreateCell(3).SetCellValue("活动地点/活动名称");
headRow.CreateCell(4).SetCellValue("对账标识");
headRow.CreateCell(5).SetCellValue("对账金额");
headRow.CreateCell(6).SetCellValue("业务性质/明细");
headRow.CreateCell(7).SetCellValue("业务说明");
headRow.CreateCell(8).SetCellValue("表达式");
headRow.CreateCell(9).SetCellValue("审核");
headRow.CreateCell(10).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "应收" : "应付"));
headRow.CreateCell(11).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "已收" : "已付"));
headRow.CreateCell(12).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "未收" : "未付"));
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
headRow.GetCell(10).CellStyle = titleCellStyle;
headRow.GetCell(11).CellStyle = titleCellStyle;
headRow.GetCell(12).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 10 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 15 * 256);
sheet.SetColumnWidth(8, 15 * 256);
sheet.SetColumnWidth(9, 20 * 256);
sheet.SetColumnWidth(10, 15 * 256);
sheet.SetColumnWidth(11, 15 * 256);
sheet.SetColumnWidth(12, 15 * 256);
if (dt != null)
{
int rowIndex = 1;
DataTable finDt = null;
for (int i = 0; i < dt.Rows.Count; i++)
{
DataSet ds = new BLL.finance().GetList(0, "fin_oid='" + dt.Rows[i]["o_id"] + "'" + _where, _chk, "");
if (ds != null && ds.Tables.Count > 0)
{
finDt = ds.Tables[0];
if (finDt != null && finDt.Rows.Count > 0)
{
for (int j = 0; j < finDt.Rows.Count; j++)
{
IRow row = sheet.CreateRow(rowIndex);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["o_id"].ToString());
row.CreateCell(1).SetCellValue(dt.Rows[i]["c_name"].ToString());
row.CreateCell(2).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["o_sdate"]).Value.ToString("yyyy-MM-dd") + "/" + ConvertHelper.toDate(dt.Rows[i]["o_edate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(3).SetCellValue(dt.Rows[i]["o_address"].ToString() + "/" + dt.Rows[i]["o_content"].ToString());
row.CreateCell(4).SetCellValue(finDt.Rows[j]["chk"].ToString());
row.CreateCell(5).SetCellValue(finDt.Rows[j]["chkMoney"].ToString());
row.CreateCell(6).SetCellValue(finDt.Rows[j]["na_name"] + "/" + finDt.Rows[j]["fin_detail"]);
row.CreateCell(7).SetCellValue(finDt.Rows[j]["fin_illustration"].ToString());
row.CreateCell(8).SetCellValue(finDt.Rows[j]["fin_expression"] + "=" + finDt.Rows[j]["fin_money"]);
row.CreateCell(9).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(finDt.Rows[j]["fin_flag"])]);
row.CreateCell(10).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["fcMoney"] + "/") + dt.Rows[i]["fin_money"]);
row.CreateCell(11).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["chkMoney"] + "/") + dt.Rows[i]["rpd_money"]);
row.CreateCell(12).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["unChkMoney"] + "/") + dt.Rows[i]["unReceiptPay"]);
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
row.GetCell(10).CellStyle = cellStyle;
row.GetCell(11).CellStyle = cellStyle;
row.GetCell(12).CellStyle = cellStyle;
rowIndex++;
}
}
}
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
protected void btnExcel_Click(object sender, EventArgs e)
{
_cusName = DTRequest.GetString("txtCusName");
_cid = DTRequest.GetString("hCusId");
_method = DTRequest.GetFormString("ddlmethod");
_isconfirm = DTRequest.GetFormString("ddlisConfirm");
_sforedate = DTRequest.GetFormString("txtsforedate");
_eforedate = DTRequest.GetFormString("txteforedate");
_sdate = DTRequest.GetFormString("txtsdate");
_edate = DTRequest.GetFormString("txtedate");
_num = DTRequest.GetFormString("txtNum");
_chk = DTRequest.GetFormString("txtChk");
_numdate = DTRequest.GetFormString("txtNumDate");
_self = DTRequest.GetFormString("self");
_sign = DTRequest.GetFormString("ddlsign");
_money = DTRequest.GetFormString("txtMoney");
_moneyType = DTRequest.GetFormString("ddlmoneyType");
_type = DTRequest.GetFormString("ddlType");
_addperson = DTRequest.GetFormString("txtAddPerson");
BLL.ReceiptPay bll = new BLL.ReceiptPay();
DataTable dt = bll.GetList(this.pageSize, this.page, "rp_type=1 " + CombSqlTxt(), "isnull(rp_date,'3000-01-01') desc,isnull(pm_sort,-1) asc,rp_id desc", out this.totalCount, out _tmoney, out _tunmoney, false).Tables[0];
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=收款通知列表.xlsx"); //HttpUtility.UrlEncode(fileName));
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("明细");
IFont font = hssfworkbook.CreateFont();
font.Boldweight = short.MaxValue;
font.FontHeightInPoints = 11;
#region 表格样式
//设置单元格的样式：水平垂直对齐居中
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BottomBorderColor = HSSFColor.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.Black.Index;
cellStyle.RightBorderColor = HSSFColor.Black.Index;
cellStyle.TopBorderColor = HSSFColor.Black.Index;
cellStyle.WrapText = true;//自动换行
//设置表头的样式：水平垂直对齐居中，加粗
ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
titleCellStyle.Alignment = HorizontalAlignment.Center;
titleCellStyle.VerticalAlignment = VerticalAlignment.Center;
titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
titleCellStyle.FillPattern = FillPattern.SparseDots; //图案样式
titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
//设置边框
titleCellStyle.BorderBottom = BorderStyle.Thin;
titleCellStyle.BorderLeft = BorderStyle.Thin;
titleCellStyle.BorderRight = BorderStyle.Thin;
titleCellStyle.BorderTop = BorderStyle.Thin;
titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
titleCellStyle.LeftBorderColor = HSSFColor.Black.Index;
titleCellStyle.RightBorderColor = HSSFColor.Black.Index;
titleCellStyle.TopBorderColor = HSSFColor.Black.Index;
//设置字体
titleCellStyle.SetFont(font);
#endregion
//表头
IRow headRow = sheet.CreateRow(0);
headRow.HeightInPoints = 25;
headRow.CreateCell(0).SetCellValue("收款对象");
headRow.CreateCell(1).SetCellValue("凭证");
headRow.CreateCell(2).SetCellValue("收款内容");
headRow.CreateCell(3).SetCellValue("收款金额");
headRow.CreateCell(4).SetCellValue("未分配金额");
headRow.CreateCell(5).SetCellValue("预收日期");
headRow.CreateCell(6).SetCellValue("收款方式");
headRow.CreateCell(7).SetCellValue("实收日期");
headRow.CreateCell(8).SetCellValue("申请人");
headRow.CreateCell(9).SetCellValue("确认收款");
headRow.GetCell(0).CellStyle = titleCellStyle;
headRow.GetCell(1).CellStyle = titleCellStyle;
headRow.GetCell(2).CellStyle = titleCellStyle;
headRow.GetCell(3).CellStyle = titleCellStyle;
headRow.GetCell(4).CellStyle = titleCellStyle;
headRow.GetCell(5).CellStyle = titleCellStyle;
headRow.GetCell(6).CellStyle = titleCellStyle;
headRow.GetCell(7).CellStyle = titleCellStyle;
headRow.GetCell(8).CellStyle = titleCellStyle;
headRow.GetCell(9).CellStyle = titleCellStyle;
sheet.SetColumnWidth(0, 15 * 256);
sheet.SetColumnWidth(1, 20 * 256);
sheet.SetColumnWidth(2, 20 * 256);
sheet.SetColumnWidth(3, 20 * 256);
sheet.SetColumnWidth(4, 20 * 256);
sheet.SetColumnWidth(5, 15 * 256);
sheet.SetColumnWidth(6, 20 * 256);
sheet.SetColumnWidth(7, 20 * 256);
sheet.SetColumnWidth(8, 20 * 256);
sheet.SetColumnWidth(9, 20 * 256);
if (dt != null)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i + 1);
row.HeightInPoints = 22;
row.CreateCell(0).SetCellValue(dt.Rows[i]["c_name"].ToString() + (Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isExpect"]), false)?"[预]":""));
row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["ce_num"]));
row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_content"]));
row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_money"]));
row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["undistribute"]));
row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_foredate"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"]));
row.CreateCell(7).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd"));
row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_personName"]));
row.CreateCell(9).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isConfirm"]), false) ? "已收款" : "待收款");
row.GetCell(0).CellStyle = cellStyle;
row.GetCell(1).CellStyle = cellStyle;
row.GetCell(2).CellStyle = cellStyle;
row.GetCell(3).CellStyle = cellStyle;
row.GetCell(4).CellStyle = cellStyle;
row.GetCell(5).CellStyle = cellStyle;
row.GetCell(6).CellStyle = cellStyle;
row.GetCell(7).CellStyle = cellStyle;
row.GetCell(8).CellStyle = cellStyle;
row.GetCell(9).CellStyle = cellStyle;
}
}
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
public static Dictionary <TextStyle, CellStyle> CreateDefaultStyels(HSSFWorkbook hssfworkbook)
{
var styles = new Dictionary <TextStyle, CellStyle>();
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 16;
font.Boldweight = 3000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add(TextStyle.Header1, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 18;
font.Boldweight = 2500;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.BigHeader, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 14;
font.Boldweight = 2000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
//style.FillPattern = FillPatternType.SOLID_FOREGROUND;
styles.Add(TextStyle.Header2, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 14;
font.Boldweight = 2000;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.Header2Simple, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.WHITE.index;
//style.FillPattern = FillPatternType.FINE_DOTS;
//style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
styles.Add(TextStyle.Header3, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.Header3Simple, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.CENTER;
styles.Add(TextStyle.Header3Center, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 12;
font.Boldweight = 1000;
font.FontName = "Arial";
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.WHITE.index;
//style.FillPattern = HSSFCellStyle.LESS_DOTS;
//style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.WrapText = true;
styles.Add(TextStyle.Header3Wrap, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.LEFT;
styles.Add(TextStyle.NormalText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.IsItalic = true;
style.SetFont(font);
style.Alignment = HorizontalAlignment.LEFT;
styles.Add(TextStyle.NormalTextItalic, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.WrapText = true;
styles.Add(TextStyle.TableRowDark, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.TOP;
styles.Add(TextStyle.TableRowDarkCenter, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
DataFormat format = hssfworkbook.CreateDataFormat();
style.DataFormat = format.GetFormat("# ### ### ##0.00");
styles.Add(TextStyle.TableRowDarkMoney, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.WrapText = true;
styles.Add(TextStyle.NormalDarkLeftTopWrap, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.SOLID_FOREGROUND;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.WrapText = true;
styles.Add(TextStyle.TableRowNormal, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.WrapText = true;
styles.Add(TextStyle.WrapText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.SetFont(font);
styles.Add(TextStyle.BoldText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font);
styles.Add(TextStyle.BoldTextAlignCenter, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.NormalAlignCenter, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.NormalLeftTop, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
style.WrapText = true;
styles.Add(TextStyle.NormalLeftTopWrap, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.NormalRightTop, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.Boldweight = 3000;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
styles.Add(TextStyle.BoldRightTop, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.FontHeightInPoints = 10;
font.FontName = "Arial";
//font.Underline
font.Color = HSSFColor.BLUE.index;
style.SetFont(font);
styles.Add(TextStyle.Hyperlink, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font);
styles.Add(TextStyle.TableHeader, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
styles.Add(TextStyle.SysDateTime, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
styles.Add(TextStyle.SysNumber, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
styles.Add(TextStyle.NormalPercent, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
style.SetFont(font);
DataFormat format = hssfworkbook.CreateDataFormat();
style.DataFormat = format.GetFormat("# ### ### ##0.00");
styles.Add(TextStyle.SysMoney, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.Boldweight = 3000;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
styles.Add(TextStyle.TableTotal, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.FontName = "Arial";
font.Boldweight = 3000;
style.Alignment = HorizontalAlignment.RIGHT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.SetFont(font);
style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
styles.Add(TextStyle.TableTotalPercent, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 10;
font.Boldweight = 3000;
font.FontName = "Arial";
style.Alignment = HorizontalAlignment.CENTER;
style.VerticalAlignment = VerticalAlignment.CENTER;
style.BorderTop = CellBorderType.THIN;
style.BorderBottom = CellBorderType.THIN;
style.BorderLeft = CellBorderType.THIN;
style.BorderRight = CellBorderType.THIN;
style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.TopBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.LeftBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.RightBorderColor = HSSFColor.GREY_50_PERCENT.index;
style.FillForegroundColor = HSSFColor.WHITE.index;
style.FillPattern = FillPatternType.FINE_DOTS;
style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;
style.WrapText = true;
style.SetFont(font);
styles.Add(TextStyle.TableHeaderGreyCenterdBorder, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 8;
font.FontName = "Arial";
style.SetFont(font);
styles.Add(TextStyle.SmallText, style);
}
{
Font font = hssfworkbook.CreateFont();
CellStyle style = hssfworkbook.CreateCellStyle();
font.Color = HSSFColor.BLACK.index;
font.FontHeightInPoints = 8;
font.FontName = "Arial";
style.SetFont(font);
style.Alignment = HorizontalAlignment.LEFT;
style.VerticalAlignment = VerticalAlignment.TOP;
style.WrapText = true;
styles.Add(TextStyle.SmallTextWrap, style);
}
return(styles);
}
/// <summary>
/// Create an Excel document with a nice breakdown of Trello.
/// </summary>
/// <returns></returns>
public static byte[] ToExcel()
{
List <Board> _boards = ParseBoardData();
// New workbook
var workbook = new HSSFWorkbook();
foreach (Board _boardInfo in _boards)
{
JavaScriptSerializer _worker = new JavaScriptSerializer();
_worker.MaxJsonLength = int.MaxValue;
File.WriteAllText(HttpContext.Current.Server.MapPath("") + @"\output\" + _boardInfo.name.ToString() + ".json", _worker.Serialize(_boardInfo));
// Create a sheet
var sheet = workbook.CreateSheet(_boardInfo.name.ToString());
// Kill everything not supposed to be in there.
var rowIndex = 0;
#region HEADING STYLE
HSSFFont _headingFont = (HSSFFont)workbook.CreateFont();
_headingFont.Boldweight = (short)FontBoldWeight.Bold;
_headingFont.Color = HSSFColor.Grey80Percent.Index;
//_headingFont.FontHeight = 280;
HSSFCellStyle _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_headingStyle.VerticalAlignment = VerticalAlignment.Center;
_headingStyle.SetFont(_headingFont);
#endregion
// Create the heading
var row = sheet.CreateRow(rowIndex);
row.Height = 350;
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue("Card name");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(1);
cell.SetCellValue("Date created");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(2);
cell.SetCellValue("Pending");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(3);
cell.SetCellValue("In Progress");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(4);
cell.SetCellValue("In Testing");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(5);
cell.SetCellValue("Released");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(6);
cell.SetCellValue("On Hold");
cell.CellStyle = _headingStyle;
cell = (HSSFCell)row.CreateCell(7);
cell.SetCellValue("Description");
cell.CellStyle = _headingStyle;
// Next row
rowIndex++;
#region ROW STYLE
HSSFCellStyle _rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
_rowStyle.VerticalAlignment = VerticalAlignment.Center;
IDataFormat _dataFormat = workbook.CreateDataFormat();
#endregion
if (_boardInfo.Lists != null)
{
foreach (Board.List _listData in _boardInfo.Lists)
{
if (_listData.Cards != null)
{
foreach (Board.List.Card _cardData in _listData.Cards)
{
// Add the row
row = sheet.CreateRow(rowIndex);
row.Height = 350;
// Card name
cell = (HSSFCell)row.CreateCell(0);
cell.SetCellValue(_cardData.name.ToString());
cell.CellStyle = _rowStyle;
// Created
cell = (HSSFCell)row.CreateCell(1);
DateTime _startDate = new DateTime(1970, 01, 01);
cell.SetCellValue(_startDate.AddSeconds(int.Parse(_cardData.id.ToString().Substring(0, 8), System.Globalization.NumberStyles.HexNumber)).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
// Actions
foreach (Board.List.Card.Action _actionData in _cardData.Actions)
{
if (_actionData.data != null)
{
if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("pending"))
{
cell = (HSSFCell)row.CreateCell(2);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("progress"))
{
cell = (HSSFCell)row.CreateCell(3);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("testing"))
{
cell = (HSSFCell)row.CreateCell(4);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("release"))
{
cell = (HSSFCell)row.CreateCell(5);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("hold"))
{
cell = (HSSFCell)row.CreateCell(6);
cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
cell.CellStyle = _rowStyle;
}
}
}
// Description
cell = (HSSFCell)row.CreateCell(7);
cell.SetCellValue(_cardData.desc.ToString());
cell.CellStyle = _rowStyle;
// Next row.
rowIndex++;
}
}
}
// Size it
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
sheet.AutoSizeColumn(3);
sheet.AutoSizeColumn(4);
sheet.AutoSizeColumn(5);
sheet.AutoSizeColumn(6);
sheet.AutoSizeColumn(7);
sheet.CreateFreezePane(0, 1);
}
}
#region OUTPUT
// Save the Excel spreadsheet to a MemoryStream and return it to the client
using (var exportData = new MemoryStream()) {
workbook.Write(exportData);
return(exportData.GetBuffer());
}
#endregion
}
/// <summary>
/// 导出list数据到Excel
/// </summary>
/// <typeparam name="T">实体</typeparam>
/// <param name="exportDatas">导出的list数据</param>
/// <param name="templateModels">Excel模版信息</param>
/// <param name="title">Sheet名称</param>
/// <param name="multiHeaderInfos">多表头定义信息</param>
/// <returns></returns>
public static HSSFWorkbook Export <T>(List <T> exportDatas, List <ExcelTemplate> templateModels, string title, List <List <MultiHeaderInfo> > multiHeaderInfos = null)
{
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++;
}
int rowIndex = 0;
foreach (var exportData in exportDatas)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
if (multiHeaderInfos != null && multiHeaderInfos.Count > 0) // 复杂表头合并等
{
List <int>[] usedCellIndexs = new List <int> [multiHeaderInfos.Count];
for (var i = 0; i < multiHeaderInfos.Count; i++)
{
usedCellIndexs[i] = new List <int>();
}
for (var i = 0; i < multiHeaderInfos.Count; i++)
{
var colIndex = 0;
var headerRow = sheet.CreateRow(i);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
headStyle.VerticalAlignment = VerticalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
foreach (var multiHeaderInfo in multiHeaderInfos[i])
{
while (true) // 找未使用的第一个单元格
{
if (!usedCellIndexs[i].Contains(colIndex))
{
break;
}
colIndex++;
}
headerRow.CreateCell(colIndex).SetCellValue(multiHeaderInfo.Name);
var oldColIndex = colIndex;
if (multiHeaderInfo.ColSpan > 1 || multiHeaderInfo.RowSpan > 1)
{
sheet.AddMergedRegion(new CellRangeAddress(i, i + multiHeaderInfo.RowSpan - 1, colIndex, colIndex + multiHeaderInfo.ColSpan - 1));
if (multiHeaderInfo.RowSpan > 1)
{
for (var j = 1; j < multiHeaderInfo.RowSpan; j++)
{
for (var k = colIndex; k < colIndex + multiHeaderInfo.ColSpan; k++)
{
usedCellIndexs[i + j].Add(k);
}
}
}
colIndex = colIndex + multiHeaderInfo.ColSpan;
}
else
{
colIndex++;
}
headerRow.GetCell(oldColIndex).CellStyle = headStyle;
}
}
rowIndex = multiHeaderInfos.Count;
}
else
{
#region 列头及样式
{
var headerRow = sheet.CreateRow(0);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.IsBold = true;
headStyle.SetFont(font);
columnIndex = 0;
foreach (var templateModel in templateModels)
{
headerRow.CreateCell(columnIndex).SetCellValue(templateModel.Name);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
columnIndex++;
}
}
#endregion
rowIndex = 1;
}
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
columnIndex = 0;
foreach (var templateModel in templateModels)
{
var newCell = dataRow.CreateCell(columnIndex);
var objValue = typeof(T).GetProperty(templateModel.Field).GetValue(exportData)?.ToString();
switch (templateModel.FieldType)
{
case EFieldType.Int:
int intV = 0;
int.TryParse(objValue, out intV);
newCell.SetCellValue(intV);
break;
case EFieldType.Double:
double doubV = 0;
double.TryParse(objValue, out doubV);
newCell.SetCellValue(doubV);
break;
case EFieldType.Guid:
newCell.SetCellValue(objValue);
break;
case EFieldType.Bool:
bool boolV = false;
bool.TryParse(objValue, out boolV);
newCell.SetCellValue(boolV);
break;
case EFieldType.Date:
DateTime.TryParse(objValue, out var dateV);
if (dateV != DateTime.MinValue)
{
newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));
}
else
{
newCell.SetCellValue("");
}
break;
case EFieldType.DateTime:
DateTime.TryParse(objValue, out var datetimeV);
if (datetimeV != DateTime.MinValue)
{
newCell.SetCellValue(datetimeV.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
newCell.SetCellValue("");
}
//newCell.SetCellType(CellType.String);
//newCell.SetCellValue(datetimeV.ToString("yyyy-MM-dd HH:mm:ss"));
break;
case EFieldType.String:
newCell.SetCellValue(objValue);
break;
default:
newCell.SetCellValue(objValue);
break;
}
columnIndex++;
}
#endregion
rowIndex++;
}
for (int i = 0; i < templateModels.Count; i++)
{
sheet.AutoSizeColumn(i);
}
return(workbook);
}
/// <summary>
/// 每日个签送钱客人情况表
/// </summary>
/// <param name="visaList"></param>
/// <param name="visaInfoList"></param>
/// <returns></returns>
public static bool GetEverydayExcel(List <Model.Visa> visaList, List <List <VisaInfo> > visaInfoList)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("每日送签客人情况");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("");
row.CreateCell(1).SetCellValue("姓名");
row.CreateCell(2).SetCellValue("签发地");
row.CreateCell(3).SetCellValue("居住地");
row.CreateCell(4).SetCellValue("签证类型");
row.CreateCell(5).SetCellValue("归国时间");
row.CreateCell(6).SetCellValue("关系");
row.CreateCell(7).SetCellValue("");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 10 * 256);
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 10 * 256);
sheet.SetColumnWidth(5, 13 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 35 * 256);
//3.插入行和单元格
int rowNum = 0;
for (int i = 0; i != visaList.Count; ++i)
{
for (int j = 0; j < visaInfoList[i].Count; j++)
{
++rowNum;
row = sheet.CreateRow(rowNum);
row.CreateCell(0).SetCellValue(rowNum);
row.CreateCell(1).SetCellValue(visaInfoList[i][j].Name);
row.CreateCell(2).SetCellValue(visaInfoList[i][j].IssuePlace);
string residence = visaInfoList[i][j].Residence;
if (visaInfoList[i][j].Residence.Contains(" "))
{
residence = visaInfoList[i][j].Residence.Split(' ')[0];
if (residence.EndsWith("省") || residence.EndsWith("市"))
{
residence = residence.Substring(0, residence.Length - 1);
}
}
row.CreateCell(3).SetCellValue(residence);
row.CreateCell(4).SetCellValue(visaList[i].DepartureType);
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(visaInfoList[i][j].ReturnTime)); //归国时间先不设置
row.CreateCell(6).SetCellValue(visaList[i].Remark);
row.CreateCell(7).SetCellValue(visaInfoList[i][j].Identification);
}
//创建单元格
//设置行高
//row.HeightInPoints = 50;
//设置值
}
HSSFFont font = (HSSFFont)wkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.SetFont(font);
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
//4.2合并单元格
int dp = 1;
for (int i = 0; i != visaList.Count; ++i)
{
sheet.AddMergedRegion(new CellRangeAddress(dp, dp + visaInfoList[i].Count - 1, 6, 6));
//单独处理合并区域的单元格格式
ICellStyle mergeStyle = wkbook.CreateCellStyle();
mergeStyle.SetFont(font);
mergeStyle.VerticalAlignment = VerticalAlignment.Center;
mergeStyle.Alignment = HorizontalAlignment.Center;
mergeStyle.BorderTop = BorderStyle.Thin;
mergeStyle.BorderBottom = BorderStyle.Thin;
mergeStyle.BorderLeft = BorderStyle.Thin;
mergeStyle.BorderRight = BorderStyle.Thin;
sheet.GetRow(dp).Cells[6].CellStyle = mergeStyle;
dp += visaInfoList[i].Count;
}
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg("每日送签客人情况表.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
/// <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);
}
/// <summary>
/// 导出数据到excel文件
/// </summary>
/// <param name="titleList">特殊处理的数据的标题集合</param>
/// <returns></returns>
public Stream CommonToExcel(List <string> titleList)
{
int rowIndex = 0;
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow row = sheet.CreateRow(rowIndex);
row.Height = 200 * 3;
//表头样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;//居中对齐
//表头单元格背景色
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//表头单元格边框
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.VerticalAlignment = VerticalAlignment.Center;
//表头字体设置
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12; //字号
font.Boldweight = 600; //加粗
//font.Color = HSSFColor.WHITE.index;//颜色
style.SetFont(font);
//数据样式
ICellStyle datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Left;//左对齐
//数据单元格的边框
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.TopBorderColor = HSSFColor.Black.Index;
datastyle.RightBorderColor = HSSFColor.Black.Index;
datastyle.BottomBorderColor = HSSFColor.Black.Index;
datastyle.LeftBorderColor = HSSFColor.Black.Index;
//数据的字体
IFont datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
datastyle.SetFont(datafont);
//设置列宽
for (int i = 0; i < columsWidth.Length; i++)
{
//sheet.SetColumnWidth(i, columsWidth[i] * 256);
sheet.SetColumnWidth(i, columsWidth[i] * 58);
}
sheet.DisplayGridlines = false;
try {
//表头数据
for (int i = 0; i < titles.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(titles[i]);
cell.CellStyle = style;
}
for (int k = 0; k < data.Count; k++)
{
row = sheet.CreateRow(k + 1);
row.Height = 200 * 2;
T t = data[k];
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
for (int j = 0; j < colums.Length; j++)
{
var temp = propertys.ToList().Where(p => p.Name == colums[j]).Single();
ICell cell = row.CreateCell(j);
var value = temp.GetValue(t, null);
if (value == null)
{
value = "";
}
else
{
if (value.GetType() == Type.GetType("System.DateTime"))
{
if (titleList != null && titleList.Contains(colums[j]))
{
value = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd HH:mm");
}
else
{
value = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd");
}
}
}
cell.SetCellValue(value.ToString());
cell.CellStyle = datastyle;
}
}
} catch (Exception ex) {
} finally {
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
}
return(ms);
}
// Token: 0x06000042 RID: 66 RVA: 0x000064B8 File Offset: 0x000046B8
protected override void View()
{
this.examinfo = DbHelper.ExecuteModel <ExamInfo>(this.examid);
if (this.examinfo.id == 0)
{
this.ShowErr("对不起，该试卷不存在或已被删除。");
}
else
{
this.sortid = this.examinfo.sortid;
this.sortinfo = SortBll.GetSortInfo(this.sortid);
if (this.ispost)
{
if (this.action == "delete")
{
string @string = FPRequest.GetString("chkid");
if (DbHelper.ExecuteDelete <ExamResult>(@string) > 0)
{
SqlParam sqlParam = DbHelper.MakeAndWhere("resultid", WhereType.In, @string);
DbHelper.ExecuteDelete <ExamResultTopic>(new SqlParam[]
{
sqlParam
});
}
}
}
if (this.examinfo.examdeparts == "" && this.examinfo.examuser == "" && this.examinfo.examroles == "")
{
List <SqlParam> list = new List <SqlParam>();
list.Add(DbHelper.MakeAndWhere("examid", this.examid));
if (this.keyword != "")
{
string text = "0";
SqlParam sqlParam2 = DbHelper.MakeAndWhere(string.Format("([username] LIKE '%{0}%' OR [realname] LIKE '%{0}%')", this.keyword), WhereType.Custom, "");
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
list.Add(DbHelper.MakeAndWhere("uid", WhereType.In, text));
}
if (this.action == "export")
{
OrderByParam[] orderbys = new OrderByParam[]
{
DbHelper.MakeOrderBy("score", OrderBy.DESC),
DbHelper.MakeOrderBy("id", OrderBy.ASC)
};
this.examresultlist = DbHelper.ExecuteList <ExamResult>(orderbys, list.ToArray());
}
else
{
this.examresultlist = DbHelper.ExecuteList <ExamResult>(this.pager, list.ToArray());
}
}
else
{
string text = "";
if (this.examinfo.examroles != "")
{
SqlParam sqlParam2 = DbHelper.MakeAndWhere("roleid", WhereType.In, this.examinfo.examroles);
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (!FPUtils.InArray(userInfo.id, text))
{
ExamResult examResult = new ExamResult();
examResult.uid = userInfo.id;
examResult.examid = this.examid;
examResult.status = -1;
this.examresultlist.Add(examResult);
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
}
}
if (this.examinfo.examdeparts != "")
{
SqlParam sqlParam2 = DbHelper.MakeAndWhere("departid", WhereType.In, this.examinfo.examdeparts);
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (!FPUtils.InArray(userInfo.id, text))
{
ExamResult examResult = new ExamResult();
examResult.uid = userInfo.id;
examResult.examid = this.examid;
examResult.status = -1;
this.examresultlist.Add(examResult);
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
}
}
if (this.examinfo.examuser != "")
{
SqlParam sqlParam2 = DbHelper.MakeAndWhere("id", WhereType.In, this.examinfo.examuser);
List <UserInfo> list2 = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
{
sqlParam2
});
foreach (UserInfo userInfo in list2)
{
if (!FPUtils.InArray(userInfo.id, text))
{
ExamResult examResult = new ExamResult();
examResult.uid = userInfo.id;
examResult.examid = this.examid;
examResult.status = -1;
this.examresultlist.Add(examResult);
if (text != "")
{
text += ",";
}
text += userInfo.id;
}
}
}
SqlParam sqlParam3 = DbHelper.MakeAndWhere("examid", this.examid);
OrderByParam orderby = DbHelper.MakeOrderBy("id", OrderBy.ASC);
List <ExamResult> list3 = DbHelper.ExecuteList <ExamResult>(orderby, new SqlParam[]
{
sqlParam3
});
int num = 0;
foreach (ExamResult examResult2 in this.examresultlist)
{
foreach (ExamResult examResult3 in list3)
{
if (examResult3.uid == examResult2.uid)
{
this.examresultlist[num].id = examResult3.id;
this.examresultlist[num].score = examResult3.score;
this.examresultlist[num].starttime = examResult3.starttime;
this.examresultlist[num].examdatetime = examResult3.examdatetime;
this.examresultlist[num].utime = examResult3.utime;
this.examresultlist[num].status = examResult3.status;
this.examresultlist[num].questions++;
this.examresultlist[num].ip = examResult3.ip;
}
}
num++;
}
if (this.keyword != "")
{
list3 = new List <ExamResult>();
foreach (ExamResult examResult2 in this.examresultlist)
{
if (examResult2.IUser.username.Contains(this.keyword) || examResult2.IUser.realname.Contains(this.keyword))
{
list3.Add(examResult2);
}
}
this.examresultlist = new List <ExamResult>();
foreach (ExamResult examResult2 in list3)
{
this.examresultlist.Add(examResult2);
}
}
if (this.action != "export" && this.action != "report")
{
this.pager.total = this.examresultlist.Count;
int num2 = (this.pager.pageindex - 1) * this.pager.pagesize;
int count = this.pager.pagesize;
if (num2 + this.pager.pagesize > this.pager.total)
{
count = this.pager.total - num2;
}
this.examresultlist = this.examresultlist.GetRange(num2, count);
}
}
if (this.ispost)
{
if (this.action == "export")
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
HSSFSheet hssfsheet = hssfworkbook.CreateSheet("Sheet1");
HSSFCellStyle hssfcellStyle = hssfworkbook.CreateCellStyle();
hssfcellStyle.Alignment = CellHorizontalAlignment.CENTER;
hssfcellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfcellStyle.BorderTop = CellBorderType.THIN;
hssfcellStyle.BorderRight = CellBorderType.THIN;
hssfcellStyle.BorderLeft = CellBorderType.THIN;
hssfcellStyle.BorderBottom = CellBorderType.THIN;
hssfcellStyle.DataFormat = 0;
HSSFFont hssffont = hssfworkbook.CreateFont();
hssffont.Boldweight = short.MaxValue;
hssfcellStyle.SetFont(hssffont);
HSSFRow hssfrow = hssfsheet.CreateRow(0);
hssfrow.CreateCell(0).SetCellValue("用户名");
hssfrow.CreateCell(1).SetCellValue("姓名");
hssfrow.CreateCell(2).SetCellValue("所在部门");
hssfrow.CreateCell(3).SetCellValue("考试得分");
hssfrow.CreateCell(4).SetCellValue("开始时间");
hssfrow.CreateCell(5).SetCellValue("考试用时");
hssfrow.CreateCell(6).SetCellValue("考试状态");
hssfrow.CreateCell(7).SetCellValue("");
hssfrow.Height = 400;
hssfsheet.SetColumnWidth(2, 6000);
hssfsheet.SetColumnWidth(4, 6000);
for (int i = 0; i < 7; i++)
{
hssfrow.Cells[i].CellStyle = hssfcellStyle;
}
HSSFCellStyle hssfcellStyle2 = hssfworkbook.CreateCellStyle();
hssfcellStyle2.Alignment = CellHorizontalAlignment.CENTER;
hssfcellStyle2.VerticalAlignment = CellVerticalAlignment.CENTER;
hssfcellStyle2.BorderTop = CellBorderType.THIN;
hssfcellStyle2.BorderRight = CellBorderType.THIN;
hssfcellStyle2.BorderLeft = CellBorderType.THIN;
hssfcellStyle2.BorderBottom = CellBorderType.THIN;
hssfcellStyle2.DataFormat = 0;
int num3 = 1;
foreach (ExamResult examResult2 in this.examresultlist)
{
HSSFRow hssfrow2 = hssfsheet.CreateRow(num3);
hssfrow2.Height = 300;
hssfrow2.CreateCell(0).SetCellValue(examResult2.IUser.username);
hssfrow2.CreateCell(1).SetCellValue(examResult2.IUser.realname);
hssfrow2.CreateCell(2).SetCellValue(examResult2.IUser.Department.name);
hssfrow2.CreateCell(3).SetCellValue(examResult2.score.ToString());
if (examResult2.status >= 0)
{
hssfrow2.CreateCell(4).SetCellValue(examResult2.examdatetime.ToString("yyyy-MM-dd HH:mm:dd"));
hssfrow2.CreateCell(5).SetCellValue((examResult2.utime / 60 + 1).ToString() + "分钟");
}
else
{
hssfrow2.CreateCell(4).SetCellValue("");
hssfrow2.CreateCell(5).SetCellValue("");
}
if (examResult2.status == 1)
{
hssfrow2.CreateCell(6).SetCellValue("已交卷");
}
else if (examResult2.status == 2)
{
hssfrow2.CreateCell(6).SetCellValue("已阅卷");
}
else if (examResult2.status == 0)
{
hssfrow2.CreateCell(6).SetCellValue("未交卷");
}
else
{
hssfrow2.CreateCell(6).SetCellValue("缺考");
}
hssfrow2.CreateCell(7).SetCellValue("");
for (int i = 0; i < 7; i++)
{
hssfrow2.Cells[i].CellStyle = hssfcellStyle2;
}
num3++;
}
using (MemoryStream memoryStream = new MemoryStream())
{
hssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0L;
hssfsheet.Dispose();
hssfworkbook.Dispose();
base.Response.ContentType = "application/vnd.ms-excel";
base.Response.ContentEncoding = Encoding.UTF8;
base.Response.Charset = "";
base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(this.examinfo.name + "成绩表.xls"));
base.Response.BinaryWrite(memoryStream.GetBuffer());
base.Response.Flush();
base.Response.End();
}
}
else if (this.action == "report")
{
AsposeWordApp asposeWordApp = new AsposeWordApp();
asposeWordApp.Open(FPUtils.GetMapPath("images\\examreport.doc"));
asposeWordApp.InsertText("examtitle", this.examinfo.name);
asposeWordApp.InsertText("username", this.user.realname);
asposeWordApp.InsertText("total", this.examinfo.total.ToString() + "分");
if (this.examinfo.islimit == 1)
{
asposeWordApp.InsertText("examtime", this.examinfo.starttime.ToString("yyyy-MM-dd HH:mm"));
}
else
{
asposeWordApp.InsertText("examtime", "不限制");
}
asposeWordApp.InsertText("exampass", (this.examinfo.passmark * this.examinfo.total / 100.0).ToString() + "分");
asposeWordApp.InsertText("qtime", this.examinfo.examtime.ToString() + "分钟");
asposeWordApp.InsertText("examuser", this.examinfo.exams.ToString() + "人");
if (this.examinfo.exams > 0)
{
asposeWordApp.InsertText("examavg", (this.examinfo.score / (double)this.examinfo.exams).ToString("0.0"));
}
else
{
asposeWordApp.InsertText("examavg", "0");
}
int[] array = new int[5];
foreach (ExamResult examResult2 in this.examresultlist)
{
if (examResult2.score < 60.0)
{
array[0]++;
}
else if (examResult2.score >= 60.0 && examResult2.score < 70.0)
{
array[1]++;
}
else if (examResult2.score >= 70.0 && examResult2.score < 80.0)
{
array[2]++;
}
else if (examResult2.score >= 80.0 && examResult2.score < 90.0)
{
array[3]++;
}
else if (examResult2.score >= 90.0)
{
array[4]++;
}
}
int i = 1;
foreach (int num4 in array)
{
asposeWordApp.InsertText("s" + i, num4.ToString() + "人");
asposeWordApp.InsertText("p" + i, (num4 / this.examinfo.exams * 100).ToString("0.0") + "%");
i++;
}
asposeWordApp.Save(base.Response, this.examinfo.name + "_考试分析报告.doc");
}
}
base.SaveRightURL();
}
}
public void TestOptimiseStyles()
{
HSSFWorkbook wb = new HSSFWorkbook();
// Two fonts
Assert.AreEqual(4, wb.NumberOfFonts);
IFont f1 = wb.CreateFont();
f1.FontHeight = ((short)11);
f1.FontName = ("Testing");
IFont f2 = wb.CreateFont();
f2.FontHeight = ((short)22);
f2.FontName = ("Also Testing");
Assert.AreEqual(6, wb.NumberOfFonts);
// Several styles
Assert.AreEqual(21, wb.NumCellStyles);
Npoi.Core.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
cs1.SetFont(f1);
Npoi.Core.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
cs2.SetFont(f2);
Npoi.Core.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
cs3.SetFont(f1);
Npoi.Core.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
cs4.SetFont(f1);
cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22);
Npoi.Core.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
cs5.SetFont(f2);
cs5.Alignment = HorizontalAlignment.Fill; //((short)111);
Npoi.Core.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
cs6.SetFont(f2);
Assert.AreEqual(27, wb.NumCellStyles);
// Use them
Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
IRow r = s.CreateRow(0);
r.CreateCell(0).CellStyle = (cs1);
r.CreateCell(1).CellStyle = (cs2);
r.CreateCell(2).CellStyle = (cs3);
r.CreateCell(3).CellStyle = (cs4);
r.CreateCell(4).CellStyle = (cs5);
r.CreateCell(5).CellStyle = (cs6);
r.CreateCell(6).CellStyle = (cs1);
r.CreateCell(7).CellStyle = (cs2);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// Optimise
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(6, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
// cs3 = cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
// cs4 --> 24 -> 23
Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
// cs5 --> 25 -> 24
Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
// cs6 = cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
// cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
// cs2 -> 22
Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);
// Add a new duplicate, and two that aren't used
HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();
csD.SetFont(f1);
r.CreateCell(8).CellStyle = (csD);
HSSFFont f3 = (HSSFFont)wb.CreateFont();
f3.FontHeight = ((short)23);
f3.FontName = ("Testing 3");
HSSFFont f4 = (HSSFFont)wb.CreateFont();
f4.FontHeight = ((short)24);
f4.FontName = ("Testing 4");
HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();
csU1.SetFont(f3);
HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();
csU2.SetFont(f4);
// Check before the optimise
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(28, wb.NumCellStyles);
// Optimise, should remove the two un-used ones and the one duplicate
HSSFOptimiser.OptimiseCellStyles(wb);
// Check
Assert.AreEqual(8, wb.NumberOfFonts);
Assert.AreEqual(25, wb.NumCellStyles);
// csD -> cs1 -> 21
Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
}
static void Main(string[] args)
{
InitializeWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = hssfworkbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = hssfworkbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = hssfworkbook.CreateFont();
font2.Color = HSSFColor.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = hssfworkbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = CellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");
cell1.CellStyle = style1;
ICell cell2 = CellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好！");
cell2.CellStyle = style2;
//cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = hssfworkbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = hssfworkbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = HSSFColor.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
WriteToFile();
}
/// <summary>
/// 生成Excel
/// </summary>
/// <returns>生成的Excel文件</returns>
public virtual byte[] GenerateExcel()
{
NeedPage = false;
if (GridHeaders == null)
{
GetHeaders();
}
if (IsSearched == false)
{
DoSearch();
}
HSSFWorkbook workbook = new HSSFWorkbook();
List <HSSFSheet> sheets = new List <NPOI.HSSF.UserModel.HSSFSheet>();
//准备好sheet，每6万行生成一个sheet
var sheetno = ((EntityList.Count - 1) / 60000) + 1;
var headerStyle = workbook.CreateCellStyle();
//设定单元格边框
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
//用灰色填充背景
var headerbg = HSSFColor.Grey25Percent.Index;
headerStyle.FillForegroundColor = headerbg;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.FillBackgroundColor = headerbg;
//去掉 Id 列和动作列
RemoveActionAndIdColumn();
//循环生成所有sheet，并为每个sheet添加表头
var headerrows = 0;
for (int i = 1; i <= sheetno; i++)
{
HSSFSheet sheet = workbook.CreateSheet("Sheet" + i) as HSSFSheet;
//生成表头
headerrows = MakeExcelHeader(sheet, GridHeaders, 0, 0, headerStyle);
sheets.Add(sheet);
}
var rowIndex = headerrows;
var colIndex = 0;
//Excel中用到的style，每种前景色和背景色的组合为一个style。Nopi值支持4000个style，所以同样的style要重复使用
Dictionary <string, ICellStyle> styles = new Dictionary <string, ICellStyle>();
//Excel中用到的font，主要是为了更改字体颜色
Dictionary <string, IFont> fonts = new Dictionary <string, IFont>();
//循环数据
foreach (var row in EntityList)
{
var sheetindex = ((rowIndex - headerrows) / 60000);
colIndex = 0;
string bgColor = "";
string fColor = "";
//获取设定的行背景色
bgColor = SetFullRowBgColor(row);
//获取设定的行前景色
fColor = SetFullRowColor(row);
var dr = sheets[sheetindex].CreateRow(rowIndex - sheetindex * 60000) as HSSFRow;
foreach (var baseCol in GridHeaders)
{
//处理枚举变量的多语言
bool IsEmunBoolParp = false;
var proType = baseCol.FieldType;
if (proType.IsEnumOrNullableEnum())
{
IsEmunBoolParp = true;
}
foreach (var col in baseCol.BottomChildren)
{
//获取数据，并过滤特殊字符
string text = Regex.Replace(col.GetText(row).ToString(), @"<[^>]*>", String.Empty);
//处理枚举变量的多语言
if (IsEmunBoolParp)
{
if (int.TryParse(text, out int enumvalue))
{
text = PropertyHelper.GetEnumDisplayName(proType, enumvalue);
}
}
//建立excel单元格
var cell = dr.CreateCell(colIndex);
ICellStyle style = null;
IFont font = null;
var styleKey = string.Empty;
//获取设定的单元格背景色
string backColor = col.GetBackGroundColor(row);
//获取设定的单元格前景色
string foreColor = col.GetForeGroundColor(row);
//如果行背景色或单元格背景色有值，则用颜色的ARGB的值作为style的key
if (bgColor != "" || backColor != "")
{
styleKey = backColor == "" ? bgColor : backColor;
}
//如果行前景色或单元格前景色有值，则用颜色的ARGB加上背景色的ARGB作为style的key
if (fColor != "" || foreColor != "")
{
styleKey += foreColor == "" ? foreColor : fColor;
}
//如果已经有符合条件的style，则使用
if (styles.ContainsKey(styleKey))
{
style = styles[styleKey];
}
//如果没有，则新建一个style
else
{
var newKey = "";
var newFontKey = "";
//新建style
style = workbook.CreateCellStyle();
//设定单元格边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
//如果行前景色或单元格前景色有值，则设定单元格的填充颜色
if (bgColor != "" || backColor != "")
{
newKey = backColor == "" ? bgColor : backColor;
var ci = Utils.GetExcelColor(backColor == "" ? bgColor : backColor);
style.FillForegroundColor = ci;
style.FillPattern = FillPattern.SolidForeground;
style.FillBackgroundColor = ci;
}
//如果行前景色或单元格前景色有值，则设定单元格的字体的颜色
if (fColor != "" || foreColor != "")
{
newFontKey = foreColor == "" ? foreColor : fColor;
newKey += foreColor == "" ? foreColor : fColor;
//如果已经有符合条件的字体，则使用
if (fonts.ContainsKey(newFontKey))
{
font = fonts[newFontKey];
}
//如果没有，则新建
else
{
//新建字体
font = workbook.CreateFont();
//设定字体颜色
font.Color = Utils.GetExcelColor(foreColor == "" ? fColor : foreColor);
//向集合中添加新字体
fonts.Add(newFontKey, font);
}
//设定style中的字体
style.SetFont(font);
}
//将新建的style添加到集合中
styles.Add(newKey, style);
}
cell.CellStyle = style;
cell.SetCellValue(text);
colIndex++;
}
}
rowIndex++;
}
//获取Excel文件的二进制数据
byte[] rv = new byte[] { };
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
rv = ms.ToArray();
}
return(rv);
}
/// <summary>
/// 大数据量多个sheet导出
/// </summary>
/// <typeparam name="T">数据源实体类</typeparam>
/// <param name="objList">数据源</param>
/// <param name="fileName">文件名称</param>
/// <param name="btyBytes">导出数据流</param>
/// <param name="columnInfo">显示列对应数据字典</param>
/// <param name="listCount">每个sheet包含数据条数</param>
/// <returns></returns>
public void ExportExcelTest <T>(List <T> objList, string fileName, Dictionary <string, string> columnInfo = null)
{
//在内存中生成一个Excel文件：
IWorkbook book = new HSSFWorkbook();
if (objList != null && objList.Count > 0)
{
var sheetName = new string[] { "日租", "月租", "季度租", "半年租", "年租" };
double sheetCount = sheetName.Count();
for (int i = 0; i < sheetCount - 1; i++)
{
ISheet sheet = book.CreateSheet(sheetName[i]);
List <T> list = objList.ToList();
int rowIndex = 0;
Type myType = objList[0].GetType();
//创建表头样式
ICellStyle style = book.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontName = "微软雅黑";
style.SetFont(font);//HEAD 样式
//根据反射从传递进来的属性名信息得到要显示的属性
List <PropertyInfo> myPro = new List <PropertyInfo>();
#region 定义表头
int m = 0;
if (columnInfo != null)
{
var rowheader = sheet.CreateRow(0);
rowheader.Height = 20 * 20;
foreach (string cName in columnInfo.Keys)
{
PropertyInfo p = myType.GetProperty(cName);
if (p != null)
{
myPro.Add(p);
rowheader.CreateCell(m).SetCellValue(columnInfo[cName]);
m++;
}
}
}
#endregion
#region 定义表体并赋值
//如果没有找到可用的属性则结束
if (myPro.Count == 0)
{
return;
}
foreach (T obj in list)
{
int n = 0;
if (sheet != null)
{
rowIndex++;
var sheetrow = sheet.CreateRow(rowIndex);
sheetrow.Height = 20 * 20;
foreach (PropertyInfo p in myPro)
{
dynamic val = p.GetValue(obj, null) ?? "";
string valtype = val.GetType().ToString();
if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1)
{
val = Convert.ToDouble(val);
}
sheetrow.CreateCell(n).SetCellValue(val);
n++;
}
}
}
#endregion
}
}
else
{
//在工作薄中建立工作表
XSSFSheet sheet = book.CreateSheet() as XSSFSheet;
sheet.SetColumnWidth(0, 30 * 256);
if (sheet != null)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据！");
}
}
if (File.Exists(fileName))
{
//存在
File.Delete(fileName);
}
var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
fs.Flush();
book.Write(fs);
fs.Close();
}
public async Task <ActionResult> ExportToExcel(MaterialReceiptDetailQueryViewModel model)
{
IList <MaterialReceiptDetail> lst = new List <MaterialReceiptDetail>();
using (MaterialReceiptServiceClient client = new MaterialReceiptServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime Desc,Key.ReceiptNo,Key.ItemNo",
Where = GetWhereCondition(model)
};
MethodReturnResult <IList <MaterialReceiptDetail> > result = client.GetDetail(ref cfg);
if (result.Code == 0)
{
lst = result.Data;
}
});
}
//创建工作薄。
IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ICell cell = null;
IRow row = null;
ISheet ws = null;
for (int j = 0; j < lst.Count; j++)
{
if (j % 65535 == 0)
{
ws = wb.CreateSheet();
row = ws.CreateRow(0);
#region //列名
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptDate); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("物料名称"); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("供应商名称"); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("描述"); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑人"); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑时间"); //编辑时间
#endregion
font.Boldweight = 5;
}
MaterialReceiptDetail obj = lst[j];
MaterialReceipt mrObj = model.GetMaterialReceipt(obj.Key.ReceiptNo);
Material m = model.GetMaterial(obj.MaterialCode);
Supplier s = model.GetSupplier(obj.SupplierCode);
row = ws.CreateRow(j + 1);
#region //数据
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : mrObj.OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", mrObj.ReceiptDate)); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(m == null ? string.Empty : m.Name); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Description); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Editor); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "MaterialReceiptData.xls"));
}
/// <summary>
///從datatable中導出到excel
/// </summary>
/// <param name="strFileName"> excel文件名</param>
/// <param name="dtSource"> datatabe源數據</param>
/// <param name="strHeaderText">表名</param>
/// <param name="sheetnum"> sheet的編號</param>
/// <returns></returns>
static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary <string, string> dir, int sheetnum)
{
//創建工作簿和sheet
IWorkbook workbook = new HSSFWorkbook();
using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
{
if (writefile.Length > 0 && sheetnum > 0)
{
workbook = WorkbookFactory.Create(writefile);
}
}
ISheet sheet = null;
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat(" yyyy-mm-dd ");
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表頭，填充列頭，樣式
if (rowIndex == 0)
{
string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
if (workbook.GetSheetIndex(sheetName) >= 0)
{
workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
}
sheet = workbook.CreateSheet(sheetName);
#region 表頭及樣式
{
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
rowIndex = 1;
}
#endregion
#region 列頭及樣式
if (rowIndex == 1)
{
IRow headerRow = sheet.CreateRow(1); //第二行設置列名
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
//寫入列標題
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//設置列寬
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
}
rowIndex = 2;
}
#endregion
}
#endregion
#region 填充內容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case " System.String ": //字符串類型
double result;
if (isNumeric(drValue, out result))
{
//數字字符串
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case " System.DateTime ": //日期類型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化顯示
break;
case " System.Boolean ": //布爾型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case " System.Int16 ": //整型
case " System.Int32 ":
case " System.Int64 ":
case " System.Byte ":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case " System.Decimal ": //浮點型
case " System.Double ":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case " System.DBNull ": //空值處理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue(drValue.ToString());
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public string ExportList <T>(T model,
string fileName,
string userId = "",
string account = "",
bool exportPdf = false)
{
try
{
#region write dictionary
var dicValue = new Dictionary <string, string>();
var dicValueProperty = new Dictionary <string, string>();
var properties = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
properties.ForEach(item =>
{
var attribute = (DescriptionAttribute)item.GetCustomAttribute(typeof(DescriptionAttribute), false);
if (attribute != null)
{
var des = attribute.Description.Split('!');
if (des.Length > 2)
{
if (!dicValue.ContainsKey(des[0]) && !dicValueProperty.ContainsKey(des[0]))
{
dicValue.Add(des[0], des[1]);
dicValueProperty.Add(des[0], des[2]);
}
}
}
});
#endregion write dictionary
#region userPath
var userPath = GetExportFilePath(fileName);
if (userPath.Equals(string.Empty))
{
return(string.Empty);
}
#endregion
#region create hssfWorkbook
HSSFWorkbook hssfWorkbook;
using (var file = new FileStream(userPath, FileMode.Open, FileAccess.Read))
{
hssfWorkbook = new HSSFWorkbook(file);
}
#endregion create hssfWorkbook
#region cellstyle
var cellstyle = hssfWorkbook.CreateCellStyle();
var format = hssfWorkbook.CreateDataFormat();
cellstyle.DataFormat = format.GetFormat("@");
cellstyle.BorderBottom = BorderStyle.Thin;
cellstyle.BorderTop = BorderStyle.Thin;
cellstyle.BorderLeft = BorderStyle.Thin;
cellstyle.BorderRight = BorderStyle.Thin;
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.FillBackgroundColor = HSSFColor.Red.Index;
cellstyle.WrapText = true;
var font = hssfWorkbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Normal;
font.FontHeightInPoints = 11;
cellstyle.SetFont(font);
var contentstyle = hssfWorkbook.CreateCellStyle();
contentstyle.DataFormat = format.GetFormat("@");
contentstyle.BorderBottom = BorderStyle.Thin;
contentstyle.BorderTop = BorderStyle.Thin;
contentstyle.BorderLeft = BorderStyle.Thin;
contentstyle.BorderRight = BorderStyle.Thin;
contentstyle.VerticalAlignment = VerticalAlignment.Center;
contentstyle.FillBackgroundColor = HSSFColor.Red.Index;
contentstyle.WrapText = true;
var contentFont = hssfWorkbook.CreateFont();
contentFont.Boldweight = (short)FontBoldWeight.Normal;
contentFont.FontHeightInPoints = 9;
contentstyle.SetFont(contentFont);
var titlecellstyle = hssfWorkbook.CreateCellStyle();
titlecellstyle.BorderBottom = BorderStyle.Thin;
titlecellstyle.BorderTop = BorderStyle.Thin;
titlecellstyle.BorderLeft = BorderStyle.Thin;
titlecellstyle.BorderRight = BorderStyle.Thin;
titlecellstyle.VerticalAlignment = VerticalAlignment.Center;
titlecellstyle.FillBackgroundColor = HSSFColor.Red.Index;
titlecellstyle.WrapText = true;
var titlefont = hssfWorkbook.CreateFont();
titlefont.Boldweight = (short)FontBoldWeight.Bold;
titlefont.FontHeightInPoints = 11;
titlecellstyle.SetFont(titlefont);
var maintitlecellstyle = hssfWorkbook.CreateCellStyle();
maintitlecellstyle.BorderBottom = BorderStyle.None;
maintitlecellstyle.BorderTop = BorderStyle.None;
maintitlecellstyle.BorderLeft = BorderStyle.None;
maintitlecellstyle.BorderRight = BorderStyle.None;
maintitlecellstyle.VerticalAlignment = VerticalAlignment.Center;
maintitlecellstyle.Alignment = HorizontalAlignment.Center;
maintitlecellstyle.FillBackgroundColor = HSSFColor.Red.Index;
maintitlecellstyle.WrapText = true;
var maintitlefont = hssfWorkbook.CreateFont();
maintitlefont.Boldweight = (short)FontBoldWeight.Bold;
maintitlefont.FontHeightInPoints = 20;
maintitlecellstyle.SetFont(maintitlefont);
var subtitlecellstyle = hssfWorkbook.CreateCellStyle();
subtitlecellstyle.BorderBottom = BorderStyle.None;
subtitlecellstyle.BorderTop = BorderStyle.None;
subtitlecellstyle.BorderLeft = BorderStyle.None;
subtitlecellstyle.BorderRight = BorderStyle.None;
subtitlecellstyle.VerticalAlignment = VerticalAlignment.Center;
subtitlecellstyle.FillBackgroundColor = HSSFColor.Red.Index;
subtitlecellstyle.WrapText = true;
var subtitleFont = hssfWorkbook.CreateFont();
subtitleFont.Boldweight = (short)FontBoldWeight.Normal;
subtitleFont.FontHeightInPoints = 12;
subtitlecellstyle.SetFont(subtitleFont);
#endregion cellstyle
ISheet sheet;
sheet = hssfWorkbook.GetSheetAt(0);
hssfWorkbook.SetSheetName(0, "sheetName");
var childRowCount = 0;
for (var rownum = 0; rownum < 30; rownum++)
{
var keyarray = dicValue.Keys.Where(c => c.Split(',')[0] == rownum.ToString()).ToArray();
if (!keyarray.Any())
{
continue;
}
var row = sheet.CreateRow(rownum + childRowCount);
if (keyarray.Count() == 1) //该行只包含一个元素
{
var key = keyarray[0];
var colrow = key.Split(',')[0].GetInt() + childRowCount;
var valueproperty = dicValueProperty[key];
var valuearray = dicValue[key].Split(',');
var valuePropertyArray = valueproperty.Split(',');
var valueHeight = valuePropertyArray[0].GetInt();
var valueWidth = valuePropertyArray[1].GetInt();
if (valuearray.Length == 2)
{
if (valuearray[1] == "Title")
{
//设置标题
MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, maintitlecellstyle);
var maincell = row.CreateCell(0);
var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
if (mainchildvalue != null)
{
maincell.SetCellValue(mainchildvalue.ToString());
}
maincell.CellStyle = maintitlecellstyle;
sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
//sheet.SetColumnWidth(1, 100 * valueWidth);
continue;
}
}
if (valuearray.Length == 2)
{
MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, titlecellstyle);
var maincell = row.CreateCell(0);
var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
if (mainchildvalue != null)
{
maincell.SetCellValue(mainchildvalue.ToString());
}
maincell.CellStyle = cellstyle;
sheet.GetRow(row.RowNum).Height = (short)(100 * valueHeight);
sheet.SetColumnWidth(0, int.Parse(valuearray[0]) * DefaultWidth * valueWidth);
continue;
}
if (valuearray.Length == 3) //该行只包含一个元素
{
MergedCell(sheet, colrow, colrow, 1, valuearray[0].GetInt() - 1, titlecellstyle);
var cell = row.CreateCell(0);
cell.SetCellValue(valuearray[2]);
cell.CellStyle = cellstyle;
var cellValue = row.CreateCell(1);
cellValue.SetCellValue(model.GetType().GetProperty(valuearray[1]).GetValue(model, null)
.ToString());
cellValue.CellStyle = contentstyle;
sheet.GetRow(row.RowNum).Height = (short)(100 * valueHeight);
}
if (valuearray.Length == 4)
{
#region DisplayName
MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, titlecellstyle);
var cell = row.CreateCell(0);
cell.SetCellValue(valuearray[2]);
cell.CellStyle = cellstyle;
sheet.GetRow(colrow).Height = (short)(100 * valueHeight);
#endregion DisplayName
#region DisplayValue
if (!model.GetType().GetProperty(valuearray[1]).PropertyType.Name.Equals("String"))
{
var list =
model.GetType().GetProperty(valuearray[1]).GetValue(model, null) as
IEnumerable <object>;
if (list != null)
{
PropertyInfo[] childProperties = null;
list.ForEach(item =>
{
childProperties = item.GetType()
.GetProperties(BindingFlags.Instance | BindingFlags.Public);
});
var dicListValue = new Dictionary <string, string>();
var dicListValueProperty = new Dictionary <string, string>();
childProperties?.ToArray().ForEach(item =>
{
var attribute =
(DescriptionAttribute)item.GetCustomAttribute(typeof(DescriptionAttribute),
false);
if (attribute != null)
{
var description = attribute.Description;
var des = description.Split('!');
if (des.Length == 3)
{
if (!dicListValue.ContainsKey(des[0]))
{
dicListValue.Add(des[0], des[1]);
dicListValueProperty.Add(des[0], des[2]);
}
}
}
});
childRowCount++;
var titleRow = sheet.CreateRow(rownum + childRowCount);
dicListValue.ForEach(item =>
{
var mergedColumn = int.Parse(item.Key.Split(',')[1]);
var cellcol = int.Parse(item.Key.Split(',')[0]);
var newcell = titleRow.CreateCell(cellcol);
newcell.SetCellValue(item.Value.Split(',')[1]);
newcell.CellStyle = cellstyle;
if (mergedColumn > 1)
{
MergedCell(sheet, titleRow.RowNum, titleRow.RowNum, cellcol,
cellcol + mergedColumn - 1, cellstyle);
}
dicListValueProperty.ForEach(p =>
{
if (p.Key == item.Key)
{
sheet.GetRow(titleRow.RowNum).Height =
(short)(100 * int.Parse(p.Value.Split(',')[0]));
sheet.SetColumnWidth(cellcol,
DefaultWidth * int.Parse(p.Value.Split(',')[1]));
}
});
});
list.ForEach(item =>
{
childRowCount++;
var newrow = sheet.GetRow(rownum + childRowCount) ??
sheet.CreateRow(rownum + childRowCount);
dicListValue.ForEach(info =>
{
var cellcol = int.Parse(info.Key.Split(',')[0]);
var newcell = newrow.CreateCell(cellcol);
newcell.SetCellValue(item.GetType().GetProperty(info.Value.Split(',')[0])
.GetValue(item, null).ToString());
newcell.CellStyle = contentstyle;
var mergedColumn = int.Parse(info.Key.Split(',')[1]);
if (mergedColumn > 1)
{
MergedCell(sheet, newrow.RowNum, newrow.RowNum, cellcol,
cellcol + mergedColumn - 1, contentstyle);
}
dicListValueProperty.ForEach(p =>
{
if (p.Key == info.Key)
{
sheet.GetRow(newrow.RowNum).Height =
(short)(100 * int.Parse(p.Value.Split(',')[0]));
}
});
});
});
}
}
else
{
cell = row.CreateCell(1);
var childvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
if (childvalue != null)
{
cell.SetCellValue(childvalue.ToString());
cell.CellStyle = contentstyle;
}
}
#endregion DisplayValue
}
}
else
{
for (var colnum = 0; colnum < keyarray.Count(); colnum++)
{
var key = keyarray[colnum];
var colrow = key.Split(',')[0].GetInt() + childRowCount; //行号
var col = key.Split(',')[1].GetInt(); //列号
var valueproperty = dicValueProperty[key]; //valuepproperty
var valuearray = dicValue[key].Split(',');
var valuePropertyArray = valueproperty.Split(',');
var valueHeight = valuePropertyArray[0].GetInt();
var valueWidth = valuePropertyArray[1].GetInt();
if (valuearray[1] == "SubTitle")
{
//设置副标题
MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, subtitlecellstyle);
var maincell = row.CreateCell(0);
var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
if (mainchildvalue != null)
{
maincell.SetCellValue(mainchildvalue.ToString());
}
maincell.CellStyle = subtitlecellstyle;
sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
continue;
}
if (valuearray[1] == "Number")
{
var cellNum = int.Parse(keyarray[1].Split(',')[1]);
MergedCell(sheet, colrow, colrow, cellNum, cellNum + valuearray[0].GetInt() - 1,
subtitlecellstyle);
var maincell = row.CreateCell(cellNum);
var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
if (mainchildvalue != null)
{
maincell.SetCellValue(mainchildvalue.ToString());
}
maincell.CellStyle = subtitlecellstyle;
sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
continue;
}
#region DisplayName
var cell = row.CreateCell(col);
cell.SetCellValue(valuearray[2]);
cell.CellStyle = cellstyle;
sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
sheet.SetColumnWidth(col, DefaultWidth * valueWidth);
#endregion DisplayName
#region DisplayValue
cell = row.CreateCell(col + 1);
var value = model.GetType()?.GetProperty(valuearray[1])?.GetValue(model, null);
if (value != null)
{
cell.SetCellValue(value.ToString());
}
cell.CellStyle = contentstyle;
#endregion DisplayValue
#region marge
if (valuearray[0].GetInt() > 2)
{
MergedCell(sheet, colrow, colrow, col + 1, col + valuearray[0].GetInt() - 1,
contentstyle);
}
var margedCol = valuearray[0].GetInt() - 1;
for (var i = 0; i < margedCol; i++)
{
sheet.SetColumnWidth(col + 1 + i, DefaultWidth * valueWidth);
}
#endregion
}
}
}
using (var fs = File.OpenWrite(userPath))
{
hssfWorkbook.Write(fs);
}
if (exportPdf && ExportPdfFile.Equals("是"))
{
userPath = CreateOfficePdfFrom(userPath);
}
if (ExportZipFile.Equals("是") && !exportPdf)
{
userPath = CreateExcelZipFile(userPath, "xls", account);
}
return(userPath);
}
catch (Exception exception)
{
return(exception.Message);
}
}
public async Task <ActionResult> ExportToExcel(LotPackageQueryViewModel model)
{
IList <PackageDetail> lstLotPackage = new List <PackageDetail>();
ZPVMLotPackageViewModel m = new ZPVMLotPackageViewModel();
using (PackageQueryServiceClient client = new PackageQueryServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "Key.PackageNo,ItemNo",
Where = GetQueryCondition(model)
};
MethodReturnResult <IList <PackageDetail> > result = client.GetDetail(ref cfg);
if (result.Code == 0)
{
lstLotPackage = result.Data;
}
});
}
//创建工作薄。
IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ISheet ws = null;
for (int j = 0; j < lstLotPackage.Count; j++)
{
if (j % 65535 == 0)
{
ws = wb.CreateSheet();
IRow row = ws.CreateRow(0);
#region //列名
ICell cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(StringResource.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("包装号");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("项目号");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("批次号");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("工单号");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("物料编码");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("等级");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("花色");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("功率");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("电流");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("最大电流");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("电压");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("最大电压");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("填充因子");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("分档名称");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("子分档代码");
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("包装日期");
#endregion
font.Boldweight = 5;
}
PackageDetail obj = lstLotPackage[j];
IRow rowData = ws.CreateRow(j + 1);
Lot lot = m.GetLotData(obj.Key.ObjectNumber);
IVTestData ivtest = m.GetIVTestData(obj.Key.ObjectNumber);
List <string> dic = null;
string ff = "";
OemData oemData = m.GetOemData(obj.Key.ObjectNumber);
if (oemData != null)
{
dic = m.GetCodeAndItemNo(oemData);
ff = (oemData.FF * 100).ToString("F4");
}
else
{
lot = m.GetLotData(obj.Key.ObjectNumber);
ivtest = m.GetIVTestData(obj.Key.ObjectNumber);
}
#region //数据
ICell cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(j + 1);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Key.PackageNo);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.ItemNo);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Key.ObjectNumber);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.OrderNumber);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.MaterialCode);
if (oemData != null)
{
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.Grade);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.Color);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.PMAX);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.ISC);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.IPM);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.VOC);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.VPM);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ff);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.PnName == null ? string.Empty : oemData.PnName);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(oemData.PsSubCode);
}
else
{
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(lot != null ? lot.Grade : string.Empty);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(lot != null ? lot.Color : string.Empty);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.CoefPM : 0);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.CoefISC : 0);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.CoefIPM : 0);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.CoefVOC : 0);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.CoefVPM : 0);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.CoefFF : 0);
string powerName = string.Empty;
if (ivtest != null &&
!string.IsNullOrEmpty(ivtest.PowersetCode) &&
ivtest.PowersetItemNo != null)
{
powerName = m.GetPowersetName(ivtest.Key.LotNumber, ivtest.PowersetCode, ivtest.PowersetItemNo.Value);
}
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(powerName);
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(ivtest != null ? ivtest.PowersetSubCode : string.Empty);
}
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(string.Format("{0:yyyy-MM-dd}", obj.CreateTime));
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "LotPackageData.xls"));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="headerTextList">表头摘要信息</param>
public MemoryStream Export(DataTable dtSource, List<String> headerTextList)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("sheet1");
//设置Excel文件属性信息
SetFileProperty(workbook);
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//计算列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
//获取每一列的最大列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
for (int i = 0; i < headerTextList.Count; i++)
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(i);
headerRow.HeightInPoints = 18;
headerRow.CreateCell(0).SetCellValue(headerTextList[i]);
HSSFCellStyle headerStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Left;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 14;
//font.Boldweight = 700;
headerStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headerStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
}
#endregion
#region 列头及样式
{
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(headerTextList.Count);
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = headerTextList.Count + 1;
}
#endregion
#region 填充表格内容
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable[] dtSource, ExcelConfig[] excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
for (int dtIndex = 0, length = dtSource.Length; dtIndex < length; dtIndex++)
{
// int colint = 0;
DataTable data = dtSource[dtIndex].Copy();
var tatalCount = dtSource[dtIndex].Columns.Count;
// var index = 0;
/* for (int i = 0; i < dtSource[dtIndex].Columns.Count;)
* {
* index++;
* DataColumn column = dtSource[dtIndex].Columns[i];
* if (excelConfig[dtIndex].ColumnEntity[colint].Column != column.ColumnName)
* {
* dtSource[dtIndex].Columns.Remove(column.ColumnName);
* }
* else
* {
* i++;
* colint++;
* if (colint == excelConfig[dtIndex].ColumnEntity.Count)
* {
* for (var j = index; j < tatalCount; j++)
* {
* DataColumn column1 = data.Columns[j];
* dtSource[dtIndex].Columns.Remove(column1.ColumnName);
* }
* break;
* }
* }
* }*/
ISheet sheet = workbook.CreateSheet((dtIndex + 1).ToString());
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "administrator"; //填加xls文件作者信息
si.ApplicationName = "ZoonTop"; //填加xls文件创建程序信息
si.LastAuthor = "administrator"; //填加xls文件最后保存者信息
si.Comments = "ZoonTop自动生成excel"; //填加xls文件作者信息
si.Title = ""; //填加xls文件标题信息
si.Subject = ""; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource[dtIndex].Columns.Count];
string[] arrColName = new string[dtSource[dtIndex].Columns.Count]; //列名
ColumnEntity[] columnModel = new ColumnEntity[dtSource[dtIndex].Columns.Count]; //航头属性
ICellStyle[] arryColumStyle = new ICellStyle[dtSource[dtIndex].Columns.Count]; //样式表
if (excelConfig[dtIndex].Background != new Color())
{
if (excelConfig[dtIndex].Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig[dtIndex].Background);
}
}
//title文字
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig[dtIndex].TitlePoint;
if (excelConfig[dtIndex].ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig[dtIndex].ForeColor);
}
font.Boldweight = 700;
font.FontHeightInPoints = 20;
headStyle.SetFont(font);
headStyle.ShrinkToFit = true;
//垂直居中,水平居中
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.Alignment = HorizontalAlignment.Center;
//边框样式
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.BorderBottom = BorderStyle.Thin;
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = 15; // excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
//边框样式
cHeadStyle.BorderLeft = BorderStyle.Thin;
cHeadStyle.BorderRight = BorderStyle.Thin;
cHeadStyle.BorderTop = BorderStyle.Thin;
cHeadStyle.BorderBottom = BorderStyle.Thin;
//垂直居中
cHeadStyle.VerticalAlignment = VerticalAlignment.Center;
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource[dtIndex].Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig[dtIndex].ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig[dtIndex].ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
columnModel[item.Ordinal] = columnentity;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 /*|| columnentity.ForeColor != new Color()*/)
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
//边框样式
columnStyle.BorderLeft = BorderStyle.Thin;
columnStyle.BorderRight = BorderStyle.Thin;
columnStyle.BorderTop = BorderStyle.Thin;
columnStyle.BorderBottom = BorderStyle.Thin;
//单元格文字
IFont columnsFont = workbook.CreateFont();
columnsFont.FontHeightInPoints = 12;
columnStyle.SetFont(columnsFont);
//columnStyle.ShrinkToFit = true;
columnStyle.WrapText = true;//自动换行
//垂直居中
columnStyle.VerticalAlignment = VerticalAlignment.Center;
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig[dtIndex].IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource[dtIndex].Rows.Count; i++)
{
for (int j = 0; j < dtSource[dtIndex].Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[dtIndex].Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
string xh = dtSource[dtIndex].Columns[0].ToString(); //第一个参数，用来合并单元格
int rowIndex = 0;
int createIndex = 0; //已经创建的行数
int dataIndex = 1;
int[] mergeRows = new int[dtSource[dtIndex].Columns.Count]; //需要合并的行数
IRow[] headerRows;
IRow headerRow;
bool IsCellRangeAddress;
int titleRow = 0;
double[] totalCount = new double[dtSource[dtIndex].Columns.Count];
if (dtSource[dtIndex].Rows.Count == 0)//当列表头大于65536行时会报错，但是....
{
#region 新建表，填充表头，填充列头，样式
#region 表头及样式
if (excelConfig[dtIndex].Title != null)
{
headerRow = sheet.CreateRow(0);
if (excelConfig[dtIndex].TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.Height = 45 * 20;
headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
headerRow.GetCell(0).CellStyle = headStyle;
//标题的宽高
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
IsCellRangeAddress = false;
rowIndex = 1;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
if (columnEntity.IsCellRangeAddress)
{
rowIndex = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
IsCellRangeAddress = true;
}
}
headerRows = new IRow[rowIndex];
for (int hi = 0; hi < rowIndex; hi++)
{
headerRow = sheet.CreateRow(hi + 1);
headerRow.Height = 30 * 20;
headerRows[hi] = headerRow;
}
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
if (!IsCellRangeAddress)//简单表头
{
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
}
else//复杂表头
{
int[] indexs = new int[rowIndex];
int max = 0;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
int headerIndex = columnEntity.Top - 1;
int index = indexs[headerIndex];
for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
{
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
indexs[headerIndex]++;
}
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
if (columnEntity.IsCellRangeAddress)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(
columnEntity.Top,
columnEntity.Bottom,
columnEntity.Left,
columnEntity.Right
));
}
indexs[headerIndex]++;
max = max > indexs[headerIndex] ? max : indexs[headerIndex];
}
//填充表头的空缺
for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
{
for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
{
headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
}
}
#endregion
}
rowIndex += 1;
createIndex = rowIndex;
#endregion
#endregion
#region 合计
IRow totalRow = sheet.CreateRow(rowIndex);
totalRow.Height = 30 * 20;
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (column.Ordinal == 0)
{
totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
}
else
{
if (column.DataType.FullName.Equals("System.Double"))
{
totalRow.CreateCell(column.Ordinal).SetCellValue(0);
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue("");
}
}
totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
#endregion
}
foreach (DataRow row in dtSource[dtIndex].Rows)
{
#region 新建表，填充表头，填充列头，样式 如果没有数据表头为空
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)//数据过多创建新的sheet页
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (excelConfig[dtIndex].Title != null)
{
headerRow = sheet.CreateRow(0);
if (excelConfig[dtIndex].TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.Height = 45 * 20;
headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
headerRow.GetCell(0).CellStyle = headStyle;
//标题的宽高
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
IsCellRangeAddress = false;
rowIndex = 1;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
if (columnEntity.IsCellRangeAddress)
{
rowIndex = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
IsCellRangeAddress = true;
}
}
headerRows = new IRow[rowIndex];
for (int hi = 0; hi < rowIndex; hi++)
{
headerRow = sheet.CreateRow(hi + 1);
headerRow.Height = 30 * 20;
headerRows[hi] = headerRow;
}
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
if (!IsCellRangeAddress)//简单表头
{
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽 // 第二个参数的单位是1/256个字符宽度，但与前端不一致，故改为48
//sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
}
else//复杂表头
{
int[] indexs = new int[rowIndex];
int max = 0;
foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
{
int headerIndex = columnEntity.Top - 1;
int index = indexs[headerIndex];
for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
{
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
indexs[headerIndex]++;
}
headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
if (columnEntity.IsCellRangeAddress)
{
//设置一个合并单元格区域，使用上下左右定义CellRangeAddress区域
//CellRangeAddress四个参数为：起始行，结束行，起始列，结束列
sheet.AddMergedRegion(new CellRangeAddress(
columnEntity.Top,
columnEntity.Bottom,
columnEntity.Left,
columnEntity.Right
));
}
indexs[headerIndex]++;
max = max > indexs[headerIndex] ? max : indexs[headerIndex];
}
//填充表头的空缺
for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
{
for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
{
headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
}
}
#endregion
}
rowIndex += 1;
createIndex = rowIndex;
titleRow = rowIndex;
#endregion
}
#endregion
#region 填充内容
if (createIndex <= rowIndex)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = 30 * 20;
createIndex++;
}
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (columnModel[column.Ordinal].Merge == "row")//合并行，行和列不可能同时出现在一个单元格
{
ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
if (mergeRows[column.Ordinal] == 0)//计算要合并几行
{
//不计算父子关系，同级关系，只要和下一个相同，就合并 to edit
//改成只有序号一样的合并 to edit
//增加父级序号也要一致(父级序号是子级的子集：父级：B，子级BB)
for (int subDataIndex = dataIndex, total = dtSource[dtIndex].Rows.Count; subDataIndex < total; subDataIndex++)
{
/*if (drValue.Equals(dtSource[dtIndex].Rows[subDataIndex][column].ToString()))
* {
* mergeRows[column.Ordinal]++;
* }*/
string[] arr = column.ColumnName.ToString().Split('_');
string mark = "";
if (arr.Length > 1)
{
int markCount = 0;
int markLength = arr[0].Length;
bool isAllEqually = true;
foreach (DataColumn parentColumn in dtSource[dtIndex].Columns)
{
if (markCount <= markLength)
{
mark = markCount > 0 ? arr[0].Substring(0, markCount) + "_XH" : "XH";
if (parentColumn.ColumnName.Equals(mark))
{
if (!row[parentColumn.ColumnName].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][parentColumn.ColumnName].ToString()))
{
isAllEqually = false;
}
markCount++;
}
}
else
{
break;
}
}
if (isAllEqually)
{
mergeRows[column.Ordinal]++;
}
}
else
{
mark = "XH";
//当前序号一致 最外层
if (row[mark].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][mark].ToString()))
{
mergeRows[column.Ordinal]++;
}
}
}
for (int i = 1; i <= mergeRows[column.Ordinal]; i++)//预创建行
{
if (sheet.GetRow(rowIndex + i) != null)
{
IRow dataRow = sheet.CreateRow(rowIndex + i);
dataRow.Height = 30 * 20;
createIndex++;
}
}
sheet.AddMergedRegion(new CellRangeAddress(
rowIndex,
rowIndex + mergeRows[column.Ordinal],
column.Ordinal,
column.Ordinal
));
}
else
{
drValue = "";
mergeRows[column.Ordinal]--;
}
SetCell(newCell, dateStyle, column.DataType, drValue);
}
else if (columnModel[column.Ordinal].Merge == "col")//合并列
{
}
else//无合并单元格
{
ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
if (column.DataType.FullName.Equals("System.Double"))
{
double value;
try
{
value = double.Parse(row[column].ToString());
totalCount[column.Ordinal] += value;
}
catch
{
totalCount[column.Ordinal] += 0;
}
}
}
#endregion
#region 合计
if (rowIndex == 65534 || dtSource[dtIndex].Rows.Count + titleRow == rowIndex + 1)//最后一行 或者所有数据填充完
{
IRow totalRow = sheet.CreateRow(rowIndex + 1);
totalRow.Height = 30 * 20;
foreach (DataColumn column in dtSource[dtIndex].Columns)
{
if (column.Ordinal == 0)
{
totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
}
else
{
if (column.DataType.FullName.Equals("System.Double"))
{
if (column.ColumnName.IndexOf("_") > 0)//子表 包含小计
{
totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal] / 2);
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal]);
}
}
else
{
totalRow.CreateCell(column.Ordinal).SetCellValue("");
}
}
totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
}
rowIndex++;
}
#endregion
rowIndex++;
dataIndex++;
}
#endregion
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}