using System;using System.Collections.Generic;using System.Linq;using System.Web;using NPOI.SS.Util;using NPOI.SS.UserModel;using System.Data;using System.Collections;using System.IO;using NPOI.HSSF.UserModel;using NPOI.HSSF.Util;using Model;using WebSite.Tools.Report;namespace AxxicsWeb.Tools.Report{ public class MaterialInReport : CreateExcel { /// <summary> /// 锁定方法 /// </summary> public static readonly Object lockobj = new Object(); // 单元格格式：默认左对齐 private ICellStyle style0 = null; // 单元格格式：居中 private ICellStyle style1 = null; // 单元格格式：居中 格式（.00） private ICellStyle style2 = null; // 报表模板文件名 public static string modeName = "report.xls"; // 工作簿 private HSSFWorkbook hssfworkbook = null; // 工作表 private ISheet sheet = null; public byte[] GetExcel(Hashtable ht) { // 所有的slipInfo数据 IList<SlipInfo> slist = ht["items"] as IList<SlipInfo>; // 查询参数：SlipInfo SlipInfo param = ht["s"] as SlipInfo; // 传票类型 string slipType = param.SlipType; // 表头数据 数组 string[] headers = null; // 存表头的list List<List<Hashtable>> data = new List<List<Hashtable>>(); // 材料入、出库 if (slipType == "01" || slipType == "02") headers = new string[] { "No.", "供应商编号", "供应商名称", "产品编号", "产品名称", "数量", "单位", "入库时间" }; // 产品入、出库 else if (slipType == "03" || slipType == "04") headers = new string[] { "No.", "客户编号", "客户名称", "产品编号", "产品名称", "数量", "单位", "入库时间" }; // 材料转成品出库 else if (slipType == "05") headers = new string[] { "No.", "客户编号", "客户名称", "供应商编号", "供应商名称", "产品编号", "产品名称", "数量", "单位", "入库时间" }; //创建工作簿和工作表 this.CreateHSworkbook(); // 创建表头 IRow row = sheet.CreateRow(2); // 列的数量： headers.Count() for (int i = 0, max = headers.Count(); i < max; i++) { CreateCell(row, i, style1, headers[i]); } // 写数据 DataTable dt = new DataTable(); // 材料转成品出库 if (slipType == "05") { GetSemiProductData(slist); } // 其他四个画面，数据是一样的 else { GetMaterialProductData(slist); } sheet.ForceFormulaRecalculation = true; using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); return stream.GetBuffer(); } } /// <summary> /// 共同表格式样取得 /// </summary> /// <param name="hssfworkbook">工作簿</param> /// <returns>式样</returns> private void CreateAllStyle(HSSFWorkbook hssfworkbook) { style0 = CreateComStyle(hssfworkbook); style0.VerticalAlignment = VerticalAlignment.CENTER; style1 = CreateComStyle(hssfworkbook); style1.VerticalAlignment = VerticalAlignment.CENTER; style1.Alignment = HorizontalAlignment.CENTER; style2 = CreateComStyle(hssfworkbook); style2.VerticalAlignment = VerticalAlignment.CENTER; style2.Alignment = HorizontalAlignment.RIGHT; style2.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00"); } /// <summary> /// 生成工作簿 /// </summary> /// <returns>sheet</returns> private ISheet CreateHSworkbook() { lock (lockobj) { using (FileStream fs = new FileStream(modePath + modeName, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(fs); } } // 定义工作簿的格式 CreateAllStyle(hssfworkbook); // 创建工作表 sheet = hssfworkbook.GetSheet("材料入库"); if (sheet == null) sheet = hssfworkbook.GetSheetAt(0); return sheet; } /// <summary> /// 取得材料入出库、产品入出库的报表数据 /// </summary> /// <returns></returns> private void GetMaterialProductData(IList<SlipInfo> slist) { for (int i = 0; i < slist.Count; i++) { // 创建行数据 IRow row1 = sheet.CreateRow(i + 3); object[] array = new object[8]; array[0] = Null2Empty((i+1).ToString()); array[1] = Null2Empty(slist[i].AcceptedCode); array[2] = Null2Empty(slist[i].AcceptedName); array[3] = Null2Empty(slist[i].ProductCode); array[4] = Null2Empty(slist[i].ProductName); array[5] = slist[i].Quantity; array[6] = Null2Empty(slist[i].UnitName); array[7] = Null2Empty(slist[i].SendTime.ToString()); for (int j = 0, max = array.Count(); j < max; j++) { ICellStyle rowstyle = null; // 定义[数量]列的单元格格式 if (j == 5) rowstyle = style2; // 定义[单位]、[入库时间]列的单元格格式 else if (j == 6 || j == 7) rowstyle = style1; // 定义其他列的单元格格式 else rowstyle = style0; //创建单元格 CreateCell(row1, j, rowstyle, array[j]); } } } /// <summary> /// 取得材料转成品的报表数据 /// </summary> /// <returns></returns> private void GetSemiProductData(IList<SlipInfo> slist) { for (int i = 0; i < slist.Count; i++) { // 创建行数据 IRow row1 = sheet.CreateRow(i + 3); object[] array = new object[10]; array[0] = Null2Empty((i+1).ToString()); array[1] = Null2Empty(slist[i].Other1); array[2] = Null2Empty(slist[i].OtherName1); array[3] = Null2Empty(slist[i].AcceptedCode); array[4] = Null2Empty(slist[i].AcceptedName); array[5] = Null2Empty(slist[i].ProductCode); array[6] = Null2Empty(slist[i].ProductName); array[7] = slist[i].Quantity; array[8] = Null2Empty(slist[i].UnitName); array[9] = Null2Empty(slist[i].SendTime.ToString()); for (int j = 0, max = array.Count(); j < max; j++) { ICellStyle rowstyle = null; // 定义[数量]列的单元格格式 if (j == 7) rowstyle = style2; // 定义[单位]、[入库时间]列的单元格格式 else if (j == 8 || j == 9) rowstyle = style1; // 定义其他列的单元格格式 else rowstyle = style0; //创建单元格 CreateCell(row1, j, rowstyle, array[j]); } } } }}
using System;using System.Collections.Generic;using System.Linq;using System.Web;using NPOI.SS.Util;using NPOI.SS.UserModel;using System.Data;using System.Collections;using System.IO;using NPOI.HSSF.UserModel;using System.Diagnostics;using System.Runtime.InteropServices;namespace WebSite.Tools.Report{ public class CreateExcel { /// <summary> /// 获得Excel模板路径 /// </summary> public string modePath = System.AppDomain.CurrentDomain.BaseDirectory + @"Template\"; /// <summary> /// 共同表格式样取得 /// </summary> /// <param name="hssfworkbook">工作簿</param> /// <returns>式样</returns> protected ICellStyle CreateComStyle(HSSFWorkbook hssfworkbook) { if (comFont == null) { comFont = hssfworkbook.CreateFont(); comFont.FontName = "宋体"; comFont.FontHeight = 11 * 20; } ICellStyle style = hssfworkbook.CreateCellStyle(); style.BorderBottom = BorderStyle.THIN ; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.THIN; style.VerticalAlignment = VerticalAlignment.TOP; style.Alignment = HorizontalAlignment.LEFT; style.WrapText = true; style.SetFont(comFont); return style; } protected ICellStyle CreateComStyle2(HSSFWorkbook hssfworkbook) { if (comFont == null) { comFont = hssfworkbook.CreateFont(); comFont.FontName = "宋体"; comFont.FontHeight = 11 * 20; } ICellStyle style = hssfworkbook.CreateCellStyle(); style.BorderBottom = BorderStyle.THIN; style.BorderLeft = BorderStyle.THIN; style.BorderRight = BorderStyle.THIN; style.BorderTop = BorderStyle.DOUBLE; style.VerticalAlignment = VerticalAlignment.TOP; style.Alignment = HorizontalAlignment.LEFT; style.WrapText = true; style.SetFont(comFont); return style; } /// <summary> /// 创建单元格 /// </summary> /// <param name="row">行对象</param> /// <param name="colIndex">单元格列Index</param> /// <param name="cellStyle">单元格式样</param> /// <param name="text">单元格文字</param> /// <param name="formula">计算公式（优先）</param> /// <returns>单元格对象</returns> protected ICell CreateCell(IRow row, int colIndex, ICellStyle cellStyle, object text = null, string formula = null) { ICell cell = row.CreateCell(colIndex); cell.CellStyle = cellStyle; if (!string.IsNullOrEmpty(formula)) cell.CellFormula = formula; if (text is double) cell.SetCellValue((double)text); else if (text is decimal) cell.SetCellValue((double)((decimal)text)); else if (text is int) cell.SetCellValue((int)text); else if (text is long) cell.SetCellValue((long)text); else cell.SetCellValue(Null2Empty(text)); return cell; } }}
