public void TestWriteModifySheetMerged()
{
HSSFWorkbook wb = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet s = wb.CreateSheet();
for (int rownum = 0; rownum < 100; rownum++)
{
IRow r = s.CreateRow(rownum);
for (int cellnum = 0; cellnum < 50; cellnum += 2)
{
ICell c = r.CreateCell(cellnum);
c.SetCellValue(rownum * 10000 + cellnum
+ (((double)rownum / 1000)
+ ((double)cellnum / 10000)));
c = r.CreateCell(cellnum + 1);
c.SetCellValue(new HSSFRichTextString("TEST"));
}
}
s.AddMergedRegion(new CellRangeAddress(0, 10, 0, 10));
s.AddMergedRegion(new CellRangeAddress(30, 40, 5, 15));
sanityChecker.CheckHSSFWorkbook(wb);
wb = HSSFTestDataSamples.WriteOutAndReadBack(wb);
s = wb.GetSheetAt(0);
CellRangeAddress r1 = s.GetMergedRegion(0);
CellRangeAddress r2 = s.GetMergedRegion(1);
ConfirmRegion(new CellRangeAddress(0, 10, 0, 10), r1);
ConfirmRegion(new CellRangeAddress(30, 40, 5, 15), r2);
}
/// <summary>
/// 合并单元格
/// </summary>
/// <typeparam name="T">实体类型</typeparam>
/// <param name="sheet">工作表</param>
/// <param name="columnIndex">列索引</param>
/// <param name="options">导出选项配置</param>
private void MergeCells <T>(NPOI.SS.UserModel.ISheet sheet, int columnIndex, IExportOptions <T> options)
where T : class, new()
{
string currentCellValue;
var startRowIndex = options.DataRowStartIndex;
NPOI.SS.Util.CellRangeAddress mergeRangeAddress;
var startRow = sheet.GetRow(startRowIndex);
if (startRow == null)
{
return;
}
var startCell = startRow.GetCell(columnIndex);
if (startCell == null)
{
return;
}
string startCellValue = startCell.StringCellValue;
if (string.IsNullOrWhiteSpace(startCellValue))
{
return;
}
for (var rowIndex = options.DataRowStartIndex; rowIndex < sheet.PhysicalNumberOfRows; rowIndex++)
{
var cell = sheet.GetRow(rowIndex)?.GetCell(columnIndex);
currentCellValue = cell == null ? string.Empty : cell.StringCellValue;
if (currentCellValue.Trim() != startCellValue.Trim())
{
mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex - 1, columnIndex, columnIndex);
if (mergeRangeAddress.NumberOfCells > 1)
{
sheet.AddMergedRegion(mergeRangeAddress);
startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center;
}
startRowIndex = rowIndex;
startCellValue = currentCellValue;
}
if (rowIndex == sheet.PhysicalNumberOfRows - 1 && startRowIndex != rowIndex)
{
mergeRangeAddress = new CellRangeAddress(startRowIndex, rowIndex, columnIndex, columnIndex);
sheet.AddMergedRegion(mergeRangeAddress);
startRow.GetCell(columnIndex).CellStyle.VerticalAlignment = VerticalAlignment.Center;
}
}
}
protected static MemoryStream GetMS(List <FishEntity.CompanyEntity> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "客户市场需求预测表", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "客户编号", CellType.String, cellStyle);
CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
CreateCell(row, 2, "类别", CellType.String, cellStyle);
CreateCell(row, 3, "综合等级", CellType.String, cellStyle);
CreateCell(row, 4, "需求量等级", CellType.String, cellStyle);
CreateCell(row, 5, "活跃程度", CellType.String, cellStyle);
CreateCell(row, 6, "忠诚度", CellType.String, cellStyle);
CreateCell(row, 7, "主要产品", CellType.String, cellStyle);
CreateCell(row, 8, "业务员", CellType.String, cellStyle);
CreateCell(row, 9, "联系人", CellType.String, cellStyle);
CreateCell(row, 10, "最近联系日期", CellType.String, cellStyle);
CreateCell(row, 11, "最近周预估", CellType.String, cellStyle);
CreateCell(row, 12, "最近月预估", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.CompanyEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.fullname);
CreateCell(row, 2, model.type);
CreateCell(row, 3, model.generallevel);
CreateCell(row, 4, model.requiredlevel);
CreateCell(row, 5, model.managestandard);
CreateCell(row, 6, model.activelevel);
CreateCell(row, 7, model.products);
CreateCell(row, 8, model.salesman);
CreateCell(row, 9, model.linkman);
CreateCell(row, 10, model.currentlink);
CreateCell(row, 11, model.currentweekestimate);
CreateCell(row, 12, model.currentmonthestimate);
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
protected static MemoryStream GetMS(List <FishEntity.CallRecordsEntity> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "通话记录表", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 12));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "记录单号", CellType.String, cellStyle);
CreateCell(row, 1, "客户名称", CellType.String, cellStyle);
CreateCell(row, 2, "联系人", CellType.String, cellStyle);
CreateCell(row, 3, "移动电话", CellType.String, cellStyle);
CreateCell(row, 4, "固定电话", CellType.String, cellStyle);
CreateCell(row, 5, "客户等级", CellType.String, cellStyle);
CreateCell(row, 6, "日期", CellType.String, cellStyle);
CreateCell(row, 7, "沟通内容", CellType.String, cellStyle);
CreateCell(row, 8, "品质要求", CellType.String, cellStyle);
CreateCell(row, 9, "主要产品", CellType.String, cellStyle);
CreateCell(row, 10, "估计周用量", CellType.String, cellStyle);
CreateCell(row, 11, "估计月用量", CellType.String, cellStyle);
CreateCell(row, 12, "地址", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.CallRecordsEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.customer);
CreateCell(row, 2, model.linkman);
CreateCell(row, 3, model.mobile);
CreateCell(row, 4, model.telephone);
CreateCell(row, 5, model.customerlevel);
CreateCell(row, 6, model.currentdate.Value.ToString("yyyy-MM-dd"));
CreateCell(row, 7, model.communicatecontent);
CreateCell(row, 8, model.requiredquantity);
CreateCell(row, 9, model.products);
CreateCell(row, 10, model.weekestimate);
CreateCell(row, 11, model.monthestimate);
CreateCell(row, 12, model.address);
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
public FileResult OmitCheckCountExportExcel()
{
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("未巡统计表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位/姓名");
row.CreateCell(1).SetCellValue("应巡");
row.CreateCell(2).SetCellValue("已巡");
row.CreateCell(3).SetCellValue("未巡");
row.CreateCell(4).SetCellValue("完成率");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 4));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 2);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.PointCount);
row.CreateCell(2).SetCellValue(v.PointCount0);
row.CreateCell(3).SetCellValue(v.PointCount1);
row.CreateCell(4).SetCellValue(v.PointCount2);
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "未巡统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
private static void WriteData(ref NPOI.SS.UserModel.ISheet sheet1, DataTable dt, Dictionary <string, string> columnInfo, string datadesc, int datastartrow)
{
//int datastartrow = 0;
if (!string.IsNullOrEmpty(datadesc))
{
NPOI.SS.UserModel.IRow desc = sheet1.CreateRow(datastartrow);
for (int i = 0; i < 10; i++)
{
desc.CreateCell(i);
}
NPOI.SS.Util.CellRangeAddress address = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 9);
sheet1.AddMergedRegion(address);
sheet1.GetRow(0).GetCell(0).SetCellValue(datadesc);
datastartrow++;
}
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(datastartrow);
int tick = 0;
foreach (string value in columnInfo.Values)
{
row1.CreateCell(tick).SetCellValue(value);
tick++;
}
//将数据逐步写入sheet1各个行
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + datastartrow + 1);
int index = 0;
foreach (string key in columnInfo.Keys)
{
bool isnumberic = false;
if (key.ToLower() == "rowid")
{
rowtemp.CreateCell(index).SetCellValue(i + 1);
}
else
{
string data = GetValue(dt.Rows[i], key, out isnumberic);
if (isnumberic && data != "--")
{
rowtemp.CreateCell(index).SetCellValue(string.IsNullOrEmpty(data) ? 0 : double.Parse(data));
}
else
{
rowtemp.CreateCell(index).SetCellValue(data);
}
}
index++;
}
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="sheet"></param>
/// <param name="options"></param>
private void CellRangeAddress <T>(NPOI.SS.UserModel.ISheet sheet, IExportOptions <T> options) where T : class, new()
{
//if (options.HeaderRow.All(x => x.Cells.All(m => m.ColumnSpan == 1)))
// return;
//var rows = options.HeaderRow.Where(x => x.Cells.Any(m => m.ColumnSpan > 1)).ToList();
var rows = options.HeaderRow.Where(x => x.Cells.Any(t => t.RowSpan > 1 || t.ColumnSpan > 1)).ToList();
if (rows.Any())
{
rows.OrderBy(x => x.RowIndex).ForEach(row =>
{
row.Cells.OrderBy(x => x.ColumnIndex).ForEach(x =>
{
if (x.RowSpan <= 1 && x.ColumnSpan <= 1)
{
return;
}
var region =
new CellRangeAddress(x.RowIndex, x.EndRowIndex, x.ColumnIndex, x.EndColumnIndex);
sheet.AddMergedRegion(region);
});
});
}
}
protected static MemoryStream GetMS(List <FishEntity.ProductEntity> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
row.HeightInPoints = 26;
CreateCell(row, 0, "报盘", CellType.String, style);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 20));
row = sheet.CreateRow(1);
style = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Normal);
CreateCell(row, 0, "供应商", CellType.String, style);
CreateCell(row, 1, "联系人", CellType.String, style);
CreateCell(row, 2, "品质", CellType.String, style);
CreateCell(row, 3, "鱼粉ID", CellType.String, style);
CreateCell(row, 4, "状态", CellType.String, style);
CreateCell(row, 5, "年份", CellType.String, style);
CreateCell(row, 6, "装货时间", CellType.String, style);
CreateCell(row, 7, "蛋白", CellType.String, style);
CreateCell(row, 8, "TVN", CellType.String, style);
CreateCell(row, 9, "灰份", CellType.String, style);
CreateCell(row, 10, "沙盐", CellType.String, style);
CreateCell(row, 11, "组胺", CellType.String, style);
CreateCell(row, 12, "FFA", CellType.String, style);
CreateCell(row, 13, "脂肪", CellType.String, style);
CreateCell(row, 14, "水份", CellType.String, style);
CreateCell(row, 15, "沙", CellType.String, style);
CreateCell(row, 16, "数量/吨", CellType.String, style);
CreateCell(row, 17, "备注", CellType.String, style);
CreateCell(row, 18, "品牌", CellType.String, style);
CreateCell(row, 19, "最近报价", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.ProductEntity model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.supplier);
CreateCell(row, 1, model.linkman);
CreateCell(row, 2, model.quality);
CreateCell(row, 3, model.code);
CreateCell(row, 4, model.statename);
CreateCell(row, 5, "");
CreateCell(row, 6, "");
CreateCell(row, 7, model.quote_protein.ToString());
CreateCell(row, 8, model.quote_tvn.ToString());
CreateCell(row, 9, model.quote_graypart.ToString());
CreateCell(row, 10, model.quote_sandsalt.ToString());
CreateCell(row, 11, model.quote_amine.ToString());
CreateCell(row, 12, model.quote_ffa.ToString());
CreateCell(row, 13, model.quote_fat.ToString());
CreateCell(row, 14, model.quote_water.ToString());
CreateCell(row, 15, model.quote_sand.ToString());
CreateCell(row, 16, "");
CreateCell(row, 17, model.remark);
CreateCell(row, 18, model.brand);
CreateCell(row, 19, model.latestquote.ToString());
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
private static MemoryStream GetExcelMemoryStream(List <dynamic> orderItemList)
{
var first = orderItemList.FirstOrDefault();
//创建表格
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义样式
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 10;
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)220, (byte)220, (byte)220);
// titleStyle
HSSFCellStyle titleStyle = (HSSFCellStyle)book.CreateCellStyle();
//（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
titleStyle.FillForegroundColor = HSSFColor.Pink.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
titleStyle.BorderBottom = BorderStyle.Thin;
titleStyle.BorderLeft = BorderStyle.Thin;
titleStyle.BorderRight = BorderStyle.Thin;
titleStyle.BorderTop = BorderStyle.Thin;
titleStyle.SetFont(cellStyleFont);//设置字体
//headStyle
HSSFCellStyle headStyle = (HSSFCellStyle)book.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center; //水平居中
headStyle.BorderBottom = BorderStyle.Thin;
headStyle.BorderLeft = BorderStyle.Thin;
headStyle.BorderRight = BorderStyle.Thin;
headStyle.BorderTop = BorderStyle.Thin;
headStyle.SetFont(cellStyleFont);
//borderStyle
HSSFCellStyle borderStyle = (HSSFCellStyle)book.CreateCellStyle();
borderStyle.BorderBottom = BorderStyle.Thin;
borderStyle.BorderLeft = BorderStyle.Thin;
borderStyle.BorderRight = BorderStyle.Thin;
borderStyle.BorderTop = BorderStyle.Thin;
#endregion
#region 表头
//设置单元格宽度
sheet.SetColumnWidth(0, 5000); //采购日期
sheet.SetColumnWidth(1, 5000); //门店名称
sheet.SetColumnWidth(2, 14000); //门店地址
sheet.SetColumnWidth(3, 4500); //采购数量
sheet.SetColumnWidth(4, 4500); //操作人
//表头
NPOI.SS.UserModel.IRow row = sheet.CreateRow(4);
row.Height = 350;
row.CreateCell(0).SetCellValue("采购日期");
row.CreateCell(1).SetCellValue("门店名称");
row.CreateCell(2).SetCellValue("门店地址");
row.CreateCell(3).SetCellValue("采购数量");
row.CreateCell(4).SetCellValue("操作人");
for (int i = 0; i < 5; i++)
{
row.Cells[i].CellStyle = titleStyle;
}
//合并单元格
CellRangeAddress region0 = new CellRangeAddress(0, 0, 0, 4);
sheet.AddMergedRegion(region0);
IRow row0 = sheet.CreateRow(0);
row0.CreateCell(0).SetCellValue("冷链食品处理详情");
row0.Cells[0].CellStyle = headStyle;
var row0Cell4 = row0.CreateCell(4);
row0Cell4.CellStyle = headStyle;
IRow row1 = sheet.CreateRow(1);
row1.CreateCell(0).SetCellValue("处理商品批次码");
row1.CreateCell(1).SetCellValue(first.BatchNo.ToString());
row1.Cells[0].CellStyle = titleStyle;
IRow row2 = sheet.CreateRow(2);
row2.CreateCell(0).SetCellValue("处理日期");
row2.CreateCell(1).SetCellValue(first.NoticeTime?.ToString("yyyy.MM.dd"));
row2.Cells[0].CellStyle = titleStyle;
IRow row3 = sheet.CreateRow(3);
row3.CreateCell(0).SetCellValue("处理人");
row3.CreateCell(1).SetCellValue(first.NoticeUserName.ToString());
row3.Cells[0].CellStyle = titleStyle;
//设置黑色边框
for (int i = 1; i < 4; i++)
{
var iRow = sheet.GetRow(i);
for (int j = 1; j < 5; j++)
{
var jCell = (j == 1) ? iRow.GetCell(j) : iRow.CreateCell(j);
jCell.CellStyle = borderStyle;
}
}
#endregion
#region 循环数据
int index = 5;
foreach (var item in orderItemList)
{
IRow itemRow = sheet.CreateRow(index);
var rowCell0 = itemRow.CreateCell(0);
rowCell0.SetCellValue((item.ImportTime ?? item.SubTime).ToString("yyyy.MM.dd"));
rowCell0.CellStyle = borderStyle;
var rowCell1 = itemRow.CreateCell(1);
rowCell1.SetCellValue(item.TargetStoreName.ToString());
rowCell1.CellStyle = borderStyle;
var rowCell2 = itemRow.CreateCell(2);
rowCell2.SetCellValue(item.TargetStoreAddress.ToString());
rowCell2.CellStyle = borderStyle;
var rowCell3 = itemRow.CreateCell(3);
rowCell3.SetCellValue(item.Number?.ToString());
rowCell3.CellStyle = borderStyle;
var rowCell4 = itemRow.CreateCell(4);
rowCell4.SetCellValue(item.ImportUserNames.ToString());
rowCell4.CellStyle = borderStyle;
index++;
}
#endregion
MemoryStream ms = new MemoryStream();
book.Write(ms);
return(ms);
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="r1">左上角单元格行标(从0开始，下同)</param>
/// <param name="c1">左上角单元格列标</param>
/// <param name="r2">右下角单元格行标</param>
/// <param name="c2">右下角单元格列标</param>
public void Merge(int r1, int c1, int r2, int c2)
{
osheet.AddMergedRegion(new CellRangeAddress(r1, c1, r2, c2));
}
protected static MemoryStream GetMS(List <FishEntity.SelfStorageFlowingReportVo> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "自营自制库存流水账", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 18));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "鱼粉ID", CellType.String, cellStyle);
CreateCell(row, 1, "品名", CellType.String, cellStyle);
CreateCell(row, 2, "状态", CellType.String, cellStyle);
CreateCell(row, 3, "单据类型", CellType.String, cellStyle);
CreateCell(row, 4, "单据号码", CellType.String, cellStyle);
CreateCell(row, 5, "日期", CellType.String, cellStyle);
CreateCell(row, 6, "出/入库存", CellType.String, cellStyle);
CreateCell(row, 7, "重量(吨)", CellType.String, cellStyle);
CreateCell(row, 8, "数量(包)", CellType.String, cellStyle);
CreateCell(row, 9, "国别", CellType.String, cellStyle);
CreateCell(row, 10, "工艺分类", CellType.String, cellStyle);
CreateCell(row, 11, "品质规格", CellType.String, cellStyle);
CreateCell(row, 12, "所属货主", CellType.String, cellStyle);
CreateCell(row, 13, "到港时间", CellType.String, cellStyle);
CreateCell(row, 14, "代理开证公司", CellType.String, cellStyle);
CreateCell(row, 15, "货代报关公司", CellType.String, cellStyle);
CreateCell(row, 16, "SGS(蛋白)", CellType.String, cellStyle);
CreateCell(row, 17, "SGS(TVN)", CellType.String, cellStyle);
CreateCell(row, 18, "组胺", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.SelfStorageFlowingReportVo model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.productcode);
CreateCell(row, 1, model.productname);
CreateCell(row, 2, model.statename);
CreateCell(row, 3, model.billtype);
CreateCell(row, 4, model.billcode);
CreateCell(row, 5, model.date.ToString("yyyy-MM-dd"));
CreateCell(row, 6, model.storagetype);
CreateCell(row, 7, model.weight.ToString());
CreateCell(row, 8, model.package.ToString());
CreateCell(row, 9, model.nature);
CreateCell(row, 10, model.techtype);
CreateCell(row, 11, model.specification);
CreateCell(row, 12, model.ownername);
CreateCell(row, 13, model.arriveportdate);
CreateCell(row, 14, model.agentifcompany);
CreateCell(row, 15, model.customsofcompany);
CreateCell(row, 16, model.sgs_protein.ToString());
CreateCell(row, 17, model.sgs_tvn.ToString());
CreateCell(row, 18, model.sgs_amine.ToString());
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
protected static MemoryStream GetMS(List <FishEntity.ProductConfirmVo> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
#region head row
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
ICellStyle style = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
row.HeightInPoints = 26;
CreateCell(row, 0, "确盘", CellType.String, style);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 28));
row = sheet.CreateRow(1);
style = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Normal);
CreateCell(row, 0, "鱼粉ID", CellType.String, style);
CreateCell(row, 1, "报盘单位", CellType.String, style);
CreateCell(row, 2, "联系人", CellType.String, style);
CreateCell(row, 3, "国别", CellType.String, style);
CreateCell(row, 4, "品质", CellType.String, style);
CreateCell(row, 5, "最新报盘日期", CellType.String, style);
CreateCell(row, 6, "船期最快", CellType.String, style);
CreateCell(row, 7, "船期最晚", CellType.String, style);
CreateCell(row, 8, "蛋白", CellType.String, style);
CreateCell(row, 9, "TVN", CellType.String, style);
CreateCell(row, 10, "组胺", CellType.String, style);
CreateCell(row, 11, "FFA", CellType.String, style);
CreateCell(row, 12, "盐和砂", CellType.String, style);
CreateCell(row, 13, "灰份", CellType.String, style);
CreateCell(row, 14, "备注", CellType.String, style);
CreateCell(row, 15, "开证单位", CellType.String, style);
CreateCell(row, 16, "联系人", CellType.String, style);
CreateCell(row, 17, "SGS重量", CellType.String, style);
CreateCell(row, 18, "品牌", CellType.String, style);
CreateCell(row, 19, "船期", CellType.String, style);
CreateCell(row, 20, "蛋白", CellType.String, style);
CreateCell(row, 21, "TVN", CellType.String, style);
CreateCell(row, 22, "组胺", CellType.String, style);
CreateCell(row, 23, "FFA", CellType.String, style);
CreateCell(row, 24, "盐和砂", CellType.String, style);
CreateCell(row, 25, "灰份", CellType.String, style);
CreateCell(row, 26, "日期", CellType.String, style);
CreateCell(row, 27, "美元价", CellType.String, style);
CreateCell(row, 28, "人民币价", CellType.String, style);
#endregion
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.ProductConfirmVo model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.quotesupplier);
CreateCell(row, 2, model.quotelinkman);
CreateCell(row, 3, model.nature);
CreateCell(row, 4, model.specification);
CreateCell(row, 5, model.quotedate);
CreateCell(row, 6, model.quotesaildatefast);
CreateCell(row, 7, model.quotesaildatelate);
CreateCell(row, 8, model.quote_protein.ToString());
CreateCell(row, 9, model.quote_tvn.ToString());
CreateCell(row, 10, model.quote_amine.ToString());
CreateCell(row, 11, model.quote_ffa.ToString());
CreateCell(row, 12, model.quote_sandsalt.ToString());
CreateCell(row, 13, model.quote_graypart.ToString());
CreateCell(row, 14, model.remark);
CreateCell(row, 15, model.confirmagent);
CreateCell(row, 16, model.confirmlinkman);
CreateCell(row, 17, model.confirmsgsweight.ToString("f2"));
CreateCell(row, 18, model.brand);
CreateCell(row, 19, model.confirmsaildate);
CreateCell(row, 20, model.sgs_protein.ToString());
CreateCell(row, 21, model.sgs_tvn.ToString());
CreateCell(row, 22, model.sgs_amine.ToString());
CreateCell(row, 23, model.sgs_ffa.ToString());
CreateCell(row, 24, model.sgs_sandsalt.ToString());
CreateCell(row, 25, model.sgs_graypart.ToString());
CreateCell(row, 26, model.confirmdate);
CreateCell(row, 27, model.confirmdollars.ToString("f2"));
CreateCell(row, 28, model.confirmrmb.ToString("f2"));
#endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 10;
hssfSheet.SetColumnWidth(0, 10 * 256);
hssfSheet.SetColumnWidth(3, 10 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 13));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 30 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue("反冲材料补料单");
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 13));
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
// 标题样式
NPOI.SS.UserModel.ICellStyle cellStyle1 = hssfworkbook.CreateCellStyle();
cellStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle1.VerticalAlignment = VerticalAlignment.Center;
cellStyle1.BorderBottom = NPOI.SS.UserModel.BorderStyle.None;
cellStyle1.BorderLeft = NPOI.SS.UserModel.BorderStyle.None;
cellStyle1.BorderRight = NPOI.SS.UserModel.BorderStyle.None;
cellStyle1.BorderTop = NPOI.SS.UserModel.BorderStyle.None;
#endregion
//数据样式
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#region 表数据
// 表数据
for (int k = 0; k < 2; k++)
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 2; i += 2)
{
row.CreateCell(i).SetCellValue(dr[0].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 2; i < 5; i += 3)
{
row.CreateCell(i).SetCellValue(dr[1].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 5; i < 7; i += 2)
{
row.CreateCell(i).SetCellValue(dr[2].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 7; i < 9; i += 2)
{
row.CreateCell(i).SetCellValue(dr[3].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 9; i < 11; i += 2)
{
row.CreateCell(i).SetCellValue(dr[4].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 11; i < 14; i += 3)
{
row.CreateCell(i).SetCellValue(dr[5].ToString());
row.GetCell(i).CellStyle = cellStyle1;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle1;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle1;
row.CreateCell(4).SetCellValue("");
row.GetCell(4).CellStyle = cellStyle1;
row.CreateCell(6).SetCellValue("");
row.GetCell(6).CellStyle = cellStyle1;
row.CreateCell(8).SetCellValue("");
row.GetCell(8).CellStyle = cellStyle1;
row.CreateCell(10).SetCellValue("");
row.GetCell(10).CellStyle = cellStyle1;
row.CreateCell(12).SetCellValue("");
row.GetCell(12).CellStyle = cellStyle1;
row.CreateCell(13).SetCellValue("");
row.GetCell(13).CellStyle = cellStyle1;
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 4));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 5, 6));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 7, 8));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 9, 10));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 11, 13));
}
// 表数据
for (int k = 2; k < dt.Rows.Count; k++)
{
if (k == dt.Rows.Count - 1)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < 2; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[0].ToString());
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 2; i < 4; i++)
{
rowlast.CreateCell(i).SetCellValue("");
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 4; i < 6; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[2].ToString());
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 6; i < 8; i++)
{
rowlast.CreateCell(i).SetCellValue("");
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 8; i < 10; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[4].ToString());
rowlast.GetCell(i).CellStyle = cellStyle1;
}
for (int i = 10; i < 14; i++)
{
rowlast.CreateCell(i).SetCellValue("");
rowlast.GetCell(i).CellStyle = cellStyle1;
}
//hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 8, 9));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 10, 13));
}
else
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 1; i++)
{
row.CreateCell(i).SetCellValue(dr[i].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 1; i < 3; i++)
{
row.CreateCell(i).SetCellValue(dr[1].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 3; i < 6; i++)
{
row.CreateCell(i).SetCellValue(dr[2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 6; i < 8; i++)
{
row.CreateCell(i).SetCellValue(dr[3].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 8; i < 10; i++)
{
row.CreateCell(i).SetCellValue(dr[4].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 10; i < 12; i++)
{
row.CreateCell(i).SetCellValue(dr[5].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 12; i < 14; i++)
{
row.CreateCell(i).SetCellValue(dr[6].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(2).SetCellValue("");
row.GetCell(2).CellStyle = cellStyle;
row.CreateCell(4).SetCellValue("");
row.GetCell(4).CellStyle = cellStyle;
row.CreateCell(5).SetCellValue("");
row.GetCell(5).CellStyle = cellStyle;
row.CreateCell(7).SetCellValue("");
row.GetCell(7).CellStyle = cellStyle;
row.CreateCell(9).SetCellValue("");
row.GetCell(9).CellStyle = cellStyle;
row.CreateCell(11).SetCellValue("");
row.GetCell(11).CellStyle = cellStyle;
row.CreateCell(13).SetCellValue("");
row.GetCell(13).CellStyle = cellStyle;
#region 合并单元格
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 2));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 3, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 8, 9));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 10, 11));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 12, 13));
#endregion
}
}
#endregion
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
try
{
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
using (var conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ELCO_ConnectionString"].ToString()))
{
SqlCommand cmd = new SqlCommand();
SqlTransaction transaction = null;
try
{
conn.Open();
transaction = conn.BeginTransaction();
cmd.Transaction = transaction;
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
string str1 = string.Empty;
for (int i = 0; i < materialid.Length; i++)
{
str1 = "update MFG_WIP_BKF_MTL_Record set Status='3',ConfirmTime=GETDATE(),ConfirmUser='******',PrintTime=GETDATE() where ID=" + materialid[i];
cmd.CommandText = str1;
cmd.ExecuteNonQuery();
}
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AppendHeader("content-type", "application/x-msexcel");
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
Response.Flush();
Response.Close();
transaction.Commit();
}
catch (Exception ex)
{
transaction.Rollback();
Response.Write(ex.Message);
}
}
}
catch (Exception ex)
{
Response.Write(ex.Message);
}
}
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
public FileResult OutRaiLExportExcel()
{
OutRaiLCount_SW sw = new OutRaiLCount_SW();
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUCheckCls.getOutRaiLCountModel(sw);
DateTime dt1 = Convert.ToDateTime(sw.DateBegin);
DateTime dt2 = Convert.ToDateTime(sw.DateEnd);
int days = ClsStr.getDateDiff(sw.DateBegin, sw.DateEnd) + 1;//日期包含天数
//vMenu.MENUNAME 页面/菜单名称
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
for (int i = 0; i < days; i++)
{
sheet1.SetColumnWidth(i + 2, 10 * 256);
}
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("出围统计表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位/姓名");
row.CreateCell(1).SetCellValue("出围合计");
row.CreateCell(2).SetCellValue("日期（日）");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
for (int i = 0; i < days; i++)
{
DateTime tm = dt1.AddDays(i);
row.CreateCell(i + 2).SetCellValue(tm.ToString("dd"));
row.GetCell(i + 2).CellStyle = getCellStyleHead(book);
}
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, days + 1));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, days - 2));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1));
int rowI = 0;
foreach (var v in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(v.ORGName);
row.CreateCell(1).SetCellValue(v.Count);
string[] arr = v.DayCountList.Split(',');
for (int i = 0; i < days; i++)
{
row.CreateCell(i + 2).SetCellValue(arr[i]);
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "出围统计表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
int dtcolunmnum = dt.Columns.Count;
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 13;
//hssfSheet.SetColumnWidth(0, 20 * 256);
//hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtcolunmnum - 1));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 30 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue(fileName);
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, dtcolunmnum - 1));
// 标题样式
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#endregion
// 表数据
for (int k = 0; k < dt.Rows.Count; k++)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[i].ToString());
rowlast.GetCell(i).CellStyle = cellStyle;
}
}
// 表数据
//for (int k = 0; k < dt.Rows.Count; k++)
//{
// hssfSheet.AutoSizeColumn(k);
//}
//获取当前列的宽度，然后对比本列的长度，取最大值
for (int columnNum = 0; columnNum <= dt.Columns.Count; columnNum++)
{
int columnWidth = hssfSheet.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= hssfSheet.LastRowNum; rowNum++)
{
IRow currentRow;
//当前行未被使用过
if (hssfSheet.GetRow(rowNum) == null)
{
currentRow = hssfSheet.CreateRow(rowNum);
}
else
{
currentRow = hssfSheet.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
}
hssfSheet.SetColumnWidth(columnNum, columnWidth * 256);
}
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
//保存excel文件
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Report/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Report/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
public static void ExportExcel(string stuID)
{
List <Course> Courses = CourseService.GetCourses(stuID);
//创建Excel工作薄
HSSFWorkbook excelBook = new HSSFWorkbook();
//创建工作表1和工作表2并命名
NPOI.SS.UserModel.ISheet sheet1 = excelBook.CreateSheet("列表模式");
NPOI.SS.UserModel.ISheet sheet2 = excelBook.CreateSheet("周历模式");
//列表模式（表1）添加数据
//创建表头行 CreateRow(0)
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("课头号");
row1.CreateCell(1).SetCellValue("课程名");
row1.CreateCell(2).SetCellValue("课程类型");
row1.CreateCell(3).SetCellValue("学习类型");
row1.CreateCell(4).SetCellValue("授课学院");
row1.CreateCell(5).SetCellValue("授课教师");
row1.CreateCell(6).SetCellValue("专业");
row1.CreateCell(7).SetCellValue("学分");
row1.CreateCell(8).SetCellValue("学时");
row1.CreateCell(9).SetCellValue("上课时间");
row1.CreateCell(10).SetCellValue("备注");
for (int i = 0; i < Courses.Count; i++)
{
NPOI.SS.UserModel.IRow row = sheet1.CreateRow(i + 1);
row.CreateCell(0).SetCellValue(Courses[i].LessonNum);
row.CreateCell(1).SetCellValue(Courses[i].LessonName);
row.CreateCell(2).SetCellValue(Courses[i].LessonType);
row.CreateCell(3).SetCellValue(Courses[i].LearninType);
row.CreateCell(4).SetCellValue(Courses[i].TeachingCollege);
row.CreateCell(5).SetCellValue(Courses[i].Teacher);
row.CreateCell(6).SetCellValue(Courses[i].Specialty);
row.CreateCell(7).SetCellValue(Courses[i].Credit);
row.CreateCell(8).SetCellValue(Courses[i].LessonHours);
row.CreateCell(9).SetCellValue(Courses[i].Time);
row.CreateCell(10).SetCellValue(Courses[i].Note);
}
//周历模式（表2）添加数据
NPOI.SS.UserModel.IRow firstRow = sheet2.CreateRow(0);
firstRow.Height = 20 * 20;
firstRow.CreateCell(0).SetCellValue("节次");
firstRow.CreateCell(1).SetCellValue("日");
firstRow.CreateCell(2).SetCellValue("一");
firstRow.CreateCell(3).SetCellValue("二");
firstRow.CreateCell(4).SetCellValue("三");
firstRow.CreateCell(5).SetCellValue("四");
firstRow.CreateCell(6).SetCellValue("五");
firstRow.CreateCell(7).SetCellValue("六");
List <IRow> rowList = new List <IRow>();
rowList.Add(firstRow);
for (int i = 0; i < 13; i++)
{
NPOI.SS.UserModel.IRow row = sheet2.CreateRow(i + 1);
row.Height = 20 * 20;
row.CreateCell(0).SetCellValue(i + 1);
rowList.Add(row);
}
for (int i = 0; i < Courses.Count; i++)
{
Course course = Courses[i];
List <List <Object> > temp = CourseTime.ParseClassTime(course);
for (int j = 0; j < temp.Count; j++)
{
string courseFirstWeek = ((int)temp[j][5]).ToString();
string courseLastWeek = ((int)temp[j][6]).ToString();
int courseBegin = (int)temp[j][2];
int courseEnd = (int)temp[j][3];
string weekDayByString = (string)temp[j][4];
int weekDay = CourseTime.WeekDayTrans(weekDayByString);
ICellStyle style = excelBook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Center;
style.VerticalAlignment = VerticalAlignment.Center;
style.WrapText = true;
ICell cell = rowList[courseBegin].CreateCell(weekDay);
cell.CellStyle = style;
cell.SetCellValue($"{course.LessonName}\n{courseFirstWeek}-{courseLastWeek}周");
sheet2.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(courseBegin, courseEnd, weekDay, weekDay));
}
}
if (!Directory.Exists(CQ.Api.AppDirectory + @"Export"))
{
Directory.CreateDirectory(CQ.Api.AppDirectory + @"Export");
}
using (FileStream fs = File.OpenWrite(CQ.Api.AppDirectory + $@"Export\{stuID}CourseTable.xls"))
{
excelBook.Write(fs);
}
}
/// <summary>
/// 导出
/// </summary>
/// <returns></returns>
public FileResult HUCountExportExcel()
{
var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW {
MENUCODE = "004001", SYSFLAG = ConfigCls.getSystemFlag()
});
//vMenu.MENUNAME 页面/菜单名称
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 50 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 10 * 256);
sheet1.SetColumnWidth(5, 10 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(vMenu.MENUNAME);
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位");
row.CreateCell(1).SetCellValue("总数");
row.CreateCell(2).SetCellValue("性别");
row.CreateCell(4).SetCellValue("固/兼职");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
row.CreateCell(2).SetCellValue("男");
row.CreateCell(3).SetCellValue("女");
row.CreateCell(4).SetCellValue("固职");
row.CreateCell(5).SetCellValue("兼职");
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 5));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 2, 3));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 4, 5));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 1, 1));
T_IPSFR_USER_SW sw = new T_IPSFR_USER_SW();
sw.TopORGNO = Request.Params["TopORGNO"];
var list = HUReportCls.getHUCountModel(sw);
int rowI = 0;
foreach (var item in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(item.ORGName);
row.CreateCell(1).SetCellValue(item.HUCount);
row.CreateCell(2).SetCellValue(item.Sex0Count);
row.CreateCell(3).SetCellValue(item.Sex1Count);
row.CreateCell(4).SetCellValue(item.Onstate0Count);
row.CreateCell(5).SetCellValue(item.Onstate1Count);
for (int i = 0; i < 6; i++)
{
if (i == 0)
{
row.GetCell(0).CellStyle = getCellStyleLeft(book);
}
else
{
row.GetCell(i).CellStyle = getCellStyleCenter(book);
}
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = vMenu.MENUNAME + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public FileResult PatrolRouteStatExportExcel()
{
//string BYORGNO = Request.Params["BYORGNO"];
//string TIMEBegin = Request.Params["TIMEBegin"];
//string TIMEEnd = Request.Params["TIMEEnd"];
//string HNamePhone = Request.Params["HNamePhone"];
//var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW { MENUCODE = "004002", SYSFLAG = ConfigCls.getSystemFlag() });
//vMenu.MENUNAME 页面/菜单名称
#region 导出统计表
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 10 * 256);
sheet1.SetColumnWidth(2, 10 * 256);
sheet1.SetColumnWidth(3, 10 * 256);
sheet1.SetColumnWidth(4, 20 * 256);
sheet1.SetColumnWidth(5, 10 * 256);
sheet1.SetColumnWidth(6, 10 * 256);
sheet1.SetColumnWidth(7, 10 * 256);
sheet1.SetColumnWidth(8, 20 * 256);
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue("巡检路线统计总表");
row.GetCell(0).CellStyle = getCellStyleTitle(book);
row = sheet1.CreateRow(1);
row.CreateCell(0).SetCellValue("单位");
row.CreateCell(1).SetCellValue("巡检次数(条)");
row.CreateCell(5).SetCellValue("巡检地点(个)");
row.GetCell(0).CellStyle = getCellStyleHead(book);
row.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
row = sheet1.CreateRow(2);
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.GetCell(1).CellStyle = getCellStyleHead(book);
row.GetCell(2).CellStyle = getCellStyleHead(book);
row.GetCell(3).CellStyle = getCellStyleHead(book);
row.GetCell(4).CellStyle = getCellStyleHead(book);
row.GetCell(5).CellStyle = getCellStyleHead(book);
row.GetCell(6).CellStyle = getCellStyleHead(book);
row.GetCell(7).CellStyle = getCellStyleHead(book);
row.GetCell(8).CellStyle = getCellStyleHead(book);
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 1, 4));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 1, 5, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(1, 2, 0, 0));
//var list = HUReportCls.getPatrolRouteStatModel(new PatrolRouteStat_SW { DateBegin = TIMEBegin, DateEnd = TIMEEnd, orgNo = BYORGNO, TopORGNO = BYORGNO });
PatrolRouteStat_SW sw = new PatrolRouteStat_SW();
sw.orgNo = Request.Params["BYORGNO"];
sw.TopORGNO = Request.Params["BYORGNO"];
sw.DateBegin = Request.Params["TIMEBegin"];
sw.DateEnd = Request.Params["TIMEEnd"];
var list = HUReportCls.getPatrolRouteStatModel(sw);
int rowI = 0;
foreach (var item in list)
{
row = sheet1.CreateRow(rowI + 3);
row.CreateCell(0).SetCellValue(item.ORGName);
row.CreateCell(1).SetCellValue(item.LineCount);
row.CreateCell(2).SetCellValue(item.LineCount0);
row.CreateCell(3).SetCellValue(item.LineCount1);
row.CreateCell(4).SetCellValue(item.LineCount2);
row.CreateCell(5).SetCellValue(item.PointCount);
row.CreateCell(6).SetCellValue(item.PointCount0);
row.CreateCell(7).SetCellValue(item.PointCount1);
row.CreateCell(8).SetCellValue(item.PointCount2);
for (int i = 0; i < 9; i++)
{
if (i == 0)
{
row.GetCell(0).CellStyle = getCellStyleLeft(book);
}
else
{
row.GetCell(i).CellStyle = getCellStyleCenter(book);
}
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = "巡检路线统计总表" + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
#endregion
}
public /*ActionResult*/ FileContentResult About()
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet();
#region 定义表头
//RGB自定义背景色
HSSFPalette palette = book.GetCustomPalette();
palette.SetColorAtIndex(HSSFColor.Pink.Index, (byte)54, (byte)96, (byte)146);
HSSFCellStyle cellStyle = (HSSFCellStyle)book.CreateCellStyle();
///（自定义背景色）单元格背景颜色 和FillPattern必须一起设置
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Pink.Index;
cellStyle.FillPattern = FillPattern.SolidForeground;
cellStyle.Alignment = HorizontalAlignment.Center; //水平居中
cellStyle.VerticalAlignment = VerticalAlignment.Center; //垂直居中
//设置黑色边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//创建字体
var cellStyleFont = (HSSFFont)book.CreateFont();
cellStyleFont.Color = HSSFColor.White.Index;
cellStyleFont.IsBold = true;
cellStyleFont.FontName = "宋体";
cellStyleFont.FontHeightInPoints = 11;
cellStyle.SetFont(cellStyleFont);
//设置单元格宽度
sheet.SetColumnWidth(0, 4000); //核销人
sheet.SetColumnWidth(1, 4000); //账号
sheet.SetColumnWidth(2, 5000); //核销时间
sheet.SetColumnWidth(3, 3500);
sheet.SetColumnWidth(4, 3500);
sheet.SetColumnWidth(5, 4000);
sheet.SetColumnWidth(6, 7000);
sheet.SetColumnWidth(7, 3500); //全面值
sheet.SetColumnWidth(8, 4500); //使用门槛
sheet.SetColumnWidth(9, 5000); //核销门店
sheet.SetColumnWidth(10, 10000); //appid
sheet.SetColumnWidth(11, 3500); //核销地址
sheet.SetColumnWidth(12, 3500); //核销id
// 第一行
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.Height = 350;
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("核销门店APPID");
row.CreateCell(11).SetCellValue("核销地址");
row.CreateCell(12).SetCellValue("核销ID");
for (int i = 0; i < 13; i++)
{
//设置高度
row.Height = 400;
row.Cells[i].CellStyle = cellStyle;
}
#endregion
//合并单元格
/**
* 第一个参数：从第几行开始合并
* 第二个参数：到第几行结束合并
* 第三个参数：从第几列开始合并
* 第四个参数：到第几列结束合并
**/
CellRangeAddress region = new CellRangeAddress(1, 2, 0, 0);
sheet.AddMergedRegion(region);
HSSFCellStyle cellStyleItem = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem.Alignment = HorizontalAlignment.Center; //水平居中
cellStyleItem.VerticalAlignment = VerticalAlignment.Center; //垂直居中
HSSFCellStyle cellStyleItem2 = (HSSFCellStyle)book.CreateCellStyle();
cellStyleItem2.VerticalAlignment = VerticalAlignment.Center; //垂直居中
for (int i = 1; i < 100; i++)
{
NPOI.SS.UserModel.IRow row2 = sheet.CreateRow(i);
row2.CreateCell(0).SetCellValue("收银员小吴");
row2.CreateCell(1).SetCellValue("13587639864");
row2.CreateCell(2).SetCellValue(DateTime.Now.ToString("yyyy/MM/dd HH:mm"));
row2.CreateCell(3).SetCellValue("1000");
row2.CreateCell(4).SetCellValue("180");
row2.CreateCell(5).SetCellValue("DJD84K6JFU");
row2.CreateCell(6).SetCellValue($"{DateTime.Now.ToString("yyyy-MM-dd")} ~ {DateTime.Now.AddDays(1).ToString("yyyy-MM-dd")}");
row2.CreateCell(7).SetCellValue("60");
row2.CreateCell(8).SetCellValue("满100元可用");
row2.CreateCell(9).SetCellValue("世纪新园中餐厅");
row2.CreateCell(10).SetCellValue(System.Guid.NewGuid().ToString());
var ignoreList = new List <int> {
5, 8, 9, 10
};
for (int j = 0; j < 11; j++)
{
if (ignoreList.Contains(j))
{
continue;
}
row2.Cells[j].CellStyle = cellStyleItem;
}
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
//Response.ContentType = "application/octet-stream";
//Response.AppendHeader("content-disposition", $"attachment;filename={DateTime.Now.ToString("yyyyMMddHHmmssfff")}.xls;");
//Response.BinaryWrite(ms.ToArray());
//book = null;
//ms.Close();
//ms.Dispose();
//return null;
return(File(ms.ToArray(), "application/octet-stream", string.Format("冷链食品处理详情{0}.xls", DateTime.Now.ToString("yyyyMMddHHmmss"))));
}
private HSSFWorkbook NPOIHelper(List <ExportAllScoreViewModel> models)
{
//创建Excel文件的对象
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
//添加一个sheet
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1");
ICellStyle cellstyle = book.CreateCellStyle();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
//sheet1.SetColumnWidth(0,100);
//sheet1.SetColumnWidth(1, 100);
//sheet1.SetColumnWidth(2, 100);
//sheet1.SetColumnWidth(3, 100);
//sheet1.SetColumnWidth(4, 100);
//sheet1.SetColumnWidth(5, 100);
//sheet1.SetColumnWidth(6, 100);
#region [头部设计]
//给sheet1添加第一行的头部标题
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
row1.CreateCell(0).SetCellValue("序号");
row1.CreateCell(1).SetCellValue("作品名称");
row1.CreateCell(2).SetCellValue("作者姓名");
row1.CreateCell(3).SetCellValue("单位名称");
row1.CreateCell(4).SetCellValue("评审指标");
row1.CreateCell(8).SetCellValue("总分");
row1.CreateCell(9).SetCellValue("评审意见");
row1.Cells.ForEach(ce =>
{
ce.CellStyle = cellstyle;
});
//给sheet1添加第二行的头部标题
NPOI.SS.UserModel.IRow row2 = sheet1.CreateRow(1);
row2.CreateCell(4).SetCellValue("教学设计(25)");
row2.CreateCell(5).SetCellValue("教学行为(25)");
row2.CreateCell(6).SetCellValue("教学效果(25)");
row2.CreateCell(7).SetCellValue("创新与实用(25)");
row2.Cells.ForEach(ce =>
{
ce.CellStyle = cellstyle;
});
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0)); // 序号
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1)); // 作品名称
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 4, 7));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 8, 8));
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 9, 9));
#endregion
var i = 0;
foreach (var item in models)
{
i++;
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(i);
rowtemp.CreateCell(1).SetCellValue(item.ResourceName);
rowtemp.CreateCell(2).SetCellValue(item.Author);
rowtemp.CreateCell(3).SetCellValue(item.AuthorCompany);
rowtemp.CreateCell(4).SetCellValue(item.Score1);
rowtemp.CreateCell(5).SetCellValue(item.Score2);
rowtemp.CreateCell(6).SetCellValue(item.Score3);
rowtemp.CreateCell(7).SetCellValue(item.Score4);
rowtemp.CreateCell(8).SetCellValue(item.Score);
rowtemp.CreateCell(9).SetCellValue(item.Comment);
}
NPOI.SS.UserModel.IRow rowtemp2 = sheet1.CreateRow(i + 3);
rowtemp2.CreateCell(1).SetCellValue("评审组:");
rowtemp2.CreateCell(2).SetCellValue(models.FirstOrDefault().GroupName);
rowtemp2.CreateCell(4).SetCellValue("评审专家:");
rowtemp2.CreateCell(8).SetCellValue("评审时间:");
return(book);
}
protected static MemoryStream GetMS(List <FishEntity.ProductQuoteVo> list)
{
MemoryStream ms = new MemoryStream();
NPOI.SS.UserModel.IWorkbook workbook = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("sheet1");
ICellStyle cellStyle = CreateCellStyle(workbook, 18, (short)FontBoldWeight.Bold);
NPOI.SS.UserModel.IRow row = sheet.CreateRow(0);
row.HeightInPoints = 26;
CreateCell(row, 0, "报盘", CellType.String, cellStyle);
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row.RowNum, row.RowNum, 0, 22));
row = sheet.CreateRow(1);
cellStyle = CreateCellStyle(workbook, 10, (short)FontBoldWeight.Bold);
CreateCell(row, 0, "鱼粉ID", CellType.String, cellStyle);
CreateCell(row, 1, "单位", CellType.String, cellStyle);
CreateCell(row, 2, "联系人", CellType.String, cellStyle);
CreateCell(row, 3, "国别", CellType.String, cellStyle);
CreateCell(row, 4, "货物情况", CellType.String, cellStyle);
CreateCell(row, 5, "品质", CellType.String, cellStyle);
CreateCell(row, 6, "最新报盘日期", CellType.String, cellStyle);
CreateCell(row, 7, "船期最快", CellType.String, cellStyle);
CreateCell(row, 8, "船期最慢", CellType.String, cellStyle);
CreateCell(row, 9, "重量", CellType.String, cellStyle);
CreateCell(row, 10, "数量", CellType.String, cellStyle);
CreateCell(row, 11, "蛋白", CellType.String, cellStyle);
CreateCell(row, 12, "TVN", CellType.String, cellStyle);
CreateCell(row, 13, "组胺", CellType.String, cellStyle);
CreateCell(row, 14, "FFA", CellType.String, cellStyle);
CreateCell(row, 15, "盐和砂", CellType.String, cellStyle);
CreateCell(row, 16, "灰分", CellType.String, cellStyle);
CreateCell(row, 17, "备注", CellType.String, cellStyle);
CreateCell(row, 18, "品牌", CellType.String, cellStyle);
CreateCell(row, 19, "汇率", CellType.String, cellStyle);
CreateCell(row, 20, "美金价", CellType.String, cellStyle);
CreateCell(row, 21, "人民币价", CellType.String, cellStyle);
if (list != null && list.Count > 0)
{
int rowidx = 1;
foreach (FishEntity.ProductQuoteVo model in list)
{
#region row
rowidx++;
row = sheet.CreateRow(rowidx);
CreateCell(row, 0, model.code);
CreateCell(row, 1, model.quotesupplier);
CreateCell(row, 2, model.quotelinkman);
CreateCell(row, 3, model.nature);
CreateCell(row, 4, model.goodsinfo);
CreateCell(row, 5, model.specification);
CreateCell(row, 6, model.quotedate);
CreateCell(row, 7, model.quotesaildatefast);
CreateCell(row, 8, model.quotesaildatelate);
CreateCell(row, 9, model.quoteweight.ToString("f2"));
CreateCell(row, 10, model.quotequantity.ToString());
CreateCell(row, 11, model.quote_protein.ToString());
CreateCell(row, 12, model.quote_tvn.ToString());
CreateCell(row, 13, model.quote_amine.ToString());
CreateCell(row, 14, model.quote_ffa.ToString());
CreateCell(row, 15, model.quote_sandsalt.ToString());
CreateCell(row, 16, model.quote_graypart.ToString());
CreateCell(row, 17, model.remark == null ? "": model.remark.ToString());
CreateCell(row, 18, model.brand);
CreateCell(row, 19, "");
CreateCell(row, 20, model.quotedollars.ToString());
CreateCell(row, 21, model.quotermb.ToString());
# endregion
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
/// <summary>
/// 将DataTable导出到Excel
/// </summary>
/// <param name="dt">DataTable</param>
/// <param name="fileName">仅文件名（非路径）</param>
/// <returns>返回Excel文件绝对路径</returns>
public void ExportDataSetToExcel(DataTable dt, string fileName, System.Web.HttpResponse Response)
{
#region 表头
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet hssfSheet = hssfworkbook.CreateSheet(fileName);
hssfSheet.DefaultColumnWidth = 13;
hssfSheet.SetColumnWidth(0, 25 * 256);
hssfSheet.SetColumnWidth(3, 20 * 256);
// 表头
NPOI.SS.UserModel.IRow tagRow0 = hssfSheet.CreateRow(0);
tagRow0.Height = 40 * 40;
ICell cell0 = tagRow0.CreateCell(0);
//设置单元格内容
cell0.SetCellValue("力诺瑞特制造工厂");
hssfSheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 7));
NPOI.SS.UserModel.IRow tagRow1 = hssfSheet.CreateRow(1);
tagRow1.Height = 20 * 20;
ICell cell1 = tagRow1.CreateCell(0);
//设置单元格内容
cell1.SetCellValue("计划外领料单");
hssfSheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, 7));
NPOI.SS.UserModel.ICellStyle tagStyle = hssfworkbook.CreateCellStyle();
tagStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle.VerticalAlignment = VerticalAlignment.Center;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
//tagStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
IFont font = hssfworkbook.CreateFont();
font.FontHeightInPoints = 16;
font.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font.FontName = "宋体";
tagStyle.SetFont(font);//HEAD 样式
cell0.CellStyle = tagStyle;
NPOI.SS.UserModel.ICellStyle tagStyle1 = hssfworkbook.CreateCellStyle();
tagStyle1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
tagStyle1.VerticalAlignment = VerticalAlignment.Center;
cell1.CellStyle = tagStyle1;
// 标题样式
NPOI.SS.UserModel.ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
#endregion
#region 表数据
// 表数据
for (int k = 0; k < 2; k++)
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i += 2)
{
row.CreateCell(i).SetCellValue(dr[i / 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle;
row.CreateCell(5).SetCellValue("");
row.GetCell(5).CellStyle = cellStyle;
row.CreateCell(7).SetCellValue("");
row.GetCell(7).CellStyle = cellStyle;
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 4, 5));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 6, 7));
}
// 表数据
for (int k = 2; k < dt.Rows.Count; k++)
{
if (k == dt.Rows.Count - 1)
{
DataRow drlast = dt.Rows[k];
NPOI.SS.UserModel.IRow rowlast = hssfSheet.CreateRow(k + 2);
rowlast.Height = 30 * 20;
for (int i = 0; i < dt.Columns.Count; i++)
{
rowlast.CreateCell(i).SetCellValue(drlast[i].ToString());
rowlast.GetCell(i).CellStyle = cellStyle;
}
//hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 1, 7));
}
else
{
DataRow dr = dt.Rows[k];
NPOI.SS.UserModel.IRow row = hssfSheet.CreateRow(k + 2);
row.Height = 30 * 20;
for (int i = 0; i < 4; i += 2)
{
row.CreateCell(i).SetCellValue(dr[i / 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
for (int i = 4; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dr[i - 2].ToString());
row.GetCell(i).CellStyle = cellStyle;
}
row.CreateCell(1).SetCellValue("");
row.GetCell(1).CellStyle = cellStyle;
row.CreateCell(3).SetCellValue("");
row.GetCell(3).CellStyle = cellStyle;
#region 合并单元格
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 0, 1));
hssfSheet.AddMergedRegion(new CellRangeAddress(k + 2, k + 2, 2, 3));
#endregion
}
}
//NPOI.SS.UserModel.IRow tagRow5 = hssfSheet.CreateRow(dt.Rows.Count + 5);
//tagRow5.Height = 20 * 20;
//ICell cell12 = tagRow5.CreateCell(0);
////设置单元格内容报告
//cell12.SetCellValue("Rev: 1.0");
//hssfSheet.AddMergedRegion(new CellRangeAddress(dt.Rows.Count + 5, dt.Rows.Count + 5, 0, 7));
//cell12.CellStyle = TelNoStyle;
#endregion
hssfSheet.PrintSetup.NoColor = true;
hssfSheet.PrintSetup.Landscape = true;
hssfSheet.PrintSetup.PaperSize = (short)PaperSize.A4;
//是否自适应界面
hssfSheet.FitToPage = true;
string uploadPath = HttpContext.Current.Request.PhysicalApplicationPath + "Mfg/Temp/";
if (!Directory.Exists(uploadPath))
{
Directory.CreateDirectory(uploadPath);
}
FileStream file = new FileStream(uploadPath + fileName + ".xls", FileMode.Create);
hssfworkbook.Write(file);
file.Close();
var basePath = VirtualPathUtility.AppendTrailingSlash(HttpContext.Current.Request.ApplicationPath);
//return (basePath + "Temp/" + fileName + ".xls");
string fileURL = HttpContext.Current.Server.MapPath((basePath + "Mfg/Temp/" + fileName + ".xls"));//文件路径，可用相对路径
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + HttpContext.Current.Server.UrlEncode(fileInfo.Name.ToString())); //文件名
Response.AddHeader("content-length", fileInfo.Length.ToString()); //文件大小
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.WriteFile(fileURL);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string passaord = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
#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
NPOI.SS.UserModel.ICellStyle dateStyle = workbook.CreateCellStyle();
NPOI.SS.UserModel.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();
if (string.IsNullOrEmpty(passaord) == false)
{
sheet.ProtectSheet(passaord);
}
}
#region 表头及样式
{
if (string.IsNullOrEmpty(strHeaderText) == false)
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
rowIndex += 1;
}
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
NPOI.SS.UserModel.ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
NPOI.SS.UserModel.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();
rowIndex += 1;
}
#endregion
}
#endregion
#region 填充内容
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
NPOI.SS.UserModel.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++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public FileResult CollectCountExportExcel()
{
string BYORGNO = Request.Params["BYORGNO"];
string TIMEBegin = Request.Params["TIMEBegin"];
string TIMEEnd = Request.Params["TIMEEnd"];
string HID = Request.Params["HID"];
string SYSTYPEVALUE = Request.Params["SYSTYPEVALUE"];
var vMenu = T_SYS_MENUCls.getModel(new T_SYS_MENU_SW {
MENUCODE = "004004", SYSFLAG = ConfigCls.getSystemFlag()
});
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("Sheet1"); //添加一个sheet
sheet1.IsPrintGridlines = true; //打印时显示网格线
sheet1.DisplayGridlines = true; //查看时显示网格线
IRow row = sheet1.CreateRow(0);
row.CreateCell(0).SetCellValue(vMenu.MENUNAME);
row.GetCell(0).CellStyle = getCellStyleTitle(book);
IEnumerable <T_IPSCOL_COLLECT_TypeCountModel> typeModel;
var list = T_IPSCOL_COLLECTCls.getModelCount(new T_IPSCOL_COLLECT_SW {
TopORGNO = BYORGNO, DateBegin = TIMEBegin, DateEnd = TIMEEnd
}, out typeModel);
int typeCount = 0;//计算类别有多少列
foreach (var v in typeModel)
{
typeCount++;
}
sheet1.SetColumnWidth(0, 30 * 256);
sheet1.SetColumnWidth(1, 20 * 256);
for (int i = 0; i < typeCount; i++)
{
sheet1.SetColumnWidth(i + 2, 20 * 256);
}
row = sheet1.CreateRow(1);
if (PublicCls.OrgIsZhen(BYORGNO) == false)
{
row.CreateCell(0).SetCellValue("单位");
}
else
{
row.CreateCell(0).SetCellValue("姓名");
}
row.CreateCell(1).SetCellValue("总数");
int indexType = 2;//从第二列开始
foreach (var v in typeModel)
{
row.CreateCell(indexType).SetCellValue(v.typeName);
indexType++;
}
for (int i = 0; i < typeCount + 2; i++)
{
row.GetCell(i).CellStyle = getCellStyleHead(book);
}
sheet1.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, typeCount + 1));
int rowI = 0; //数据行
foreach (var v in list) //循环获取数据
{
row = sheet1.CreateRow(rowI + 2);
if (string.IsNullOrEmpty(v.ORGName) == false)
{
row.CreateCell(0).SetCellValue(v.ORGName);
row.GetCell(0).CellStyle = getCellStyleLeft(book);
}
else
{
row.CreateCell(0).SetCellValue(v.HName);
row.GetCell(0).CellStyle = getCellStyleCenter(book);
}
row.CreateCell(1).SetCellValue(v.CollectCount);
row.GetCell(1).CellStyle = getCellStyleCenter(book);
int TypeI = 2;//类型开始列
foreach (var vv in v.TypeCountModel)
{
row.CreateCell(TypeI).SetCellValue(vv.typeCount);
row.GetCell(TypeI).CellStyle = getCellStyleCenter(book);
TypeI++;
}
rowI++;
}
// 写入到客户端
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
ms.Seek(0, SeekOrigin.Begin);
string fileName = vMenu.MENUNAME + DateTime.Now.ToString("yyyy-MM-dd") + ".xls";
return(File(ms, "application/vnd.ms-excel", fileName));
}
public string ExportAccountTemplate(SaveFileDialog sflg, DataTable dt)
{
string message = string.Empty;
try
{
string filename = sflg.FileName;
NPOI.SS.UserModel.IWorkbook book = null;
if (sflg.FilterIndex == 1)
{
book = new NPOI.HSSF.UserModel.HSSFWorkbook();
}
else
{
book = new NPOI.XSSF.UserModel.XSSFWorkbook();
}
//整体样式
ICellStyle style = book.CreateCellStyle();
style.FillPattern = FillPattern.NoFill;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Top;
style.WrapText = true;
IFont font = book.CreateFont();
font.FontHeightInPoints = 10;
font.FontName = "宋体";
style.SetFont(font);
//style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//一般样式
ICellStyle style_center = book.CreateCellStyle();
style_center.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style_center.VerticalAlignment = VerticalAlignment.Center;
style_center.WrapText = true;
//style_center.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style_center.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//一般样式
ICellStyle style_left = book.CreateCellStyle();
style_left.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
style_left.WrapText = true;
//style_left.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
//style_left.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.ISheet sheet = book.CreateSheet("清算信息汇总");
sheet.DefaultRowHeight = 20;
for (int columnNum = 0; columnNum < 11; columnNum++)
{
sheet.SetDefaultColumnStyle(columnNum, style);
}
int rowIndex = 0;
#region 添加表头
NPOI.SS.UserModel.IRow rowName = sheet.CreateRow(rowIndex);
rowName.CreateCell(0, CellType.String).SetCellValue("地区");
rowName.CreateCell(1, CellType.String).SetCellValue("序号");
rowName.CreateCell(2, CellType.String).SetCellValue("车辆生产企业");
rowName.CreateCell(3, CellType.String).SetCellValue("车辆型号");
rowName.CreateCell(4, CellType.Numeric).SetCellValue("企业申报推广数");
rowName.CreateCell(5, CellType.Numeric).SetCellValue("企业申请补助标准");
rowName.CreateCell(6, CellType.Numeric).SetCellValue("企业申请清算资金");
rowName.CreateCell(7, CellType.Numeric).SetCellValue("专家组核定的推广数");
rowName.CreateCell(8, CellType.Numeric).SetCellValue("专家组核定的补助标准");
rowName.CreateCell(9, CellType.Numeric).SetCellValue("应清算补助资金");
rowName.CreateCell(10, CellType.String).SetCellValue("核减原因");
rowIndex++;
#endregion
#region 添加数据
int dealerNum = 0;
//第一行总计
var countENTNum = (from DataRow row in dt.Rows select new { ent_num = row["ENT_NUM"] }).Sum(a => Convert.ToDecimal(a.ent_num));
var countENTMoney = (from DataRow row in dt.Rows select new { ent_money = row["SQBZBZ"] }).Sum(a => Convert.ToDecimal(a.ent_money));
var countENTCount = (from DataRow row in dt.Rows select new { ent_count = row["ENT_COUNT"] }).Sum(a => Convert.ToDecimal(a.ent_count));
var countAPPNum = (from DataRow row in dt.Rows select new { app_num = row["APP_NUM"] }).Sum(a => Convert.ToDecimal(a.app_num));
var countAPPMoney = (from DataRow row in dt.Rows select new { app_money = row["APP_MONEY"] }).Sum(a => Convert.ToDecimal(a.app_money));
var countAPPCount = (from DataRow row in dt.Rows select new { app_count = row["APP_COUNT"] }).Sum(a => Convert.ToDecimal(a.app_count));
IRow rowTotal = sheet.CreateRow(rowIndex);
ICell cellTotal = rowTotal.CreateCell(0);
cellTotal.SetCellValue("总计");
cellTotal.CellStyle = style_center;
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, 3));
rowTotal.CreateCell(4, CellType.String).SetCellValue(countENTNum.ToString());
rowTotal.CreateCell(5, CellType.String).SetCellValue(countENTMoney.ToString());
rowTotal.CreateCell(6, CellType.String).SetCellValue(countENTCount.ToString());
rowTotal.CreateCell(7, CellType.String).SetCellValue(countAPPNum.ToString());
rowTotal.CreateCell(8, CellType.String).SetCellValue(countAPPMoney.ToString());
rowTotal.CreateCell(9, CellType.String).SetCellValue(countAPPCount.ToString());
rowIndex++;
#region 循环省份写入
var ProvinceList = dt.AsEnumerable().Select(d => d.Field <string>("DQ")).Distinct().ToList();
for (int i = 0; i < ProvinceList.Count(); i++)
{
dealerNum = 1;
//地区 省份
int DealerCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().Count();
int ClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLXH")).Count();
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + ClxhCount + DealerCount, 0, 0));
IRow rowSFTotal = sheet.CreateRow(rowIndex);
ICell cellSF = rowSFTotal.CreateCell(0);
cellSF.SetCellValue(ProvinceList[i]);
#region 省份合计
// IRow rowSFTotal = sheet.CreateRow(rowIndex);
var dvShengfen = dt.DefaultView;
dvShengfen.RowFilter = String.Format("DQ='{0}'", ProvinceList[i]);
var dtShengfen = dvShengfen.ToTable();
var sum_ENT_NUM = Convert.ToDecimal(dtShengfen.Compute("sum(ENT_NUM)", "TRUE"));
var sum_SQBZBZ = dtShengfen.Compute("sum(SQBZBZ)", "TRUE");
var sum_ENT_COUNT = dtShengfen.Compute("sum(ENT_COUNT)", "TRUE");
var sum_APP_NUM = dtShengfen.Compute("sum(APP_NUM)", "TRUE");
var sum_APP_MONEY = dtShengfen.Compute("sum(APP_MONEY)", "TRUE");
var sum_APP_COUNT = dtShengfen.Compute("sum(APP_COUNT)", "TRUE");
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 1, 3));
ICell cellSFTotal = rowSFTotal.CreateCell(1);
cellSFTotal.SetCellValue("合计");
cellSFTotal.CellStyle = style_center;
rowSFTotal.CreateCell(4, CellType.String).SetCellValue(sum_ENT_NUM.ToString());
rowSFTotal.CreateCell(5, CellType.String).SetCellValue(sum_SQBZBZ.ToString());
rowSFTotal.CreateCell(6, CellType.String).SetCellValue(sum_ENT_COUNT.ToString());
rowSFTotal.CreateCell(7, CellType.String).SetCellValue(sum_APP_NUM.ToString());
rowSFTotal.CreateCell(8, CellType.String).SetCellValue(sum_APP_MONEY.ToString());
rowSFTotal.CreateCell(9, CellType.String).SetCellValue(sum_APP_COUNT.ToString());
rowIndex++;
#endregion
#region 照汽车生产企业写入
var DealerList = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i]).Select(d => d.Field <string>("CLSCQY")).Distinct().ToList();
for (int j = 0; j < DealerList.Count(); j++)
{
//企业序号
int DealerClxhCount = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).Select(d => d.Field <string>("CLXH")).Count();
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 1, 1));
IRow rowDealerTotal = sheet.CreateRow(rowIndex);
ICell cellDealerNum = rowDealerTotal.CreateCell(1);
cellDealerNum.SetCellValue(dealerNum);
//企业名称
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + DealerClxhCount, 2, 2));
ICell cellDealer = rowDealerTotal.CreateCell(2);
cellDealer.SetCellValue(DealerList[j]);
//企业小计
var dvDealer = dt.DefaultView;
dvDealer.RowFilter = String.Format("CLSCQY='{0}' AND DQ = '{1}'", DealerList[j], ProvinceList[i]);
var dtDealer = dvDealer.ToTable();
var dealer_ENT_NUM = dtDealer.Compute("sum(ENT_NUM)", "TRUE");
var dealer_SQBZBZ = dtDealer.Compute("sum(SQBZBZ)", "TRUE");
var dealer_ENT_COUNT = dtDealer.Compute("sum(ENT_COUNT)", "TRUE");
var dealer_APP_NUM = dtDealer.Compute("sum(APP_NUM)", "TRUE");
var dealer_APP_MONEY = dtDealer.Compute("sum(APP_MONEY)", "TRUE");
var dealer_APP_COUNT = dtDealer.Compute("sum(APP_COUNT)", "TRUE");
rowDealerTotal.CreateCell(3, CellType.String).SetCellValue("小计");
rowDealerTotal.CreateCell(4, CellType.String).SetCellValue(dealer_ENT_NUM.ToString());
rowDealerTotal.CreateCell(5, CellType.String).SetCellValue(dealer_SQBZBZ.ToString());
rowDealerTotal.CreateCell(6, CellType.String).SetCellValue(dealer_ENT_COUNT.ToString());
rowDealerTotal.CreateCell(7, CellType.String).SetCellValue(dealer_APP_NUM.ToString());
rowDealerTotal.CreateCell(8, CellType.String).SetCellValue(dealer_APP_MONEY.ToString());
rowDealerTotal.CreateCell(9, CellType.String).SetCellValue(dealer_APP_COUNT.ToString());
rowIndex++;
//按照车辆型号写入
var detailData = dt.AsEnumerable().Where(d => d.Field <string>("DQ") == ProvinceList[i] && d.Field <string>("CLSCQY") == DealerList[j]).CopyToDataTable();
for (int k = 0; k < detailData.Rows.Count; k++)
{
IRow rowDealer = sheet.CreateRow(rowIndex);
rowDealer.CreateCell(3, CellType.String).SetCellValue(detailData.Rows[k]["CLXH"].ToString());
rowDealer.CreateCell(4, CellType.String).SetCellValue(detailData.Rows[k]["ENT_NUM"].ToString());
rowDealer.CreateCell(5, CellType.String).SetCellValue(detailData.Rows[k]["SQBZBZ"].ToString());
rowDealer.CreateCell(6, CellType.String).SetCellValue(detailData.Rows[k]["ENT_COUNT"].ToString());
rowDealer.CreateCell(7, CellType.String).SetCellValue(detailData.Rows[k]["APP_NUM"].ToString());
rowDealer.CreateCell(8, CellType.String).SetCellValue(detailData.Rows[k]["APP_MONEY"].ToString());
rowDealer.CreateCell(9, CellType.String).SetCellValue(detailData.Rows[k]["APP_COUNT"].ToString());
rowDealer.CreateCell(10, CellType.String).SetCellValue(detailData.Rows[k]["APP_RESULT"].ToString());
rowDealer.Cells[7].CellStyle = style_left;
rowIndex++;
}
dealerNum++;
}
#endregion
}
for (int colNum = 0; colNum <= 10; colNum++)
{
int columnWidth = sheet.GetColumnWidth(colNum) / 256;
for (int rowNum = 0; rowNum < sheet.LastRowNum; rowNum++)
{
IRow currentRow = sheet.GetRow(rowNum);
ICell currentCell = currentRow.GetCell(colNum);
if (currentCell != null)
{
int length = Encoding.UTF8.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length + 1)
{
columnWidth = length + 1 > 254 ? 255 : length + 1;
}
currentRow.HeightInPoints = 20;
}
}
sheet.SetColumnWidth(colNum, columnWidth * 256);
}
sheet.ProtectSheet("1");
#endregion
#endregion
System.IO.MemoryStream ms = new System.IO.MemoryStream();
book.Write(ms);
book = null;
using (FileStream fs = new FileStream(filename, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
ms.Close();
ms.Dispose();
}
catch (Exception ex)
{
message = ex.Message;
}
return(message);
}
protected override void SetColumnHead(NPOI.SS.UserModel.ISheet sheet, ref int rowIndex)
{
if (this.ColumnHeadList.Count > 0)
{
// 冻结
sheet.CreateFreezePane(1, 4);
// 数据从第3行开始显示
rowIndex = rowIndex + 2;
// 所有列头居中
this.HeadStyle.Alignment = HorizontalAlignment.Center;
for (int i = 0; i < 2; i++)
{
IRow row = sheet.CreateRow(rowIndex);
foreach (ColumnsMapping cm in this.ColumnHeadList)
{
ICell cell = null;
if (i == 0)
{
if (cm.ColumnsIndex < 3 || cm.ColumnsIndex == 13 || cm.ColumnsIndex == 14)
{
// 合并行
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex + 1, cm.ColumnsIndex, cm.ColumnsIndex));
cell = row.CreateCell(cm.ColumnsIndex);
// 设置列宽
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
// 设置列头样式
cell.CellStyle = this.HeadStyle;
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex == 3 || cm.ColumnsIndex == 8)
{
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, cm.ColumnsIndex, cm.ColumnsIndex + 4));
cell = row.CreateCell(cm.ColumnsIndex);
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.CellStyle = this.HeadStyle;
if (cm.ColumnsIndex == 3)
{
cell.SetCellValue("成本");
}
else if (cm.ColumnsIndex == 8)
{
cell.SetCellValue("收入");
}
for (int j = 3; j <= 12; j++)
{
if (j == 3 || j == 8)
{
continue;
}
cell = row.CreateCell(j);
cell.CellStyle = this.HeadStyle;
}
}
}
else
{
if (cm.ColumnsIndex >= 3 && cm.ColumnsIndex <= 12)
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
SetColumnsWidth(sheet, cm.ColumnsIndex, cm.Width);
cell.SetCellValue(cm.ColumnsText);
}
else if (cm.ColumnsIndex < 3 || cm.ColumnsIndex == 13 || cm.ColumnsIndex == 14)
{
cell = row.CreateCell(cm.ColumnsIndex);
cell.CellStyle = this.HeadStyle;
}
}
}
rowIndex++;
}
}
}
/// <summary>
/// 合并单元格
/// </summary>
/// <param name="sheet">要合并单元格所在的sheet</param>
/// <param name="rowstart">开始行的索引</param>
/// <param name="rowend">结束行的索引</param>
/// <param name="colstart">开始列的索引</param>
/// <param name="colend">结束列的索引</param>
public static void SetCellRangeAddress(NPOI.SS.UserModel.ISheet sheet, int rowstart, int rowend, int colstart, int colend)
{
CellRangeAddress cellRangeAddress = new CellRangeAddress(rowstart, rowend, colstart, colend);
sheet.AddMergedRegion(cellRangeAddress);
}