public async Task <ActionResult> ExportToExcel(WIPDisplayDetailQueryViewModel model)
{
IList <Lot> lstLot = new List <Lot>();
using (LotQueryServiceClient client = new LotQueryServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime DESC",
Where = GetQueryCondition(model)
};
MethodReturnResult <IList <Lot> > result = client.Get(ref cfg);
if (result.Code == 0)
{
lstLot = 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 < lstLot.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("序号"); //序号
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("返修？"); //返修标志
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;
}
Lot obj = lstLot[j];
IRow rowData = ws.CreateRow(j + 1);
#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); //批次号
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); //产品号
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Quantity); //数量
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Grade); //等级
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Color); //花色
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.LineCode); //线别代码
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.EquipmentCode); //设备代码
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.LocationName); //车间名称
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.RouteName); //工艺流程
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.RouteStepName); //工步
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.StateFlag.GetDisplayName()); //批次状态
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.LotType.GetDisplayName()); //批次类型
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.HoldFlag ? StringResource.Yes : StringResource.No); //暂停标志
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.PackageNo); //包装号
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.RepairFlag); //返修次数
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.ReworkFlag); //返工次数
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.ReworkTime)); //返工时间
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Reworker); //返工操作人
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.CreateTime)); //创建时间
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Creator); //创建人
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间
cellData = rowData.CreateCell(rowData.Cells.Count);
cellData.CellStyle = style;
cellData.SetCellValue(obj.Editor); //编辑人
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "WIPDetailData.xls"));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = 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);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#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="strHeaderText">Excel表头文本（例如：车辆列表）</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 = System.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 NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 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);
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 = 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": //日期类型
System.DateTime dateV;
System.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);
}
}
public void ExportItemSourceToExcel(List <ProductiveTaskListModel> lists, string filePath)
{
if (lists.Count == 0)
{
return;
}
string part1 = lists[0].FProductionDate.ToLongDateString().ToString();
string fName = Path.Combine(filePath, $"{part1}生产任务清单.xls");
//如果存在此文件则添加1
if (File.Exists(fName))
{
fName = fName.Replace(".xls", DateTime.Now.ToString("HHmmss") + ".xls");
}
IWorkbook wb = new HSSFWorkbook();
//定义单元格格式
#region 缩小填充
ICellStyle fitStyle = wb.CreateCellStyle();
fitStyle.ShrinkToFit = true;
fitStyle.BorderBottom = BorderStyle.Thin; fitStyle.BorderLeft = BorderStyle.Thin; fitStyle.BorderTop = BorderStyle.Thin; fitStyle.BorderRight = BorderStyle.Thin;
IFont fitFont = wb.CreateFont();
fitFont.FontHeight = 14 * 20;
fitFont.FontName = "宋体";
fitStyle.SetFont(fitFont);
#endregion
#region 数字左对齐
ICellStyle numberStyle = wb.CreateCellStyle();
numberStyle.ShrinkToFit = true;
numberStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin;
numberStyle.Alignment = HorizontalAlignment.Right;
//numberStyle.DataFormat= HSSFDataFormat.GetBuiltinFormat("0");
IFont numberFont = wb.CreateFont();
numberFont.FontHeight = 14 * 20;
numberFont.FontName = "宋体";
numberStyle.SetFont(numberFont);
#endregion
#region 红色数字
ICellStyle warningStyle = wb.CreateCellStyle();
warningStyle.ShrinkToFit = true;
warningStyle.BorderBottom = BorderStyle.Thin; numberStyle.BorderLeft = BorderStyle.Thin; numberStyle.BorderTop = BorderStyle.Thin; numberStyle.BorderRight = BorderStyle.Thin;
IFont warningFont = wb.CreateFont();
warningFont.FontHeight = 14 * 20;
warningFont.FontName = "宋体";
warningFont.Color = HSSFColor.Red.Index;
warningStyle.SetFont(warningFont);
#endregion
#region 生产类型为返工时候，值为"灌"，设置样式
ICellStyle guanStyle = wb.CreateCellStyle();
guanStyle.Alignment = HorizontalAlignment.Center;
guanStyle.ShrinkToFit = true;
IFont guanFont = wb.CreateFont();
guanFont.Color = (short)FontColor.Red;
guanFont.FontName = "宋体";
guanFont.FontHeight = 15 * 20;
guanStyle.SetFont(guanFont);
#endregion
#region 标题样式
IFont titleFont = wb.CreateFont();
titleFont.FontName = "宋体";
titleFont.Boldweight = (short)FontBoldWeight.Bold;
titleFont.FontHeight = 24 * 20; //和行高一样？ 也是20倍
ICellStyle titleStyle = wb.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.SetFont(titleFont);
#endregion
IDataFormat format = wb.CreateDataFormat();
//cellsylenumber.DataFormat = format.GetFormat("0.00");
//获取不同的类型
var countGroup = lists.Where(m => m.FType != null).GroupBy(m => m.FType).Select(t => new { FType = t.Key, Count = t.Count() });
foreach (var item in countGroup)
{
var typedlists = lists.Where(n => n.FType == item.FType).ToList();
string previousBatch = string.Empty;
double batchQuantity = 0;
//string prevoousProductionModel = string.Empty;
ISheet sheet = wb.CreateSheet(item.FType);
sheet.ForceFormulaRecalculation = true;
sheet.SetColumnWidth(0, 20);
//6.设置列宽 SetColumnWidth(列的索引号从0开始, N * 256) 第二个参数的单位是1/256个字符宽度。例：将第四列宽度设置为了30个字符
//7.设置行高 Height的单位是1/20个点。例：设置高度为50个点 row0.Height = 50 * 20;
sheet.SetColumnWidth(0, (int)19 * 256);
sheet.SetColumnWidth(1, (int)14 * 256);
sheet.SetColumnWidth(2, (int)11.5 * 256);
sheet.SetColumnWidth(3, (int)4 * 256);
sheet.SetColumnWidth(4, (int)13 * 256);
sheet.SetColumnWidth(5, (int)25.35 * 256);
sheet.SetColumnWidth(6, (int)6 * 256);
sheet.SetColumnWidth(7, (int)19.49 * 256);
sheet.SetColumnWidth(8, (int)18.2 * 256);
sheet.SetColumnWidth(9, (int)5.35 * 256);
sheet.SetColumnWidth(10, (int)9.92 * 256);
sheet.SetColumnWidth(11, (int)6 * 256);
sheet.SetColumnWidth(12, (int)7 * 256);
sheet.SetColumnWidth(13, (int)18 * 256); //灌和做货
sheet.SetColumnWidth(14, 8 * 256); //差额
sheet.SetColumnWidth(15, 0 * 256); //第几次做货
sheet.SetColumnWidth(16, 15 * 256); //订单号
sheet.SetColumnWidth(17, 40 * 256); //唯一值
sheet.SetColumnWidth(18, 14 * 256); //安全编号
sheet.SetColumnWidth(19, 10 * 256); //样油重量
#region Logo
ExportImgToExcel(sheet, (HSSFWorkbook)wb);
#endregion
#region 标题
IRow row0 = sheet.CreateRow(0);
row0.Height = 30 * 20;
//CellRangeAddress region = new CellRangeAddress(0, 0, 0, 13);//CellRangeAddress rg = new CellRangeAddress(j + 2, j + 2, 8, 11);
//sheet.AddMergedRegion(region);
ICell cell = row0.CreateCell(5);
cell.SetCellValue(typedlists[0].FProductionDate.ToString("yyyy年MM月dd日") + typedlists[0].FType + "生产任务清单");
/*第一个参数：从第几行开始合并 第二个参数：到第几行结束合并 第三个参数：从第几列开始合并 第四个参数：到第几列结束合并 */
cell.CellStyle = titleStyle;
#endregion
#region 表头
IRow row1 = sheet.CreateRow(1);
row1.Height = (short)20.5 * 20;
var A = row1.CreateCell(0); A.SetCellValue("产品型号"); A.CellStyle = fitStyle;
var B = row1.CreateCell(1); B.SetCellValue("产品批号"); B.CellStyle = fitStyle;
var C = row1.CreateCell(2); C.SetCellValue("生产数量"); C.CellStyle = fitStyle;
var D = row1.CreateCell(3); D.SetCellValue("小料"); D.CellStyle = fitStyle;
var E = row1.CreateCell(4); E.SetCellValue("包装桶数"); E.CellStyle = fitStyle;
var F = row1.CreateCell(5); F.SetCellValue("包装桶"); F.CellStyle = fitStyle;
var G = row1.CreateCell(6); G.SetCellValue("客户代码"); G.CellStyle = fitStyle;
var H = row1.CreateCell(7); H.SetCellValue("标签型号"); H.CellStyle = fitStyle;
var I = row1.CreateCell(8); I.SetCellValue("备注"); I.CellStyle = fitStyle;
var J = row1.CreateCell(9); J.SetCellValue("留样"); J.CellStyle = fitStyle;
var K = row1.CreateCell(10); K.SetCellValue("合格证"); K.CellStyle = fitStyle;
var L = row1.CreateCell(11); L.SetCellValue("接收人"); L.CellStyle = fitStyle;
var M = row1.CreateCell(12); M.SetCellValue("残液"); M.CellStyle = fitStyle;
var N = row1.CreateCell(13); N.SetCellValue(""); N.CellStyle = guanStyle;//灌不灌
var O = row1.CreateCell(14); O.SetCellValue("差额"); O.CellStyle = fitStyle;
var P = row1.CreateCell(15); P.SetCellValue("生产次数"); P.CellStyle = numberStyle;
var Q = row1.CreateCell(16); Q.SetCellValue("订单号"); Q.CellStyle = fitStyle;
var R = row1.CreateCell(17); R.SetCellValue("唯一值"); R.CellStyle = fitStyle;
var S = row1.CreateCell(18); S.SetCellValue("安全编号"); S.CellStyle = fitStyle;
var T = row1.CreateCell(19); T.SetCellValue("样油重量(g)"); T.CellStyle = fitStyle;
#endregion
for (int j = 0; j < typedlists.Count; j++)
{
IRow row = sheet.CreateRow(j + 2);
row.Height = (short)20.5 * 20;
string currentBatch = typedlists[j].FBatchNo;
var A0 = row.CreateCell(0); var B0 = row.CreateCell(1); var C0 = row.CreateCell(2); var D0 = row.CreateCell(3); var M0 = row.CreateCell(12); var O0 = row.CreateCell(14);
if (currentBatch != previousBatch)
{
batchQuantity = Convert.ToDouble(typedlists[j].FQuantity) - Convert.ToDouble(typedlists[j].RowQuantity) + Convert.ToDouble(typedlists[j].FResidue);
A0.SetCellValue(typedlists[j].FitemName);
B0.SetCellValue(currentBatch);
C0.SetCellValue(Convert.ToDouble(typedlists[j].FQuantity));
D0.SetCellValue(typedlists[j].FHasSmallMaterial);
M0.SetCellValue(Convert.ToDouble(typedlists[j].FResidue.ToString("0.0")));
O0.CellStyle = fitStyle;
}
else
{
batchQuantity -= Convert.ToDouble(typedlists[j].RowQuantity);
O0.CellStyle = warningStyle;
}
O0.SetCellValue(batchQuantity);
A0.CellStyle = fitStyle; B0.CellStyle = fitStyle; C0.CellStyle = numberStyle; M0.CellStyle = numberStyle; D0.CellStyle = fitStyle;
var E0 = row.CreateCell(4); E0.SetCellValue(typedlists[j].FPackage); E0.CellStyle = fitStyle;
var F0 = row.CreateCell(5); F0.SetCellValue(typedlists[j].FBucketName); F0.CellStyle = fitStyle;
var G0 = row.CreateCell(6); G0.SetCellValue(typedlists[j].FOrgID); G0.CellStyle = fitStyle;
var H0 = row.CreateCell(7); H0.SetCellValue(typedlists[j].FLabel); H0.CellStyle = fitStyle;
#region 备注字体设置
var I0 = row.CreateCell(8);
string value = typedlists[j].FNote;
I0.SetCellValue(value);
ICellStyle noteStyle = wb.CreateCellStyle();
noteStyle.BorderBottom = BorderStyle.Thin; noteStyle.BorderLeft = BorderStyle.Thin; noteStyle.BorderTop = BorderStyle.Thin;
IFont noteFont = wb.CreateFont();
noteFont.FontName = "宋体";
int dataLength = UniversalFunction.GetLength(value);
if (dataLength >= 50)
{
noteFont.FontHeight = 7 * 20;
}
else if (dataLength >= 40)
{
noteFont.FontHeight = 8.5 * 20;
}
else if (dataLength >= 30)
{
noteFont.FontHeight = 10 * 20;
}
else if (dataLength >= 25)
{
noteFont.FontHeight = 12 * 20;
}
else
{
noteFont.FontHeight = 14 * 20;
}
noteStyle.SetFont(noteFont);
I0.CellStyle = noteStyle;
#endregion
#region 留样开始3列不赋值(赋值空都会有个空格)
ICellStyle blankStyle = wb.CreateCellStyle();
blankStyle.BorderLeft = BorderStyle.Thin; blankStyle.BorderTop = BorderStyle.Thin; blankStyle.BorderBottom = BorderStyle.Thin;
var J0 = row.CreateCell(9); J0.CellStyle = blankStyle;
var K0 = row.CreateCell(10); K0.CellStyle = blankStyle;
var L0 = row.CreateCell(11); L0.CellStyle = blankStyle;
#endregion
string productionNumber = typedlists[j].ProductionNumber > 0 && typedlists[j].ProductionNumber <= 3 ? $"第{typedlists[j].ProductionNumber}次生产" : "";
string guan = typedlists[j].ProductionType == "返工" ? "灌" : "";
var N0 = row.CreateCell(13); N0.SetCellValue(guan + " " + productionNumber); N0.CellStyle = guanStyle;//灌不灌
//var P0 = row.CreateCell(15); P0.SetCellValue(); P0.CellStyle = fitStyle; // 第多少次生产和 灌写入同一个单元格，本单元格已设置宽度为0，
var Q0 = row.CreateCell(16); Q0.SetCellValue(typedlists[j].FBillNo); Q0.CellStyle = fitStyle;
var R0 = row.CreateCell(17); R0.SetCellValue(UniversalFunction.ToHexString(typedlists[j].RowHashValue)); R0.CellStyle = fitStyle;
var S0 = row.CreateCell(18); S0.SetCellValue(typedlists[j].SafeCode); S0.CellStyle = fitStyle;
var T0 = row.CreateCell(19); T0.SetCellValue(Convert.ToInt32(typedlists[j].PaintSampleTotal)); T0.CellStyle = numberStyle;
previousBatch = currentBatch.Length != 0 ? currentBatch : previousBatch;
}
}
FileStream fs = new FileStream(fName, FileMode.Create); //新建才不会报错
wb.Write(fs); //会自动关闭流文件 //fs.Flush();
fs.Close();
}
void OnGUI()
{
GUIStyle style = new GUIStyle();
GUI.Label(new Rect(10, 10, 100, 30), "Sheet Name:");
MySheetName = GUI.TextField(new Rect(90, 10, 200, 30), MySheetName, 25);
style.richText = true;
FileStream MyAddress = new FileStream(Application.dataPath + "/My First Excel.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite, FileShare.ReadWrite);
if (GUI.Button(new Rect(300, 10, 200, 30), "Create Excel Files With " + "<color=red>NPOI</color>"))
{
HSSFWorkbook MyWorkbook = new HSSFWorkbook();
HSSFSheet Sheet01 = (HSSFSheet)MyWorkbook.CreateSheet(MySheetName);
for (int i = 0; i < 5; i++)
{
HSSFRow Row = (HSSFRow)Sheet01.CreateRow((short)i);
HSSFCell cell = (HSSFCell)Row.CreateCell((short)0);
cell.SetCellValue(MyCellArray[i]);
if (i < MyCellArray02.Count)
{
HSSFCell cell02 = (HSSFCell)Row.CreateCell((short)1);
cell02.SetCellValue(MyCellArray02[i]);
}
else
{
HSSFCell cell02 = (HSSFCell)Row.CreateCell((short)1);
cell02.SetCellValue("");
}
Row.RowStyle = MyWorkbook.CreateCellStyle();
Row.RowStyle.BorderBottom = BorderStyle.Double;
cell.CellStyle = MyWorkbook.CreateCellStyle();
cell.CellStyle.BorderRight = BorderStyle.Thin;
cell.CellStyle.BorderBottom = BorderStyle.Dashed;
cell.CellStyle.BottomBorderColor = HSSFColor.Red.Index;
HSSFFont MyFont = (HSSFFont)MyWorkbook.CreateFont();
MyFont.FontName = "Tahoma";
MyFont.FontHeightInPoints = 14;
MyFont.Color = HSSFColor.Gold.Index;
MyFont.Boldweight = (short)FontBoldWeight.Bold;
cell.CellStyle.SetFont(MyFont);
}
MyWorkbook.Write(MyAddress);
MyWorkbook.Close();
}
if (GUI.Button(new Rect(60, 120, 200, 30), "Reading Excel Files With " + "<color=yellow>NPOI</color>"))
{
ReadExcelEnable_NPOI = true;
ReadExcelEnable_ExcelDataReader = false;
MyString.Clear();
}
if (ReadExcelEnable_NPOI == true)
{
HSSFWorkbook MyBook;
using (FileStream MyAddress_Read = new FileStream(Application.dataPath + "/My First Excel.xls", FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
MyBook = new HSSFWorkbook(MyAddress_Read);
}
ISheet Sheet_Read = MyBook.GetSheet(MySheetName);
for (int row = 0; row <= Sheet_Read.LastRowNum; row++)
{
IRow Row_Read = Sheet_Read.GetRow(row);
for (int cells = 0; cells < Row_Read.LastCellNum; cells++)
{
GUI.Label(new Rect(400 + cells * 200, 100 + row * 40, 200, 20), Row_Read.GetCell(cells).ToString());
//Arabic And Persian Support
//GUI.Label(new Rect(400 + cells * 200,100+ row * 40, 200, 20),ArabicFixer.Fix(Row_Read.GetCell(cells).ToString()));
}
}
}
if (GUI.Button(new Rect(10, 160, 300, 30), "Reading Excel Files With " + "<color=yellow>ExcelDataReader</color>"))
{
ReadExcelEnable_ExcelDataReader = true;
ReadExcelEnable_NPOI = false;
MyString.Clear();
}
if (ReadExcelEnable_ExcelDataReader == true)
{
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(MyAddress);
DataSet Result = excelReader.AsDataSet();
excelReader.IsFirstRowAsColumnNames = true;
int NumberOfRows = Result.Tables[0].Rows.Count;
int NumberOfCols = Result.Tables[0].Columns.Count;
for (int i = 0; i < NumberOfRows; i++)
{
for (int j = 0; j < NumberOfCols; j++)
{
if (i == NumberOfRows - 1 && j == NumberOfCols - 1)
{
ReadExcelEnable_ExcelDataReader = false;
}
MyString.Add(Result.Tables[0].Rows[i][j].ToString());
}
}
}
for (int b = 0; b < MyString.Count; b++)
{
GUI.Label(new Rect(400, 100 + 20 * b, 200, 20), MyString[b]);
//Arabic And Persian Support
//GUI.Label(new Rect(400, 100 + 20 * b, 200, 20), ArabicFixer.Fix(MyString[b]));
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
try
{
var invisibleColumnCount = dtSource.Columns.Cast <DataColumn>().Count(item => item.Caption == "invisible");
for (int i = 0; i < invisibleColumnCount; i++)
{
dtSource.Columns.RemoveAt(0);
}
HSSFWorkbook workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "Robin van Persie"; //填加xls文件作者信息
si.ApplicationName = "for downloading."; //填加xls文件创建程序信息
si.LastAuthor = "RvP"; //填加xls文件最后保存者信息
si.Comments = "RvP"; //填加xls文件作者信息
si.Title = "Title"; //填加xls文件标题信息
si.Subject = "Subject"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName).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 表头及样式
{
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var 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));
}
#endregion
#region 列头及样式
{
var headerRow = sheet.CreateRow(1);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var 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);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var 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;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
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;
workbook.Close();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
catch (Exception ex)
{
LogBN.WriteLog(typeof(NpoiHelper), "导出Excel的方法MemoryStream Export 程序段的异常" + ex);
return(null);
}
}
private static void exportExcel <T>(this List <T> data, string path) where T : class
{
if (data == null)
{
throw new ArgumentNullException("data");
}
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet("Sheet1");
var props = typeof(T).GetProperties();
#region build row0
var row0 = sheet.CreateRow(0);
for (var i = 0; i < props.Length; i++)
{
var prop = props[i];
var attr = prop.GetCustomAttribute <DisplayAttribute>();
var name = attr != null ? attr.Name : prop.Name;
if (name.Equals("#Ignore#"))
{
continue;
}
row0.CreateCell(i).SetCellValue(name);
}
#endregion
var dataFormat = workbook.CreateDataFormat();
var cellStyle = workbook.CreateCellStyle();
#region build rows
for (int i = 0; i < data.Count; i++)
{
var row = sheet.CreateRow(i + 1);
for (var o = 0; o < props.Length; o++)
{
var prop = props[o];
var attr = prop.GetCustomAttribute <DisplayAttribute>();
var name = attr != null ? attr.Name : prop.Name;
if (name.Equals("#Ignore#"))
{
continue;
}
var value = prop.GetValue(data[i]);
var dataType = prop.PropertyType;
if (value == null)
{
row.CreateCell(o);
continue;
}
if (dataType == typeof(bool) || dataType == typeof(bool?))
{
switch ((bool)value)
{
case true:
row.CreateCell(o).SetCellValue("Yes");
break;
default:
row.CreateCell(o).SetCellValue("No");
break;
}
}
else if (dataType == typeof(int) || dataType == typeof(int?))
{
if (name == "Gender" || name == "Giới tính")
{
switch (value)
{
case 1:
row.CreateCell(o).SetCellValue("Male");
break;
case 0:
row.CreateCell(o).SetCellValue("Female");
break;
default:
row.CreateCell(o).SetCellValue(string.Empty);
break;
}
}
else
{
var intValue = 0;
if (int.TryParse(value.ToString(), out intValue))
{
row.CreateCell(o).SetCellValue(intValue);
}
}
}
else if (dataType == typeof(long) || dataType == typeof(long?))
{
long longValue = 0;
if (long.TryParse(value.ToString(), out longValue))
{
row.CreateCell(o).SetCellValue(longValue);
}
}
else if (dataType == typeof(double) || dataType == typeof(double?))
{
double doubleValue = 0;
if (double.TryParse(value.ToString(), out doubleValue))
{
row.CreateCell(o).SetCellValue(doubleValue);
}
}
else if (dataType == typeof(decimal) || dataType == typeof(decimal?))
{
double doubleValue = 0;
if (double.TryParse(value.ToString(), out doubleValue))
{
row.CreateCell(o).SetCellValue(doubleValue);
}
}
else if (dataType == typeof(DateTime) || dataType == typeof(DateTime?))
{
DateTime dateTimeValue;
if (DateTime.TryParse(value.ToString(), out dateTimeValue))
{
row.CreateCell(o).SetCellValue(dateTimeValue);
if (name == "Birthday" || name == "Ngày sinh")
{
var birthdaystyle = workbook.CreateCellStyle();
row.Cells[o - 1].CellStyle = birthdaystyle;
row.Cells[o - 1].CellStyle.DataFormat = dataFormat.GetFormat("dd-MM-yyyy");
}
else
{
row.Cells[o - 1].CellStyle = cellStyle;
row.Cells[o - 1].CellStyle.DataFormat = dataFormat.GetFormat("dd-MM-yyyy HH:mm:ss");
}
}
}
else
{
if (dataType == typeof(String))
{
if (value.ToString().Length > 32767)
{
value = value.ToString().Substring(0, 32767);
}
}
row.CreateCell(o).SetCellValue(value.ToString());
}
}
}
#endregion
using (var stream = new MemoryStream())
{
workbook.Write(stream);
using (var fileStream = File.Create(path))
{
stream.Seek(0, SeekOrigin.Begin);
stream.CopyTo(fileStream);
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
for (int i = 0; i < dtSource.Columns.Count;)
{
bool IsExists = false;
DataColumn column = dtSource.Columns[i];
for (int j = 0; j < excelConfig.ColumnEntity.Count; j++)
{
if (excelConfig.ColumnEntity[j].Column == column.ColumnName)
{
IsExists = true;
break;
}
}
if (!IsExists)
{
dtSource.Columns.Remove(column);
}
else
{
i++;
}
}
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 = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource.Columns.Count];
string[] arrColName = new string[dtSource.Columns.Count]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource.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.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
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);
}
}
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig.IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
HSSFWorkbook CreateWorkbook()
{
var workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("NPOI demo sheet");
//create styles
IFont fontBold = workbook.CreateFont();
fontBold.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightOrange.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium;
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
ICellStyle itemStyle = workbook.CreateCellStyle();
itemStyle.Alignment = HorizontalAlignment.Center;
itemStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium;
itemStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
itemStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
itemStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
ICellStyle summaryStyle = workbook.CreateCellStyle();
summaryStyle.Alignment = HorizontalAlignment.Center;
summaryStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
summaryStyle.FillPattern = FillPattern.SolidForeground;
summaryStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Medium;
summaryStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Medium;
summaryStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Medium;
summaryStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Medium;
summaryStyle.SetFont(fontBold);
//create header row
var headerRow = sheet.CreateRow(1);
var headerRow_Cell1 = headerRow.CreateCell(1);
headerRow_Cell1.SetCellValue("Header 1");
headerRow_Cell1.CellStyle = headerStyle;
var headerRow_Cell2 = headerRow.CreateCell(2);
headerRow_Cell2.SetCellValue("Header 2");
headerRow_Cell2.CellStyle = headerStyle;
var headerRow_Cell3 = headerRow.CreateCell(3);
headerRow_Cell3.SetCellValue("Header 3");
headerRow_Cell3.CellStyle = headerStyle;
var headerRow_Cell4 = headerRow.CreateCell(4);
headerRow_Cell4.SetCellValue("Header 4");
headerRow_Cell4.CellStyle = headerStyle;
int rowNo = 0;
//create items rows
for (var i = 1; i < 11; i++)
{
rowNo = i + 1;
var itemRow = sheet.CreateRow(rowNo);
var itemRow_Cell1 = itemRow.CreateCell(1);
itemRow_Cell1.SetCellValue("Row [" + i + "] Cell [1]");
itemRow_Cell1.CellStyle = itemStyle;
var itemRow_Cell2 = itemRow.CreateCell(2);
itemRow_Cell2.SetCellValue("Row [" + i + "] Cell [2]");
itemRow_Cell2.CellStyle = itemStyle;
var itemRow_Cell3 = itemRow.CreateCell(3);
itemRow_Cell3.SetCellValue("Row [" + i + "] Cell [3]");
itemRow_Cell3.CellStyle = itemStyle;
var itemRow_Cell4 = itemRow.CreateCell(4);
itemRow_Cell4.SetCellValue(10 * rowNo);
itemRow_Cell4.CellStyle = itemStyle;
}
//create summary row
var summaryRow = sheet.CreateRow(++rowNo);
var summaryRowCell = summaryRow.CreateCell(4);
summaryRowCell.SetCellType(CellType.Formula);
summaryRowCell.CellFormula = "sum(e3:e12)";
summaryRowCell.CellStyle = summaryStyle;
//define column width
sheet.SetColumnWidth(1, 4000);
sheet.SetColumnWidth(2, 4000);
sheet.SetColumnWidth(3, 4000);
sheet.SetColumnWidth(4, 4000);
return(workbook);
}
public static string FmtExcel(DateTime dt, string fileName)
{
HSSFWorkbook workBook = new HSSFWorkbook();
ISheet st = workBook.CreateSheet(dt.ToString("yyyy.M.d"));
//先创建cellstyle
ICellStyle r1s = workBook.CreateCellStyle();
r1s.FillForegroundColor = HSSFColor.Grey40Percent.Index;
r1s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
r1s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
r1s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
r1s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
IFont font = workBook.CreateFont();
font.IsBold = true;
font.FontName = "宋体";
font.FontHeightInPoints = 14;
r1s.SetFont(font);
r1s.FillPattern = FillPattern.SolidForeground;
ICellStyle r2s = workBook.CreateCellStyle();
r2s.FillForegroundColor = HSSFColor.LemonChiffon.Index;
r2s.FillPattern = FillPattern.SolidForeground;
r2s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
r2s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
r2s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
r2s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
IFont font2 = workBook.CreateFont();
font2.IsBold = true;
font2.FontHeightInPoints = 12;
font2.FontName = "宋体";
r2s.SetFont(font2);
ICellStyle r3s = workBook.CreateCellStyle();
r3s.FillForegroundColor = HSSFColor.Grey25Percent.Index;
r3s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
r3s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
r3s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
r3s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
r3s.FillPattern = FillPattern.SolidForeground;
IFont font3 = workBook.CreateFont();
font3.FontName = "宋体";
font3.IsBold = true;
font3.FontHeightInPoints = 10;
r3s.SetFont(font3);
ICellStyle rfs = workBook.CreateCellStyle();
//rfs.FillForegroundColor = HSSFColor.LemonChiffon.Index;
//rfs.FillPattern = FillPattern.SolidForeground;
rfs.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
rfs.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
rfs.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
rfs.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
ICellStyle rf0s = workBook.CreateCellStyle();
rf0s.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
rf0s.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
rf0s.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
rf0s.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
IFont frfs = workBook.CreateFont();
frfs.FontName = "宋体";
IFont frf0s = workBook.CreateFont();
frf0s.FontName = "宋体";
frfs.FontHeightInPoints = 10;
frf0s.FontHeightInPoints = 10;
frf0s.IsBold = true;
rf0s.SetFont(frf0s);
rfs.SetFont(frfs);
//榆社化石博物馆传感监测典型数据统计(2017 / 8 / 6)
IRow r0 = st.CreateRow(0);
ICellStyle r0s = workBook.CreateCellStyle();
IFont font0 = workBook.CreateFont();
font0.IsBold = true;
font0.FontHeightInPoints = 18;
font0.FontName = "黑体";
r0s.SetFont(font0);
ICell cr0 = r0.CreateCell(0);
cr0.SetCellValue("榆社化石博物馆传感监测典型数据统计(" + dt.ToString("yyyy/M/d") + ")");
cr0.CellStyle = r0s;
CellRangeAddress crar0 = new CellRangeAddress(0, 0, 0, 5);
st.AddMergedRegion(crar0);
st.CreateRow(1).Height = 200;
foreach (var g1 in ConfigData.GroupConfigRoot.GroupConfig1s)
{
IRow row = st.CreateRow(st.LastRowNum + 1);
ICell c0 = row.CreateCell(0);
c0.SetCellValue(g1.Name);
ICell c0sp = row.CreateCell(1);
c0sp.SetCellValue("");
ICell c1 = row.CreateCell(2);
c1.SetCellValue("数值");
ICell c2 = row.CreateCell(3);
c2.SetCellValue("节点号");
ICell c3 = row.CreateCell(4);
c3.SetCellValue("时间");
ICell c4 = row.CreateCell(5);
c4.SetCellValue("备注");
CellRangeAddress cra = new CellRangeAddress(c0.RowIndex, c0.RowIndex, c0.ColumnIndex, c0.ColumnIndex + 1);
st.AddMergedRegion(cra);
c0sp.CellStyle = r1s;
c0.CellStyle = r1s;
c1.CellStyle = r1s;
c2.CellStyle = r1s;
c3.CellStyle = r1s;
c4.CellStyle = r1s;
foreach (var g2 in g1.GroupConfigs)
{
IRow row2 = st.CreateRow(st.LastRowNum + 1);
ICell c20 = row2.CreateCell(0);
c20.CellStyle = r2s;
c20.SetCellValue(" " + g2.Name);
ICell c21 = row2.CreateCell(1);
c21.CellStyle = r2s;
c21.SetCellValue("");
ICell c22 = row2.CreateCell(2);
c22.CellStyle = r2s;
c22.SetCellValue("");
ICell c23 = row2.CreateCell(3);
c23.CellStyle = r2s;
c23.SetCellValue("");
ICell c24 = row2.CreateCell(4);
c24.CellStyle = r2s;
c24.SetCellValue("");
ICell c25 = row2.CreateCell(5);
c25.CellStyle = r2s;
c25.SetCellValue("");
CellRangeAddress cra20 = new CellRangeAddress(c20.RowIndex, c20.RowIndex, 0, 1);
st.AddMergedRegion(cra20);
foreach (var g3 in g2.GroupConfigs)
{
IRow row3 = st.CreateRow(st.LastRowNum + 1);
ICell c30 = row3.CreateCell(0);
c30.SetCellValue(" " + g3.Name);
c30.CellStyle = r3s;
ICell c31 = row3.CreateCell(1);
c31.SetCellValue("");
c31.CellStyle = r3s;
ICell c32 = row3.CreateCell(2);
c32.SetCellValue("");
c32.CellStyle = r3s;
ICell c33 = row3.CreateCell(3);
c33.SetCellValue("");
c33.CellStyle = r3s;
ICell c34 = row3.CreateCell(4);
c34.SetCellValue("");
c34.CellStyle = r3s;
ICell c35 = row3.CreateCell(5);
c35.SetCellValue("");
c35.CellStyle = r3s;
bool ir1 = true;
foreach (var s in g3.Sensors)
{
foreach (var f in s.Model.Fields)
{
if (!f.Realtime)
{
continue;
}
IRow rf = st.CreateRow(st.LastRowNum + 1);
ICell cf0 = rf.CreateCell(0);
cf0.CellStyle = rf0s;
if (ir1)
{
ir1 = false;
cf0.SetCellValue("包括:");
}
else
{
cf0.SetCellValue("");
}
ICell cf1 = rf.CreateCell(1);
cf1.SetCellValue(f.Name + "(" + f.Unit + ")");
cf1.CellStyle = rfs;
ICell cf2 = rf.CreateCell(2);
cf2.SetCellValue(f.GetValue(dt));
cf2.CellStyle = rfs;
ICell cf3 = rf.CreateCell(3);
cf3.SetCellValue(s.NodeId.ToString());
cf3.CellStyle = rfs;
ICell cf4 = rf.CreateCell(4);
cf4.SetCellValue(dt.ToString("H:mm"));
cf4.CellStyle = rfs;
ICell cf5 = rf.CreateCell(5);
cf5.SetCellValue(s.Comment);
cf5.CellStyle = rfs;
}
}
}
}
}
//自动宽度
st.AutoSizeColumn(1);
st.AutoSizeColumn(2);
st.AutoSizeColumn(3);
st.AutoSizeColumn(4);
st.SetColumnWidth(0, 4000);
st.SetColumnWidth(5, 9000);
//16_00榆社化石博物馆传感监测典型数据统计表2017.8.6.xls
using (FileStream fs = File.OpenWrite(fileName))
{
workBook.Write(fs);
}
return(fileName);
}
protected void btn_Marker_Export_Click(object sender, EventArgs e)
{
//string sql = "select PCON_BCODE,PCON_SCH,(PCON_PJID+'/'+PCON_PJNAME) AS PCON_PJNAME,d.BR_KPJE,PCON_ENGNAME,PCON_YFK,PCON_JINE,PCON_SPJE,PCON_STATE,PCON_JECHG,PCON_QTCHG,PCON_FHSJ,PCON_ERROR,PCON_CUSTMNAME,PCON_BALANCEACNT,CASE WHEN convert(decimal(18,2),PCON_BALANCEACNT)=0 THEN PCON_JINE ELSE PCON_BALANCEACNT END AS PCON_HTZJ,PCON_YZHTH,OTHER_MONUNIT,PCON_MONUNIT,PCON_KPJE,PCON_RESPONSER,PCON_NOTE,CR_PSZT,c.* from TBPM_CONPCHSINFO as a left join TBCR_CONTRACTREVIEW as b on a.PCON_REVID=b.CR_ID left join TBPM_DETAIL as c on a.PCON_BCODE=c.CM_CONTR left join TBPM_BUSBILLRECORD as d on a.PCON_BCODE=d.BR_HTBH order by PCON_BCODE";
string sql = "select PCON_CUSTMNAME,PCON_BCODE,TSA_ID,PCON_YZHTH,b.CM_PROJ,b.CM_ENGNAME,b.CM_MAP,b.CM_MATERIAL,b.CM_PRICE,b.CM_PRICE,b.CM_COUNT,b.CM_NUMBER,b.CM_UNIT,b.CM_WEIGHT,b.CM_ALL,b.CM_SIGN,b.CM_JIAO,b.CM_DUTY,b.CM_NOTE,b.CM_DTSJ,b.CM_CPRK,b.CM_TZFH,b.CM_CKSJ,b.CM_DFSM,PCON_YFK,CASE WHEN convert(decimal(18,2),PCON_BALANCEACNT)=0 THEN PCON_JINE ELSE PCON_BALANCEACNT END AS PCON_HTZJ,PCON_JINE,BR_KPJE from TBPM_CONPCHSINFO as a left join TBPM_DETAIL as b on a.PCON_BCODE=b.CM_CONTR left join (select BR_HTBH, sum(BR_KPJE)as BR_KPJE from TBPM_BUSBILLRECORD group by BR_HTBH) as d on a.PCON_BCODE=d.BR_HTBH order by PCON_BCODE";
DataTable dt = DBCallCommon.GetDTUsingSqlText(sql);
List <string> list = new List <string>();
string filename = "销售合同导出" + DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xls";
HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", System.Web.HttpContext.Current.Server.UrlEncode(filename)));
HttpContext.Current.Response.Clear();
//1.读取Excel到FileStream
using (FileStream fs = File.OpenRead(System.Web.HttpContext.Current.Server.MapPath("销售合同导出模板.xls")))
{
IWorkbook wk = new HSSFWorkbook(fs);
ISheet sheet0 = wk.GetSheetAt(0);
#region 写入数据
IFont font1 = wk.CreateFont();
font1.FontName = "仿宋"; //字体
font1.FontHeightInPoints = 11; //字号
ICellStyle cells = wk.CreateCellStyle();
cells.Alignment = HorizontalAlignment.CENTER;
cells.SetFont(font1);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
IRow row = sheet0.CreateRow(i + 3);
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(dr["PCON_CUSTMNAME"].ToString());
row.CreateCell(2).SetCellValue(dr["PCON_BCODE"].ToString());
row.CreateCell(3).SetCellValue(dr["TSA_ID"].ToString());
row.CreateCell(4).SetCellValue(dr["PCON_YZHTH"].ToString());
row.CreateCell(5).SetCellValue(dr["CM_PROJ"].ToString());
row.CreateCell(6).SetCellValue(dr["CM_ENGNAME"].ToString());
row.CreateCell(7).SetCellValue(dr["CM_MAP"].ToString());
row.CreateCell(8).SetCellValue(dr["CM_MATERIAL"].ToString());
row.CreateCell(9).SetCellValue(dr["CM_PRICE"].ToString());
row.CreateCell(10).SetCellValue(dr["CM_COUNT"].ToString());
row.CreateCell(11).SetCellValue(dr["CM_NUMBER"].ToString());
row.CreateCell(12).SetCellValue(dr["CM_UNIT"].ToString());
row.CreateCell(13).SetCellValue(dr["CM_WEIGHT"].ToString());
row.CreateCell(14).SetCellValue(dr["CM_ALL"].ToString());
row.CreateCell(15).SetCellValue(dr["CM_SIGN"].ToString());
row.CreateCell(16).SetCellValue(dr["CM_JIAO"].ToString());
row.CreateCell(17).SetCellValue(dr["CM_DUTY"].ToString());
row.CreateCell(18).SetCellValue(dr["CM_NOTE"].ToString());
row.CreateCell(19).SetCellValue(dr["CM_DTSJ"].ToString());
row.CreateCell(20).SetCellValue(dr["CM_CPRK"].ToString());
row.CreateCell(21).SetCellValue(dr["CM_TZFH"].ToString());
row.CreateCell(22).SetCellValue(dr["CM_CKSJ"].ToString());
row.CreateCell(23).SetCellValue(dr["CM_DFSM"].ToString());
if (!list.Contains(dr["PCON_BCODE"].ToString()))
{
row.CreateCell(24).SetCellValue(string.Format("{0:N2}", Convert.ToDecimal(dr["PCON_YFK"].ToString()) / Convert.ToDecimal(Convert.ToDecimal(dr["PCON_HTZJ"].ToString()) == 0 ? "1" : dr["PCON_HTZJ"].ToString()) * 100) + "%");
row.CreateCell(25).SetCellValue(dr["PCON_YFK"].ToString());
row.CreateCell(26).SetCellValue(string.Format("{0:N2}", (1 - Convert.ToDecimal(dr["PCON_YFK"].ToString()) / Convert.ToDecimal(Convert.ToDecimal(dr["PCON_HTZJ"].ToString()) == 0 ? "1" : dr["PCON_HTZJ"].ToString())) * 100) + "%");
row.CreateCell(27).SetCellValue((Convert.ToDecimal(dr["PCON_JINE"].ToString()) - Convert.ToDecimal(dr["PCON_YFK"].ToString())).ToString());
row.CreateCell(28).SetCellValue(dr["BR_KPJE"].ToString());
list.Add(dr["PCON_BCODE"].ToString());
}
else
{
row.CreateCell(24).SetCellValue("");
row.CreateCell(25).SetCellValue("");
row.CreateCell(26).SetCellValue("");
row.CreateCell(27).SetCellValue("");
row.CreateCell(28).SetCellValue("");
}
for (int j = 0; j < 29; j++)
{
row.Cells[j].CellStyle = cells;
}
}
#endregion
sheet0.ForceFormulaRecalculation = true;
MemoryStream file = new MemoryStream();
wk.Write(file);
HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
HttpContext.Current.Response.End();
}
}
//Datatable导出Excel
public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
//MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_successfully"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (Exception ex)
{
//ILog log = LogManager.GetLogger("Exception Log");
//log.Error(ex.Message + Environment.NewLine + ex.StackTrace);
//记录AuditTrail
// CCFS.Framework.BLL.AuditTrailBLL.LogAuditTrail(ex);
// MessageBox.Show(m_Common_ResourceManager.GetString("Export_to_excel_failed"), m_Common_ResourceManager.GetString("Information"), MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally { workbook = null; }
}
/// <summary>
/// 导出合并单元格的Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="savePath"></param>
/// <param name="dicMerge">合并数据集的格式</param>
/// <returns></returns>
public static bool ExportDataTable2MergeCellsInExcelByNPOI(DataTable dt, string savePath, Dictionary <string, Dictionary <string, int> > dicMerge)
{
System.IO.FileStream file;
try
{
file = new System.IO.FileStream(savePath, FileMode.Create);
}
catch { return(false); }
try
{
HSSFWorkbook workbook = CreateWorkBook();
ISheet sheetNew;
if (dt.TableName == "")
{
sheetNew = workbook.CreateSheet();
}
else
{
sheetNew = workbook.CreateSheet(dt.TableName);
}
//列名行
NPOI.SS.UserModel.IRow row = sheetNew.CreateRow(0);
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Columns[j].ColumnName);
}
//数据行
for (int i = 1; i <= dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow pRow = sheetNew.CreateRow(i);
for (int k = 0; k < dt.Columns.Count; k++)
{
string cellValue = dt.Rows[i - 1].ItemArray[k].ToString();
ICell curCell = pRow.CreateCell(k);
curCell.SetCellValue(cellValue);
}
}
int startRow = 1;
//合并路名列
foreach (KeyValuePair <string, Dictionary <string, int> > item in dicMerge)
{
string roadName = item.Key;
int sameRoadRowsCount = GetRowsFromDic(item.Value, startRow, ref sheetNew);
CellRangeAddress range = new CellRangeAddress(startRow, startRow + sameRoadRowsCount - 1, 0, 0);
sheetNew.AddMergedRegion(range);
//((HSSFSheet)sheetNew).SetEnclosedBorderOfRegion(range,NPOI.SS.UserModel.BorderStyle.Dotted, NPOI.HSSF.Util.HSSFColor.Red.Index);
startRow += sameRoadRowsCount;
}
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
style.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center;
workbook.Write(file);//如果存在同名文件夹，那么旧文件夹自动被覆盖！
return(true);
}
finally
{
file.Close();
}
}
/// <summary>
/// 导出Excel格式的充值码信息
/// </summary>
/// <param name="path">导出文件的路径（服务器端）</param>
/// <param name="orgid">机构id</param>
/// <param name="rsid">充值码设置项的id</param>
/// <returns></returns>
public string RechargeCode4Excel(string path, int orgid, int rsid)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
//xml配置文件
XmlDocument xmldoc = new XmlDocument();
string confing = WeiSha.Common.App.Get["ExcelInputConfig"].VirtualPath + "充值码.xml";
xmldoc.Load(WeiSha.Common.Server.MapPath(confing));
XmlNodeList nodes = xmldoc.GetElementsByTagName("item");
//创建工作簿对象
Song.Entities.RechargeSet rs = Gateway.Default.From <RechargeSet>().Where(RechargeSet._.Rs_ID == rsid).ToFirst <RechargeSet>();
ISheet sheet = hssfworkbook.CreateSheet(rs.Rs_Theme);
//sheet.DefaultColumnWidth = 30;
//创建数据行对象
IRow rowHead = sheet.CreateRow(0);
for (int i = 0; i < nodes.Count; i++)
{
rowHead.CreateCell(i).SetCellValue(nodes[i].Attributes["Column"].Value);
}
//生成数据行
ICellStyle style_size = hssfworkbook.CreateCellStyle();
style_size.WrapText = true;
WhereClip wc = RechargeCode._.Org_ID == orgid;
if (rsid >= 0)
{
wc.And(RechargeCode._.Rs_ID == rsid);
}
RechargeCode[] rcodes = Gateway.Default.From <RechargeCode>().Where(wc).OrderBy(RechargeCode._.Rc_CrtTime.Desc).ToArray <RechargeCode>();
for (int i = 0; i < rcodes.Length; i++)
{
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < nodes.Count; j++)
{
Type type = rcodes[i].GetType();
System.Reflection.PropertyInfo propertyInfo = type.GetProperty(nodes[j].Attributes["Field"].Value); //获取指定名称的属性
object obj = propertyInfo.GetValue(rcodes[i], null);
if (obj != null)
{
string format = nodes[j].Attributes["Format"] != null ? nodes[j].Attributes["Format"].Value : "";
string datatype = nodes[j].Attributes["DataType"] != null ? nodes[j].Attributes["DataType"].Value : "";
string defvalue = nodes[j].Attributes["DefautValue"] != null ? nodes[j].Attributes["DefautValue"].Value : "";
string value = "";
switch (datatype)
{
case "date":
DateTime tm = Convert.ToDateTime(obj);
value = tm > DateTime.Now.AddYears(-100) ? tm.ToString(format) : "";
break;
default:
value = obj.ToString();
break;
}
if (defvalue.Trim() != "")
{
foreach (string s in defvalue.Split('|'))
{
string h = s.Substring(0, s.IndexOf("="));
string f = s.Substring(s.LastIndexOf("=") + 1);
if (value.ToLower() == h.ToLower())
{
value = f;
}
}
}
row.CreateCell(j).SetCellValue(value);
}
}
}
FileStream file = new FileStream(path, FileMode.Create);
hssfworkbook.Write(file);
file.Close();
return(path);
}
static void Main(string[] args)
{
Console.WriteLine(DateTime.Now + " Start");
//查詢的值
var strEnter = Console.ReadLine();
if (strEnter == "" && args[0] == "")
{
Console.WriteLine("請輸入值");
return;
}
if (strEnter == "")
{
strEnter = args[0];
}
//var strEnter = "T0001";
//連線字串
SqlConnection conn = new SqlConnection();
string strconn = ConfigurationManager.ConnectionStrings["PracticeConnectionString"].ConnectionString;
conn.ConnectionString = strconn;
//設定EXCEL
IWorkbook workbook = new HSSFWorkbook();
try
{
using (conn)
{
//SP名稱
var procedure = "spSearch";
//SP參數
var values = new { Account = strEnter };
//執行結果
var results = conn.Query <Member>(procedure, values, commandType: CommandType.StoredProcedure).ToList();
if (results.Count == 0)
{
Console.WriteLine("資料不存在");
return;
}
//EXCEL sheet名稱
ISheet sheet = workbook.CreateSheet("Member");
//設定欄位名稱的cell style
var style = workbook.CreateCellStyle();
var font = workbook.CreateFont();
font.Underline = FontUnderlineType.Single;
font.Color = HSSFColor.White.Index;
style.SetFont(font);
//style.FillForegroundColor = HSSFColor.BlueGrey.Index;
style.FillPattern = FillPattern.SolidForeground;
var i = 0;
//跑表頭，根據欄位屬性來設定
foreach (var item in results)
{
var j = 0;
var row = sheet.CreateRow(i);
if (i == 0)
{
foreach (var property in item.GetType().GetProperties())
{
var cell = row.CreateCell(j);
cell.SetCellValue(property.Name);
cell.CellStyle = style;
sheet.AutoSizeColumn(j);
j++;
}
i++;
j = 0;
row = sheet.CreateRow(i);
}
//跑內容
foreach (var property in item.GetType().GetProperties())
{
row.CreateCell(j).SetCellValue(property.GetValue(item, null)?.ToString());
j++;
}
i++;
}
//若有檔案刪除定並重寫
File.Delete("D:/tmp/test.xlsx");
FileStream file = new FileStream("D:/tmp/test.xls", FileMode.Create);
workbook.Write(file);
file.Close();
}
}
catch (Exception)
{
throw;
}
Console.WriteLine(DateTime.Now + " 成功");
Console.ReadKey();
}
/// <summary>
/// 导出Excel内部功能实现，提供给前面三个方法进行调用
/// </summary>
/// <typeparam name="T">导出Excel的实体集合</typeparam>
/// <param name="list">需要导出Excel的List集合</param>
/// <param name="excelheader">Excel表头</param>
/// <param name="listGather">将list集合进行汇总后的信息</param>
/// <param name="excludeNoUsePropety">排除T实体中不需要的属性</param>
/// <returns></returns>
private static IWorkbook ExportToWorkBookExcel <T>(IList <T> list, List <string> excelheader, IList <T> listGather,
List <string> excludeNoUsePropety) where T : class, new()
{
IWorkbook iWorkbook = new HSSFWorkbook();
ISheet isSheet = iWorkbook.CreateSheet();
var typeOf = typeof(T);
var getProperties = typeOf.GetProperties();
//第一步：默认首先设置表头信息
IRow headRow = isSheet.CreateRow(0);
for (int i = 0; i < excelheader.Count; i++)
{
headRow.CreateCell(i, CellType.STRING).SetCellValue(excelheader[i]);
}
//第二步：遍历所有的读取出来的数据添加到Excel中，添加集合
var cellStyle = (HSSFCellStyle)iWorkbook.CreateCellStyle();
var format = (HSSFDataFormat)iWorkbook.CreateDataFormat();
for (int i = 0; i < list.Count; i++)
{
IRow temRow = isSheet.CreateRow(i + 1);
//遍历一行的每一个单元格，填写内容到单元格中
int num = 0; //控制在第几行显示第几个数据
//得到显示或者不显示的字段(整个实体类型的集合)，然后循环去显示
for (int j = 0; j < getProperties.Length; j++)
{
var proName = getProperties[j].Name;
if (excludeNoUsePropety.Count != 0)
{
if (excludeNoUsePropety.Contains(proName))
{
continue;
}
}
try
{
var newCell = temRow.CreateCell(num++);
var vName = typeOf.GetProperty(proName).GetValue(list[i], null);
if (vName != null)
{
Type type = vName.GetType(); //增加数据类型转换，int和decimal转换后在Excel里面显示正常
switch (type.FullName)
{
case "System.Int32":
newCell.SetCellValue(Convert.ToInt32(vName.ToString()));
cellStyle.DataFormat = format.GetFormat("0");
newCell.CellStyle = cellStyle;
break;
case "System.Decimal":
newCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
newCell.SetCellValue(Convert.ToDouble(vName.ToString()));
break;
default:
newCell.SetCellValue(vName.ToString());
break;
}
}
else
{
newCell.SetCellValue("");
}
}
catch (Exception exception)
{
throw new Exception("导出Excel出现错误了，错误原因：" + exception.Message);
}
}
}
//第三步：判断是否含有汇总信息，如果含有汇总信息，则显示在Excel的最下面，如果没有汇总信息，则直接返回
if (listGather.Count != 0)
{
IRow footRow = isSheet.CreateRow(list.Count + 1); //创建最后一行用来存放汇总信息
int num = 0; //控制在第几行显示第几个数据
for (int j = 0; j < getProperties.Length; j++)
{
var properName = getProperties[j].Name;
if (excludeNoUsePropety.Count != 0) //证明需要除掉传递进来不需要的属性
{
if (excludeNoUsePropety.Contains(properName))
{
continue;
}
}
var footCell = footRow.CreateCell(num++);
var vName = typeOf.GetProperty(properName).GetValue(listGather[0], null);
if (vName != null)
{
Type type = vName.GetType(); //增加数据类型转换
switch (type.FullName)
{
case "System.Int32":
footCell.SetCellValue(Convert.ToInt32(vName.ToString()));
cellStyle.DataFormat = format.GetFormat("0");
footCell.CellStyle = cellStyle;
break;
case "System.Decimal":
footCell.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
footCell.SetCellValue(Convert.ToDouble(vName.ToString()));
break;
default:
footCell.SetCellValue(vName.ToString());
break;
}
}
else
{
footCell.SetCellValue("");
}
}
}
return(iWorkbook);
}
public void setSheet(HSSFWorkbook newExcel, HSSFSheet sheet, List <LL_Table> dt, string title)
{
#region 设置行宽，列高
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 30 * 256);
sheet.SetColumnWidth(3, 30 * 256);
sheet.SetColumnWidth(4, 30 * 256);
sheet.SetColumnWidth(5, 30 * 256);
sheet.SetColumnWidth(6, 30 * 256);
sheet.SetColumnWidth(7, 30 * 256);
sheet.SetColumnWidth(8, 30 * 256);
sheet.SetColumnWidth(9, 30 * 256);
sheet.SetColumnWidth(10, 30 * 256);
sheet.SetColumnWidth(11, 30 * 256);
sheet.SetColumnWidth(12, 30 * 256);
sheet.DefaultRowHeight = 15 * 20;
#endregion
#region 设置字体
HSSFFont font_title = (HSSFFont)newExcel.CreateFont();
font_title.FontHeightInPoints = 10;
HSSFFont font_name = (HSSFFont)newExcel.CreateFont();
font_name.FontHeightInPoints = 7;
font_name.IsBold = true;
HSSFFont font_data = (HSSFFont)newExcel.CreateFont();
font_data.FontHeightInPoints = 7;
#endregion
#region 设置样式
//1、标题的样式
HSSFCellStyle style_title = (HSSFCellStyle)newExcel.CreateCellStyle();
style_title.Alignment = HorizontalAlignment.Center;
style_title.VerticalAlignment = VerticalAlignment.Center;
style_title.SetFont(font_title);
//2、字段名的样式
HSSFCellStyle style_name = (HSSFCellStyle)newExcel.CreateCellStyle();
style_name.Alignment = HorizontalAlignment.Center;
style_name.VerticalAlignment = VerticalAlignment.Center;
style_name.SetFont(font_name);
style_name.BorderTop = BorderStyle.Thin;
style_name.BorderBottom = BorderStyle.Thin;
style_name.BorderLeft = BorderStyle.Thin;
style_name.BorderRight = BorderStyle.Thin;
//3、批次的样式
HSSFCellStyle style_batch = (HSSFCellStyle)newExcel.CreateCellStyle();
style_batch.Alignment = HorizontalAlignment.Center;
style_batch.VerticalAlignment = VerticalAlignment.Center;
style_batch.FillPattern = FillPattern.SolidForeground;
style_batch.FillForegroundColor = HSSFColor.Grey40Percent.Index;
style_batch.SetFont(font_data);
style_batch.BorderTop = BorderStyle.Thin;
style_batch.BorderBottom = BorderStyle.Thin;
style_batch.BorderLeft = BorderStyle.Thin;
style_batch.BorderRight = BorderStyle.Thin;
//4、数据的样式
HSSFCellStyle style_data = (HSSFCellStyle)newExcel.CreateCellStyle();
style_data.Alignment = HorizontalAlignment.Center;
style_data.VerticalAlignment = VerticalAlignment.Center;
style_data.SetFont(font_data);
style_data.BorderTop = BorderStyle.Thin;
style_data.BorderBottom = BorderStyle.Thin;
style_data.BorderLeft = BorderStyle.Thin;
style_data.BorderRight = BorderStyle.Thin;
#endregion
#region 设置内容
//第一行 标题
HSSFRow row_title = (HSSFRow)sheet.CreateRow(0);
HSSFCell cell_title = (HSSFCell)row_title.CreateCell(0);
cell_title.SetCellValue(title);
cell_title.CellStyle = style_title;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 12)); //合并单元格(起始行，结束行，起始列，结束列)
//第二行 字段名
HSSFRow row_name = (HSSFRow)sheet.CreateRow(1);
HSSFCell cell_name_1 = (HSSFCell)row_name.CreateCell(0);
cell_name_1.SetCellValue("序号");
cell_name_1.CellStyle = style_name;
HSSFCell cell_name_2 = (HSSFCell)row_name.CreateCell(1);
cell_name_2.SetCellValue("学号");
cell_name_2.CellStyle = style_name;
HSSFCell cell_name_3 = (HSSFCell)row_name.CreateCell(2);
cell_name_3.SetCellValue("班级");
cell_name_3.CellStyle = style_name;
HSSFCell cell_name_4 = (HSSFCell)row_name.CreateCell(3);
cell_name_4.SetCellValue("姓名");
cell_name_4.CellStyle = style_name;
HSSFCell cell_name_5 = (HSSFCell)row_name.CreateCell(4);
cell_name_5.SetCellValue("节假日去向");
cell_name_5.CellStyle = style_name;
HSSFCell cell_name_6 = (HSSFCell)row_name.CreateCell(5);
cell_name_6.SetCellValue("离校时间");
cell_name_6.CellStyle = style_name;
HSSFCell cell_name_7 = (HSSFCell)row_name.CreateCell(6);
cell_name_7.SetCellValue("返校时间");
cell_name_7.CellStyle = style_name;
HSSFCell cell_name_8 = (HSSFCell)row_name.CreateCell(7);
cell_name_8.SetCellValue("离校方式");
cell_name_8.CellStyle = style_name;
HSSFCell cell_name_9 = (HSSFCell)row_name.CreateCell(8);
cell_name_9.SetCellValue("返校方式");
cell_name_9.CellStyle = style_name;
HSSFCell cell_name_10 = (HSSFCell)row_name.CreateCell(9);
cell_name_10.SetCellValue("离校去向地址");
cell_name_10.CellStyle = style_name;
HSSFCell cell_name_11 = (HSSFCell)row_name.CreateCell(10);
cell_name_11.SetCellValue("联系人");
cell_name_11.CellStyle = style_name;
HSSFCell cell_name_12 = (HSSFCell)row_name.CreateCell(11);
cell_name_12.SetCellValue("联系方式");
cell_name_12.CellStyle = style_name;
HSSFCell cell_name_13 = (HSSFCell)row_name.CreateCell(12);
cell_name_13.SetCellValue("签名确认");
cell_name_13.CellStyle = style_name;
//数据
int n = 2;
int i = 1;
foreach (LL_Table item in dt)
{
HSSFRow row = (HSSFRow)sheet.CreateRow(n++);//写入行
row.CreateCell(0).SetCellValue(i++);
row.CreateCell(1).SetCellValue(item.ST_Num);
row.CreateCell(2).SetCellValue(item.ST_Class);
row.CreateCell(3).SetCellValue(item.ST_Name);
row.CreateCell(4).SetCellValue(item.ST_Go);
row.CreateCell(5).SetCellValue(item.TimeLeave);
row.CreateCell(6).SetCellValue(item.TimeBack);
row.CreateCell(7).SetCellValue(item.LeaveWay);
row.CreateCell(8).SetCellValue(item.BackWay);
row.CreateCell(9).SetCellValue(item.Address);
row.CreateCell(10).SetCellValue(item.Contact);
row.CreateCell(11).SetCellValue(item.Tel);
row.CreateCell(12).SetCellValue("");
foreach (ICell cell in row)
{
if (cell.ColumnIndex == 0)
{
cell.CellStyle = style_batch;
}
else
{
cell.CellStyle = style_data;
}
}
}
#endregion
}
public ActionResult ExportStats(Schools school, UserGroup ugroup, int year)
{
var stats = new ClassStatistics {
school = school, usergroup = ugroup, year = year
};
stats.CalculateStats(repository);
var ms = new MemoryStream();
using (var fs =
new FileStream(
AppDomain.CurrentDomain.BaseDirectory + "/Content/templates/NPOITemplate.xls",
FileMode.Open, FileAccess.Read, FileShare.Read))
{
var templateWorkbook = new HSSFWorkbook(fs, true);
var sheet = templateWorkbook.CreateSheet(school.ToString());
// create fonts
var boldStyle = templateWorkbook.CreateCellStyle();
var boldFont = templateWorkbook.CreateFont();
boldFont.Boldweight = (short)FontBoldWeight.BOLD;
boldStyle.SetFont(boldFont);
var rowcount = 0;
var row = sheet.CreateRow(rowcount++);
var colcount = 0;
// show general stats first
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Malaysian"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Foreigners"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("SubTotal"));
colcount += 2;
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total"));
row = sheet.CreateRow(rowcount++);
for (int i = 0; i < 3; i++)
{
row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M"));
row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F"));
}
row = sheet.CreateRow(rowcount++);
row.CreateCell(0, CellType.NUMERIC).SetCellValue(stats.msian_male);
row.CreateCell(1, CellType.NUMERIC).SetCellValue(stats.msian_female);
row.CreateCell(2, CellType.NUMERIC).SetCellValue(stats.foreign_male);
row.CreateCell(3, CellType.NUMERIC).SetCellValue(stats.foreign_female);
row.CreateCell(4, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male);
row.CreateCell(5, CellType.NUMERIC).SetCellValue(stats.msian_female + stats.foreign_female);
row.CreateCell(6, CellType.NUMERIC).SetCellValue(stats.msian_male + stats.foreign_male + stats.msian_female + stats.foreign_female);
foreach (var entry in stats.collections)
{
// class row
row = sheet.CreateRow(rowcount++);
row.CreateCell(0).SetCellValue(SecurityElement.Escape(entry.name));
row.GetCell(0).CellStyle = boldStyle;
// header row1
row = sheet.CreateRow(rowcount++);
colcount = 0;
foreach (var race in entry.GetList())
{
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape(race.name));
colcount += 2;
}
row.CreateCell(colcount).SetCellValue(SecurityElement.Escape("Total"));
// header row2
row = sheet.CreateRow(rowcount++);
for (int i = 0; i < entry.GetList().Count(); i++)
{
row.CreateCell(i * 2).SetCellValue(SecurityElement.Escape("M"));
row.CreateCell(i * 2 + 1).SetCellValue(SecurityElement.Escape("F"));
}
// stats row
row = sheet.CreateRow(rowcount++);
colcount = 0;
foreach (var race in entry.GetList())
{
row.CreateCell(colcount++).SetCellValue(race.male);
row.CreateCell(colcount++).SetCellValue(race.female);
}
row.CreateCell(colcount).SetCellValue(entry.GetList().Sum(x => x.male + x.female));
}
// delete first sheet
templateWorkbook.RemoveSheetAt(0);
templateWorkbook.Write(ms);
}
// return created file path);
return(File(ms.ToArray(), "application/vnd.ms-excel", string.Format("Statistics_{0}_{1}.xls", ugroup, school)));
}
public void PrintMnExcelFile()
{
string name = Request.QueryString["name"];
string sex = Request.QueryString["sex"];
string sf = Request.QueryString["sf"];
string sfz = Request.QueryString["sfz"];
string dept = Request.QueryString["dept"];
string ids = Request.QueryString["ids"];
string sum = Request.QueryString["sum"];
string result = "";
try
{
int beginRow = 4; int beginCol = 0;
int endRow = 46; int endCol = 7;
//有数据开始生成表格
string save_path = "", tick = "";
string temp_path = Server.MapPath("~/UploadFile/xls_files");//生成的文件存放路径
if (!Directory.Exists(temp_path))
{
Directory.CreateDirectory(temp_path);
}
FileStream fs = new FileStream(Server.MapPath("~/Excel/美年体检最新报价.xls"), FileMode.OpenOrCreate, FileAccess.ReadWrite);
var hssfworkbook = new HSSFWorkbook(fs);
var dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
var si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
var style_border = hssfworkbook.CreateCellStyle();
var font = hssfworkbook.CreateFont();
font.FontHeight = 16 * 16;
style_border.VerticalAlignment = VerticalAlignment.CENTER;
style_border.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style_border.BorderBottom = CellBorderType.THIN;
style_border.BottomBorderColor = HSSFColor.BLACK.index;
style_border.BorderLeft = CellBorderType.THIN;
style_border.LeftBorderColor = HSSFColor.BLACK.index;
style_border.BorderRight = CellBorderType.THIN;
style_border.RightBorderColor = HSSFColor.BLACK.index;
style_border.BorderTop = CellBorderType.THIN;
style_border.TopBorderColor = HSSFColor.BLACK.index;
style_border.SetFont(font);
ISheet sheet1 = hssfworkbook.GetSheet("Sheet1");
IRow row1 = sheet1.GetRow(1);
ICell cellName = row1.GetCell(0);
string biaotou = "姓名：" + name + " 身份：" + sf + " 身份证：" + sfz + " 单位：" + dept + " 性别：" + sex;
cellName.SetCellValue(biaotou);
IRow rowend = sheet1.GetRow(endRow);
ICell cellend2 = rowend.GetCell(4);
string hhjjh = cellend2.ToString();
ICell cellend = rowend.GetCell(5);
cellend.SetCellValue(sum);
string[] idArray = ids.Split(',');
foreach (var item in idArray)
{
int id = int.Parse(item);
IRow rowID = sheet1.GetRow(id);
rowID.ZeroHeight = true;//行的高度为0,隐藏 等同于删除行
}
sheet1.ForceFormulaRecalculation = true;
tick = "美年体检模板表" + DateTime.Now.ToString("yyMMddhhmmss");;
string vname = ".xls";
save_path = temp_path + "\\" + tick + vname;
var file = new FileStream(save_path, FileMode.Create);
hssfworkbook.Write(file);
file.Dispose();
file.Close();
result = "{\"state\":\"yes\",\"msg\":\"~/UploadFile/xls_files/" + tick + vname + "\"}";
// result = "{\"state\":\"yes\",\"msg\":\"~/Excel/" + tick + vname + "\"}";
}
catch (Exception ex) {
result = "{\"state\":\"no\",\"msg\":\"生成报表失败错误信息:" + ex.Message + "!\"}";
throw (ex);
}
Response.Write(result);
}
public void Report(string FileName)
{
DivideByFactory();
int pos = 0;
//Export to excel
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
////create a entry of DocumentSummaryInformation
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI Team";
hssfworkbook.DocumentSummaryInformation = dsi;
////create a entry of SummaryInformation
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Subject = "NPOI SDK Example";
hssfworkbook.SummaryInformation = si;
#region Create fonts and styles
HSSFFont HeaderF1 = hssfworkbook.CreateFont();
HeaderF1.FontHeightInPoints = 11;
HeaderF1.Boldweight = 11 * 256;
HeaderF1.FontName = "Calibri";
HSSFFont HeaderF2 = hssfworkbook.CreateFont();
HeaderF2.FontHeightInPoints = 10;
HeaderF2.Boldweight = 10 * 256;
HeaderF2.FontName = "Calibri";
HSSFFont HeaderF3 = hssfworkbook.CreateFont();
HeaderF3.FontHeightInPoints = 9;
HeaderF3.Boldweight = 9 * 256;
HeaderF3.FontName = "Calibri";
HSSFFont SimpleF = hssfworkbook.CreateFont();
SimpleF.FontHeightInPoints = 10;
SimpleF.FontName = "Calibri";
HSSFCellStyle SimpleCS = hssfworkbook.CreateCellStyle();
SimpleCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
SimpleCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
SimpleCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderRight = HSSFCellStyle.BORDER_THIN;
SimpleCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleCS.BorderTop = HSSFCellStyle.BORDER_THIN;
SimpleCS.TopBorderColor = HSSFColor.BLACK.index;
SimpleCS.SetFont(SimpleF);
HSSFCellStyle CountDecCS = hssfworkbook.CreateCellStyle();
CountDecCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0.000");
CountDecCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountDecCS.BottomBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountDecCS.LeftBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountDecCS.RightBorderColor = HSSFColor.BLACK.index;
CountDecCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountDecCS.TopBorderColor = HSSFColor.BLACK.index;
CountDecCS.SetFont(SimpleF);
HSSFCellStyle CountCS = hssfworkbook.CreateCellStyle();
CountCS.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("### ### ##0");
CountCS.BorderBottom = HSSFCellStyle.BORDER_THIN;
CountCS.BottomBorderColor = HSSFColor.BLACK.index;
CountCS.BorderLeft = HSSFCellStyle.BORDER_THIN;
CountCS.LeftBorderColor = HSSFColor.BLACK.index;
CountCS.BorderRight = HSSFCellStyle.BORDER_THIN;
CountCS.RightBorderColor = HSSFColor.BLACK.index;
CountCS.BorderTop = HSSFCellStyle.BORDER_THIN;
CountCS.TopBorderColor = HSSFColor.BLACK.index;
CountCS.SetFont(SimpleF);
HSSFCellStyle SimpleHeaderCS = hssfworkbook.CreateCellStyle();
SimpleHeaderCS.BorderBottom = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.BottomBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderLeft = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.LeftBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderRight = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.RightBorderColor = HSSFColor.BLACK.index;
SimpleHeaderCS.BorderTop = HSSFCellStyle.BORDER_MEDIUM;
SimpleHeaderCS.TopBorderColor = HSSFColor.BLACK.index;
//SimpleHeaderCS.WrapText = true;
SimpleHeaderCS.SetFont(HeaderF3);
#endregion Create fonts and styles
HSSFCell Cell1;
if (FrontsProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата создания");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["DocDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsProfilDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (FrontsTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Фасады, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата создания");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Фасад");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Вставка-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет наполнителя-2");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Квадратура");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < FrontsTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["DocDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr36"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr37"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr38"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Expr1"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToDouble(FrontsTPSDT.Rows[i]["Square"]));
Cell1.CellStyle = CountDecCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(FrontsTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(FrontsTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorProfilDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, Профиль");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата создания");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorProfilDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["DocDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorProfilDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorProfilDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
pos = 0;
if (DecorTPSDT.Rows.Count > 0)
{
HSSFSheet sheet1 = hssfworkbook.CreateSheet("Декор, ТПС");
sheet1.PrintSetup.PaperSize = (short)PaperSizeType.A4;
sheet1.SetMargin(HSSFSheet.LeftMargin, (double).12);
sheet1.SetMargin(HSSFSheet.RightMargin, (double).07);
sheet1.SetMargin(HSSFSheet.TopMargin, (double).20);
sheet1.SetMargin(HSSFSheet.BottomMargin, (double).20);
pos += 2;
int ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Клиент ЗОВ");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ заказа");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Дата создания");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Бухг.наим.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Инв.номер");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Артикул");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Цвет");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Длина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Высота");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ширина");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Кол-во");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("Ед.изм.");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("ID упаковки");
Cell1.CellStyle = SimpleHeaderCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue("№ упаковки");
Cell1.CellStyle = SimpleHeaderCS;
pos++;
for (int i = 0; i < DecorTPSDT.Rows.Count; i++)
{
ColIndex = 0;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["ZOVClientName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["OrderNumber"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["DocDateTime"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["AccountingName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["InvNumber"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["TechStoreName"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Expr35"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Length"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Height"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Width"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["Count"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(DecorTPSDT.Rows[i]["Measure"].ToString());
Cell1.CellStyle = SimpleCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackageID"]));
Cell1.CellStyle = CountCS;
Cell1 = sheet1.CreateRow(pos).CreateCell(ColIndex++);
Cell1.SetCellValue(Convert.ToInt32(DecorTPSDT.Rows[i]["PackNumber"]));
Cell1.CellStyle = CountCS;
pos++;
}
}
string tempFolder = System.Environment.GetEnvironmentVariable("TEMP");
FileInfo file = new FileInfo(tempFolder + @"\" + FileName + ".xls");
int j = 1;
while (file.Exists == true)
{
file = new FileInfo(tempFolder + @"\" + FileName + "(" + j++ + ").xls");
}
FileStream NewFile = new FileStream(file.FullName, FileMode.Create);
hssfworkbook.Write(NewFile);
NewFile.Close();
System.Diagnostics.Process.Start(file.FullName);
ClearReport();
}
/// <summary>
/// 表头及内容样式
/// </summary>
/// <param name="sheet"></param>
static void SheetHeadStyle(ISheet ws, HSSFWorkbook workBook, int rowCount, int colCount)
{
//
ws.AutoSizeColumn(0); //标题行自适应宽度
ICellStyle styleHeadNull = workBook.CreateCellStyle(); //为了....
IFont fontHeadNull = workBook.CreateFont(); ///
ICellStyle styleHead = workBook.CreateCellStyle();
IFont fontHead = workBook.CreateFont(); //新建一个字体样式对象
for (int i = 0; i < rowCount; i++)
{
IRow rowHead = ws.GetRow(i); //在工作表中：建立行，参数为行号，从0计
rowHead.Height = Convert.ToInt16(rowHead.Height + (2 * 20));
for (int j = 0; j < colCount; j++)
{
if (i == 0)
{
styleHead.Alignment = HorizontalAlignment.Center; //设置单元格的样式：水平对齐居中
styleHead.VerticalAlignment = VerticalAlignment.Top;
fontHead.Boldweight = short.MaxValue; //设置字体加粗样式
fontHead.Color = NPOI.HSSF.Util.HSSFColor.White.Index;
//
styleHead.BorderBottom = BorderStyle.Thin; //边框
styleHead.BorderLeft = BorderStyle.Thin;
styleHead.BorderRight = BorderStyle.Thin;
styleHead.BorderTop = BorderStyle.Thin;
//
styleHead.FillPattern = FillPattern.SolidForeground;
styleHead.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightBlue.Index;
}
else
{
styleHead = styleHeadNull;
fontHead = fontHeadNull; //新建一个字体样式对象
styleHead.Alignment = HorizontalAlignment.Left; //设置单元格的样式：水平对齐居中
styleHead.VerticalAlignment = VerticalAlignment.Top;
fontHead.Color = NPOI.HSSF.Util.HSSFColor.Grey80Percent.Index;
//
styleHead.BorderBottom = BorderStyle.Thin; //边框
styleHead.BorderLeft = BorderStyle.Thin;
styleHead.BorderRight = BorderStyle.Thin;
styleHead.BorderTop = BorderStyle.Thin;
//
}
styleHead.SetFont(fontHead); //使用SetFont方法将字体样式添加到单元格样式中
ICell cellHead = rowHead.GetCell(j); //在行中：建立单元格，参数为列号，从0计
cellHead.CellStyle = styleHead; //将新的样式赋给单元格
int length = Encoding.Default.GetBytes(cellHead.ToString()).Length;
if (length > 250)
{
length = 200;
}
if (ws.GetColumnWidth(j) / 256 < length + 1)
{
ws.SetColumnWidth(j, (length + 2) * 256);
}
}
}
//
}
//Datatable导出Excel
public static void GridToExcelByNPOI(DataTable dt, string strExcelFileName, string title)
{
HSSFWorkbook workbook = new HSSFWorkbook();
try
{
ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//标题头
int icolIndex = 0;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dt.Columns.Count > 0 ? dt.Columns.Count - 1 : 0);
sheet.AddMergedRegion(region);
((HSSFSheet)sheet).SetEnclosedBorderOfRegion(region, BorderStyle.Thin, NPOI.HSSF.Util.HSSFColor.Black.Index);
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 20;
ICell celltitle = headerRow.CreateCell(0);
celltitle.SetCellValue(title);
celltitle.CellStyle = HeadercellStyle;
//用column name 作为列名
IRow headerRow1 = sheet.CreateRow(1);
foreach (DataColumn item in dt.Columns)
{
ICell cell1 = headerRow1.CreateCell(icolIndex);
cell1.SetCellValue(item.ColumnName);
cell1.CellStyle = HeadercellStyle;
icolIndex++;
}
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 2;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
}
catch (Exception ex)
{
string s = ex.Message;
}
finally
{
workbook = null;
}
}
/// <summary>
/// DataTable导出到Excel文件（无表头）另外的是有表头的
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
MemoryStream getms = new MemoryStream();
#region 为getms赋值
if (oldColumnNames.Length != newColumnNames.Length)
{
getms = new MemoryStream();
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
* 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 < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
* 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;
* }
* }
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
* 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 = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].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();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
getms = ms;
}
#endregion
using (MemoryStream ms = getms)
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
//npoi中单个工作薄最多可以创建4000个单元格样式，当时我也是卡在单元格样式这块卡了半天
//npoi中会默认创建一个单元格样式，也就是我们在新建excel中看到的那种样式
//每当我们创建一个单元格时，单元格样式默认为上述的单元格样式
//为什么要强调这个默认的单元格样式呢？因为我被他坑过（泪奔。。。）
//如果你要设置某个单元格的单元格样式，一定要先创建新的单元格样式，切勿直接修改，因为你会发现影响的可不只是这一个单元格
//当时我傻傻的认为每个单元格都有自己的样式，结果他们的样式都指向系统创建的默认的样式，而不是自己new的
//具体的使用后面呈现
#endregion
#region 四、单元格边框问题
//我们在excel中设置边框非常方便，鼠标拖一下，右击设置单元格样式，设置边框即可，但是在npoi可没这么简单，这个过程我们要用代码写出来
//主观上边框分为外边框和内边框
//但实际上投射到单元格就是16种单元格样式的组合！！
private ICellStyle GetCellBorderStyle(int style, NPOI.SS.UserModel.BorderStyle outStyle, NPOI.SS.UserModel.BorderStyle inStyle)
{
ICellStyle cellStyle = _hssfWorkbook.CreateCellStyle();
cellStyle.BorderTop = inStyle;
cellStyle.BorderLeft = inStyle;
cellStyle.BorderRight = inStyle;
cellStyle.BorderBottom = inStyle;
switch (style)
{
//九宫格中间的样式
case 0:
break;
//九宫格左上角样式
case 1:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
break;
//九宫格上方样式
case 2:
cellStyle.BorderTop = outStyle;
break;
//九宫格右上角样式
case 3:
cellStyle.BorderTop = outStyle;
cellStyle.BorderRight = outStyle;
break;
//九宫格右边样式
case 4:
cellStyle.BorderRight = outStyle;
break;
//九宫格右下角样式
case 5:
cellStyle.BorderBottom = outStyle;
cellStyle.BorderRight = outStyle;
break;
//九宫格下方样式
case 6:
cellStyle.BorderBottom = outStyle;
break;
//九宫格左下角样式
case 7:
cellStyle.BorderBottom = outStyle;
cellStyle.BorderLeft = outStyle;
break;
//九宫格左边样式
case 8:
cellStyle.BorderLeft = outStyle;
break;
//单行单列样式
case 9:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单列多行上方样式
case 10:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
break;
//单列多行中间样式
case 11:
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
break;
//单列多行下方样式
case 12:
cellStyle.BorderLeft = outStyle;
cellStyle.BorderRight = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单行多列右边样式
case 13:
cellStyle.BorderTop = outStyle;
cellStyle.BorderRight = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单行多列中间样式
case 14:
cellStyle.BorderTop = outStyle;
cellStyle.BorderBottom = outStyle;
break;
//单行多列下方样式
case 15:
cellStyle.BorderTop = outStyle;
cellStyle.BorderLeft = outStyle;
cellStyle.BorderBottom = outStyle;
break;
default:
break;
}
return(cellStyle);
}
/// <summary>
/// 把指定的DataTable导出Excel
/// Yakecan
/// </summary>
/// <param name="dt">数据源</param>
/// <param name="fileName">导出的文件名称</param>
/// <param name="sheetTittle">Sheet的名称</param>
public void Export(DataTable dt, string fileName, string sheetTittle)
{
HSSFWorkbook workbook = CreateNewExcel();
ISheet sheet1 = workbook.CreateSheet(sheetTittle);
IRow titleRow = sheet1.CreateRow(0);
titleRow.Height = (short)20 * 25;
ICellStyle titleStyle = workbook.CreateCellStyle();
titleStyle.Alignment = HorizontalAlignment.Center;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = (short)16;
titleStyle.SetFont(font);
NPOI.SS.Util.CellRangeAddress region = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dt.Columns.Count);
sheet1.AddMergedRegion(region); // 添加合并区域
ICell titleCell = titleRow.CreateCell(0);
titleCell.CellStyle = titleStyle;
titleCell.SetCellValue(sheetTittle);
IRow headerRow = sheet1.CreateRow(1);
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
IFont titleFont = workbook.CreateFont();
titleFont.FontHeightInPoints = (short)11;
titleFont.FontName = "宋体";
headerStyle.SetFont(titleFont);
headerRow.CreateCell(0).SetCellValue("序号");
headerRow.GetCell(0).CellStyle = headerStyle;
for (int i = 0; i < dt.Columns.Count; i++)
{
headerRow.CreateCell(i + 1).SetCellValue(dt.Columns[i].ColumnName);
headerRow.GetCell(i + 1).CellStyle = headerStyle;
sheet1.SetColumnWidth(i, 256 * 18);
}
ICellStyle bodyStyle = workbook.CreateCellStyle();
bodyStyle.BorderBottom = BorderStyle.Thin;
bodyStyle.BorderLeft = BorderStyle.Thin;
bodyStyle.BorderRight = BorderStyle.Thin;
bodyStyle.BorderTop = BorderStyle.Thin;
for (int r = 0; r < dt.Rows.Count; r++)
{
IRow bodyRow = sheet1.CreateRow(r + 2);
bodyRow.CreateCell(0).SetCellValue(r + 1);
bodyRow.GetCell(0).CellStyle = bodyStyle;
bodyRow.GetCell(0).CellStyle.Alignment = HorizontalAlignment.Center;
for (int c = 0; c < dt.Columns.Count; c++)
{
bodyRow.CreateCell(c + 1).SetCellValue(dt.Rows[r][c].ToString());
bodyRow.GetCell(c + 1).CellStyle = bodyStyle;
}
}
sheet1.CreateFreezePane(1, 2);
//FileStream fs = new FileStream(path, FileMode.Create);
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
sheet1 = null;
headerRow = null;
workbook = null;
OutPutExcelStreamOnClient(ms, fileName);
ms.Dispose();
}
/// <summary>
/// 根据数据实体和配置生成相应的Excel文件
/// </summary>
/// <param name="sourceData">数据源</param>
/// <param name="fileName">文件名</param>
/// <returns></returns>
public string CreateExcelFile(IList sourceData, string fileName)
{
//获取列表第一个元素的数据类型
Type type = null;
if (sourceData.Count > 0)
{
type = sourceData[0].GetType();
}
//创建2007的Excel
HSSFWorkbook workbook = null;
//如果文件存在，则直接获取采用追加的方式追加进去
string filePath = GetDownloadPath(fileName);
string absFilePath = FileUtils.GetPhysicalPath(filePath);
ISheet sheet = null;
IRow row = null;
//如果已经存在该文件，则说明已经产生过了，那么采用追加的方式
if (File.Exists(absFilePath))
{
using (var fs = new FileStream(absFilePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
POIFSFileSystem ps = new POIFSFileSystem(fs);
workbook = new HSSFWorkbook(ps);
sheet = workbook.GetSheetAt(0);
fs.Close();
fs.Dispose();
//row = sheet.CreateRow(sheet.LastRowNum + 1);
}//读取流
}
else
{
// (absFilePath);
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
//设置标题列样式
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.VerticalAlignment = VerticalAlignment.Center;
headStyle.Alignment = HorizontalAlignment.Center;
//设置字体-加粗字体
IFont headFont = workbook.CreateFont();
headFont.IsBold = true;
row = sheet.CreateRow(0);
//创建标题列
for (int i = 0; i < this.Properties.Count; i++)
{
var cell = row.CreateCell(i);
cell.SetCellValue(this.Properties[i].Caption);
cell.CellStyle = headStyle;
cell.CellStyle.SetFont(headFont);
}
}
//创建内容列
for (int i = 0; i < sourceData.Count; i++)
{
var entity = sourceData[i];
//创建新的内容行
row = sheet.CreateRow(i + sheet.LastRowNum + 1);
//根据配置的属性列创建对应单元格
for (int j = 0; j < this.Properties.Count; j++)
{
var prop = this.Properties[j].EntityProp;
//储存内容值
object value = null;
//检查是否存在，如果存在则表示是拼接数据
if (prop.IndexOf("+", StringComparison.Ordinal) > -1)
{
var joinProps = prop.Split('+');
for (int jp = 0; jp < joinProps.Length; jp++)
{
var jprop = joinProps[jp];
var newValue = this.GetPropValue(jprop, type, entity);
if (value != null)
{
if (newValue != null)
{
//这里只智齿字符串的累加，主要解决地址合并省市区的情况
value = value.ToString() + this.Properties[j].JoinPropChar + newValue.ToString();
}
}
else
{
value = newValue;
}
}
}
else
{
value = this.GetPropValue(prop, type, entity);
}
//根据数据类型填写对应的单元格
if (value == null)
{
row.CreateCell(j).SetCellValue("");
}
else if (value is int)
{
row.CreateCell(j).SetCellValue((int)value);
}
else if (value is long)
{
row.CreateCell(j).SetCellValue((long)value);
}
else if (value is decimal)
{
row.CreateCell(j).SetCellValue(Convert.ToDouble(value));
}
else if (value is double)
{
row.CreateCell(j).SetCellValue((double)value);
}
else if (value is DateTime)
{
row.CreateCell(j).SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss.fff"));
}
else if (value is bool)
{
row.CreateCell(j).SetCellValue((bool)value);
}
else
{
row.CreateCell(j).SetCellValue(value.ToString());
}
}
}
using (FileStream fs = File.OpenWrite(absFilePath))
{
workbook.Write(fs);
fs.Close();
fs.Dispose();
}
workbook = null;
return(filePath);
}
public static void ExportExcel(DataGridView dgv, string fileName = "", string fontname = "微软雅黑", short fontsize = 10)
{
//检测是否有数据
if (dgv.RowCount <= 0)
{
return;
}
//创建主要对象
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Weight");
//设置字体，大小，对齐方式
HSSFCellStyle style = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontName = fontname;
font.FontHeightInPoints = fontsize;
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
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 headerStyle = workbook.CreateCellStyle();
headerStyle.FillForegroundColor = IndexedColors.Yellow.Index;
headerStyle.FillPattern = FillPattern.SolidForeground;
headerStyle.SetFont(font);
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中对齐
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
//添加表头
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
dataRow.CreateCell(i).SetCellValue(dgv.Columns[i].HeaderText);
dataRow.GetCell(i).CellStyle = headerStyle;
}
//注释的这行是设置筛选的
//sheet.SetAutoFilter(new CellRangeAddress(0, dgv.Columns.Count, 0, dgv.Columns.Count));
//添加列及内容
for (int i = 0; i < dgv.Rows.Count; i++)
{
sheet.AutoSizeColumn(i);//先来个常规自适应
dataRow = (HSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
string ValueType = dgv.Rows[i].Cells[j].Value.GetType().ToString();
string Value = dgv.Rows[i].Cells[j].Value.ToString();
switch (ValueType)
{
case "System.String": //字符串类型
dataRow.CreateCell(j).SetCellValue(Value);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(Value, out dateV);
dataRow.CreateCell(j).SetCellValue(dateV);
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(Value, out boolV);
dataRow.CreateCell(j).SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(Value, out intV);
dataRow.CreateCell(j).SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(Value, out doubV);
dataRow.CreateCell(j).SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
dataRow.CreateCell(j).SetCellValue("");
break;
default:
dataRow.CreateCell(j).SetCellValue("");
break;
}
dataRow.GetCell(j).CellStyle = style;
//设置宽度
sheet.SetColumnWidth(j, (Value.Length + 10) * 300);
//首行冻结
sheet.CreateFreezePane(dgv.Columns.Count, 1);
}
}
//保存文件
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xls";
saveDialog.Filter = "Excel文件|*.xls";
saveDialog.FileName = fileName;
MemoryStream ms = new MemoryStream();
if (saveDialog.ShowDialog() == DialogResult.OK)
{
saveFileName = saveDialog.FileName;
if (!CheckFiles(saveFileName))
{
MessageBox.Show("文件占用，请关闭文件后再继续操作！ " + saveFileName);
workbook = null;
ms.Close();
ms.Dispose();
return;
}
workbook.Write(ms);
FileStream file = new FileStream(saveFileName, FileMode.Create);
workbook.Write(file);
file.Close();
workbook = null;
ms.Close();
ms.Dispose();
MessageBox.Show("导出完成！", "", MessageBoxButtons.OK);
}
else
{
workbook = null;
ms.Close();
ms.Dispose();
}
}
//styles
public void createStyle(string name)
{
AddStyle(name, (HSSFCellStyle)hssfworkbook.CreateCellStyle());
}
/// <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 = 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);
workbook.Close();
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <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>
/// 导出报表证书信息查询数据到excle
/// </summary>
/// <param name="fields">字段</param>
/// <param name="query">数据集</param>
/// <param name="path">excle模版的位置</param>
/// <param name="from">显示的标题默认行数为1</param>
/// <returns></returns>
public string WriteExcleVJianDingRenWu(string[] fields, dynamic[] query, string path = @"~/up/VJianDingRenWu.xls", int from = 1)
{
HSSFWorkbook _book = new HSSFWorkbook();
string xlsPath = System.Web.HttpContext.Current.Server.MapPath(path); //物理路径
FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read); //打开文件
IWorkbook hssfworkbook = new HSSFWorkbook(file); //数据流进行编辑
ISheet sheet = hssfworkbook.GetSheet("检定任务"); //读取工作表
string guid = Guid.NewGuid().ToString(); //生产唯一标识
string saveFileName = xlsPath.Path(@"JianDingRenWu/" + guid + "VJianDingRenWu");
Dictionary <string, string> propertyName;
PropertyInfo[] properties;
//标题行
var titles = "委托单号,报告编号,是否可以领取,器具名称,型号,出厂编号,证书单位,客户特殊要求,所在位置,器具状态,送检时间,超期原因,上传状态,报告状态,审核审批不通过原因,送检单位".Split(',');
var dd = sheet.GetRow(0).GetCell(1).CellStyle;
HSSFRow dataRow = sheet.CreateRow(0) as HSSFRow;
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.ShrinkToFit = true;
for (int i = 0; i < titles.Length; i++)
{
if (!string.IsNullOrWhiteSpace(titles[i]))
{
var cell = dataRow.CreateCell(i);
cell.CellStyle = dd;
cell.SetCellValue(titles[i]); //列值
}
}
//内容行
for (int i = 0; i < query.Length; i++)
{
propertyName = new Dictionary <string, string>();
if (query[i] == null)
{
continue;
}
Type type = query[i].GetType();
properties = type.GetProperties(BindingFlags.Instance | BindingFlags.Public);
foreach (PropertyInfo property in properties)
{
object o = property.GetValue(query[i], null);
if (!string.IsNullOrEmpty(property.Name) && o != null)
{
propertyName.Add(property.Name, o.ToString());
}
}
int j = 0;
dataRow = sheet.CreateRow(i + from) as HSSFRow;
fields.All(a =>
{
if (propertyName.ContainsKey(a)) //列名
{
var cell = dataRow.CreateCell(j);
cell.SetCellValue(propertyName[a]);
//列值
}
j++;
return(true);
});
}
sheet.ForceFormulaRecalculation = true;
using (FileStream fileWrite = new FileStream(saveFileName, FileMode.Create))
{
hssfworkbook.Write(fileWrite);
}
//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(string.Format("../../up/JianDingRenWu/{0}.xls", guid + "VJianDingRenWu"));
//记录日志
}
