using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.Data; using System.IO; using NPOI; using NPOI.SS.UserModel; using NPOI.HSSF.UserModel; using Gnc.Org.Entity; using NPOI.SS.Util; using System.Threading; using System.Globalization; using NPOI.HSSF.Util; using System.Drawing; using System.Web; namespace Common.Business { public static class ExcelUtility { /// <summary> /// Excel转换DataTable /// </summary> /// <param name="FilePath">文件的绝对路径</param> /// <returns>DataTable</returns> public static DataTable ExcelInput(Stream file) { //第一行一般为标题行。 DataTable table = new DataTable(); //根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档 HSSFWorkbook workbook = new HSSFWorkbook(file); HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(0); //获取excel的第一个sheet //获取Excel的最大行数 int rowsCount = sheet.PhysicalNumberOfRows; //为保证Table布局与Excel一样，这里应该取所有行中的最大列数（需要遍历整个Sheet）。 //为少一交全Excel遍历，提高性能，我们可以人为把第0行的列数调整至所有行中的最大列数。 int colsCount = sheet.GetRow(0).PhysicalNumberOfCells; for (int i = 0; i < colsCount; i++) { table.Columns.Add(i.ToString()); } for (int x = 0; x < rowsCount; x++) { DataRow dr = table.NewRow(); for (int y = 0; y < colsCount; y++) { dr[y] = sheet.GetRow(x).GetCell(y).ToString(); } table.Rows.Add(dr); } sheet = null; workbook = null; return table; } public static void ExportTimetracking<T>(List<T> list, Dictionary<string, string> colInfor, string filename) { Type myType = list[0].GetType(); List<System.Reflection.PropertyInfo> myPro = new List<System.Reflection.PropertyInfo>(); HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet(filename); sheet1.SetColumnWidth(0, 10 * 256); sheet1.SetColumnWidth(1, 10 * 256); sheet1.SetColumnWidth(2, 10 * 256); sheet1.SetColumnWidth(3, 10 * 256); sheet1.SetColumnWidth(4, 10 * 256); sheet1.SetColumnWidth(5, 30 * 256); sheet1.SetColumnWidth(6, 30 * 256); sheet1.SetColumnWidth(7, 15 * 256); sheet1.SetColumnWidth(8, 30 * 256); IRow rTitle = sheet1.CreateRow(0); int colIdex = 0; ICellStyle headerStyle = hssfworkbook.CreateCellStyle(); headerStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; headerStyle.FillPattern = FillPatternType.SOLID_FOREGROUND; foreach (string cName in colInfor.Keys) { System.Reflection.PropertyInfo p = myType.GetProperty(cName); if (p != null) { myPro.Add(p); ICell cTitle = rTitle.CreateCell(colIdex); cTitle.SetCellValue(colInfor[cName]); cTitle.CellStyle = headerStyle; } colIdex++; } if (myPro.Count == 0) { return; } int rowIndex = 1; foreach (T obj in list) { IRow rData = sheet1.CreateRow(rowIndex); int cIndex = 0; foreach (System.Reflection.PropertyInfo p in myPro) { ICell cData = rData.CreateCell(cIndex); if ((p.PropertyType).FullName.Contains("DateTime")) { cData.SetCellValue(p.GetValue(obj, null).ToString().Split(' ')[0]); } else { cData.SetCellValue(p.GetValue(obj, null).ToString()); } cIndex++; } rowIndex++; } string path = HttpContext.Current.Server.MapPath("/UserUploadFiles/DeptAExport/" + filename); FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); HttpResponse Response = HttpContext.Current.Response; Response.Clear(); Response.AppendHeader("Content-Disposition", "attachment;filename=" + filename); Response.Charset = "gb2312"; Response.ContentType = "application/ms-excel"; Response.WriteFile(path); Response.End(); } public static void ExportTrackingInvoice(List<TimeTrackingInvoiceView> timeTrackingInvoiceView, DateTime billDate) { HSSFWorkbook hssfworkbook = new HSSFWorkbook(); ISheet sheet1 = hssfworkbook.CreateSheet(billDate.ToString("yyyy-MM-dd")); sheet1.SetColumnWidth(0, 20 * 256); sheet1.SetColumnWidth(1, 30 * 256); sheet1.SetColumnWidth(2, 10 * 256); sheet1.SetColumnWidth(3, 10 * 256); sheet1.SetColumnWidth(4, 10 * 256); sheet1.SetColumnWidth(5, 10 * 256); sheet1.SetColumnWidth(6, 10 * 256); sheet1.SetColumnWidth(7, 10 * 256); ICellStyle styleColum = hssfworkbook.CreateCellStyle(); styleColum.BorderBottom = BorderStyle.THIN; styleColum.BorderLeft = BorderStyle.THIN; styleColum.BorderRight = BorderStyle.THIN; styleColum.BorderTop = BorderStyle.THIN; sheet1.SetDefaultColumnStyle(0, styleColum); sheet1.SetDefaultColumnStyle(1, styleColum); sheet1.SetDefaultColumnStyle(2, styleColum); sheet1.SetDefaultColumnStyle(3, styleColum); sheet1.SetDefaultColumnStyle(4, styleColum); sheet1.SetDefaultColumnStyle(5, styleColum); sheet1.SetDefaultColumnStyle(6, styleColum); sheet1.SetDefaultColumnStyle(7, styleColum); //创建标题行 IRow rTitle = sheet1.CreateRow(0); ICell cTitle = rTitle.CreateCell(0); cTitle.SetCellValue(" Soft Co. Invoice"); ICellStyle style = hssfworkbook.CreateCellStyle(); style.Alignment = HorizontalAlignment.CENTER; IFont font = hssfworkbook.CreateFont(); font.FontHeight = 20 * 20; style.SetFont(font); cTitle.CellStyle = style; sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 8)); //创建表头 IRow rHeader = sheet1.CreateRow(1); ICellStyle styleHeader = hssfworkbook.CreateCellStyle(); styleHeader.VerticalAlignment = VerticalAlignment.CENTER; styleHeader.Alignment = HorizontalAlignment.CENTER; styleHeader.FillForegroundColor = GetXLColour(hssfworkbook, Color.FromArgb(255, 255, 153)); styleHeader.FillPattern = FillPatternType.SOLID_FOREGROUND; styleHeader.BorderBottom = BorderStyle.THIN; styleHeader.BorderLeft = BorderStyle.THIN; styleHeader.BorderRight = BorderStyle.THIN; styleHeader.BorderTop = BorderStyle.THIN; ICell c0 = rHeader.CreateCell(0); c0.SetCellValue("Customer Name"); c0.CellStyle = styleHeader; sheet1.AddMergedRegion(new CellRangeAddress(1, 3, 0, 0)); ICell c1 = rHeader.CreateCell(1); c1.SetCellValue("Project"); c1.CellStyle = styleHeader; sheet1.AddMergedRegion(new CellRangeAddress(1, 3, 1, 1)); ICell c2 = rHeader.CreateCell(2); c2.SetCellValue("Estimate"); c2.CellStyle = styleHeader; ICell c3 = rHeader.CreateCell(3); c3.SetCellValue("Cumulate"); c3.CellStyle = styleHeader; ICell c4 = rHeader.CreateCell(4); c4.SetCellValue("hm"); c4.CellStyle = styleHeader; ICell c5 = rHeader.CreateCell(5); c5.SetCellValue("US SDS"); c5.CellStyle = styleHeader; ICell c6 = rHeader.CreateCell(6); c6.SetCellValue("Total"); c6.CellStyle = styleHeader; ICell c7 = rHeader.CreateCell(7); c7.SetCellValue("Comment"); c7.CellStyle = styleHeader; sheet1.AddMergedRegion(new CellRangeAddress(1, 3, 7, 7)); IRow r2 = sheet1.CreateRow(2); ICell r2c2 = r2.CreateCell(2); r2c2.SetCellValue("(Hours)"); r2c2.CellStyle = styleHeader; sheet1.AddMergedRegion(new CellRangeAddress(2, 3, 2, 2)); ICell r2c3 = r2.CreateCell(3); r2c3.SetCellValue("(Hours)"); r2c3.CellStyle = styleHeader; sheet1.AddMergedRegion(new CellRangeAddress(2, 3, 3, 3)); ICell r2c4 = r2.CreateCell(4); r2c4.SetCellValue("(Hours)"); r2c4.CellStyle = styleHeader; ICell r2c5 = r2.CreateCell(5); r2c5.SetCellValue("(Hours)"); r2c5.CellStyle = styleHeader; ICell r2c6 = r2.CreateCell(6); r2c6.SetCellValue("(Hours)"); r2c6.CellStyle = styleHeader; IRow r3 = sheet1.CreateRow(3); ICell r3c4 = r3.CreateCell(4); r3c4.SetCellValue(billDate.ToString("yyyy/MM/dd")); r3c4.CellStyle = styleHeader; ICell r3c5 = r3.CreateCell(5); r3c5.SetCellValue(billDate.ToString("yyyy/MM/dd")); r3c5.CellStyle = styleHeader; ICell r3c6 = r3.CreateCell(6); r3c6.SetCellValue(billDate.ToString("yyyy/MM/dd")); r3c6.CellStyle = styleHeader; List<TimeTrackingInvoiceView> customersSum = (from e in timeTrackingInvoiceView group e by e.Customer into g select new TimeTrackingInvoiceView { Cumulate = g.Sum(t => t.Cumulate), Customer = g.FirstOrDefault().Customer, Estimate = g.Sum(t => t.Estimate), hmOurs = g.Sum(t => t.hmOurs) }).ToList(); int rowIndex = 4; ICellStyle styleColSum = hssfworkbook.CreateCellStyle(); styleColSum.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIGHT_BLUE.index; styleColSum.FillPattern = FillPatternType.SOLID_FOREGROUND; styleColSum.BorderLeft = BorderStyle.THIN; styleColSum.BorderRight = BorderStyle.THIN; ICellStyle styleCus = hssfworkbook.CreateCellStyle(); styleCus.VerticalAlignment = VerticalAlignment.CENTER; for (int i = 0; i < customersSum.Count; i++) { List<TimeTrackingInvoiceView> trackingForCustomer = timeTrackingInvoiceView.Where(t => t.Customer == customersSum[i].Customer).ToList(); int cusStartIndex = rowIndex; for (int j = 0; j < trackingForCustomer.Count; j++) { IRow rData = sheet1.CreateRow(rowIndex++); ICell cDate0 = rData.CreateCell(0); cDate0.SetCellValue(trackingForCustomer[j].Customer); cDate0.CellStyle = styleCus; ICell cDate1 = rData.CreateCell(1); cDate1.SetCellValue(trackingForCustomer[j].Project); ICell cDate2 = rData.CreateCell(2); cDate2.SetCellValue(trackingForCustomer[j].Estimate.ToString("f2")); ICell cDate3 = rData.CreateCell(3); cDate3.SetCellValue(trackingForCustomer[j].Cumulate.ToString("f2")); ICell cDate4 = rData.CreateCell(4); cDate4.SetCellValue(trackingForCustomer[j].hmOurs.ToString("f2")); } sheet1.AddMergedRegion(new CellRangeAddress(cusStartIndex, rowIndex-1, 0, 0)); IRow rDataSum = sheet1.CreateRow(rowIndex++); ICell cDateSum0 = rDataSum.CreateCell(0); cDateSum0.SetCellValue(customersSum[i].Customer + " Tatal"); cDateSum0.CellStyle = styleColSum; ICell cDateSum2 = rDataSum.CreateCell(2); cDateSum2.SetCellValue(customersSum[i].Estimate.ToString("f2")); cDateSum2.CellStyle = styleColSum; ICell cDateSum3 = rDataSum.CreateCell(3); cDateSum3.SetCellValue(customersSum[i].Cumulate.ToString("f2")); cDateSum3.CellStyle = styleColSum; ICell cDateSum4 = rDataSum.CreateCell(4); cDateSum4.SetCellValue(customersSum[i].hmOurs.ToString("f2")); cDateSum4.CellStyle = styleColSum; ICell cDateSum5 = rDataSum.CreateCell(5); cDateSum5.CellStyle = styleColSum; ICell cDateSum6 = rDataSum.CreateCell(6); cDateSum6.CellStyle = styleColSum; ICell cDateSum7 = rDataSum.CreateCell(7); cDateSum7.CellStyle = styleColSum; sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 1)); } ICellStyle styleColSumAll = hssfworkbook.CreateCellStyle(); // styleColSumAll.FillForegroundColor = GetXLColour(hssfworkbook, Color.FromArgb(164, 208, 80)); styleColSumAll.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LIME.index; styleColSumAll.FillPattern = FillPatternType.SOLID_FOREGROUND; styleColSumAll.BorderLeft = BorderStyle.THIN; styleColSumAll.BorderRight = BorderStyle.THIN; IRow rDateSumAll = sheet1.CreateRow(rowIndex++); ICell cDataSumAll0 = rDateSumAll.CreateCell(0); cDataSumAll0.SetCellValue("Tatal"); cDataSumAll0.CellStyle = styleColSumAll; ICell cDateSumAll2 = rDateSumAll.CreateCell(2); cDateSumAll2.SetCellValue(customersSum.Sum(t => t.Estimate).ToString("f2")); cDateSumAll2.CellStyle = styleColSumAll; ICell cDateSumAll3 = rDateSumAll.CreateCell(3); cDateSumAll3.SetCellValue(customersSum.Sum(t => t.Cumulate).ToString("f2")); cDateSumAll3.CellStyle = styleColSumAll; ICell cDateSumAll4 = rDateSumAll.CreateCell(4); cDateSumAll4.CellStyle = styleColSumAll; cDateSumAll4.SetCellValue(customersSum.Sum(t => t.hmOurs).ToString("f2")); ICell cDateSumAll5 = rDateSumAll.CreateCell(5); cDateSumAll5.CellStyle = styleColSumAll; ICell cDateSumAll6 = rDateSumAll.CreateCell(6); cDateSumAll6.CellStyle = styleColSumAll; ICell cDateSumAll7 = rDateSumAll.CreateCell(7); cDateSumAll7.CellStyle = styleColSumAll; sheet1.AddMergedRegion(new CellRangeAddress(rowIndex - 1, rowIndex - 1, 0, 1)); IRow rblank0 = sheet1.CreateRow(rowIndex++); IRow rblank1 = sheet1.CreateRow(rowIndex++); IRow rDate = sheet1.CreateRow(rowIndex++); ICell cDate = rDate.CreateCell(6); cDate.SetCellValue(billDate.ToString("MMM,dd,yyyy", DateTimeFormatInfo.InvariantInfo)); string fileName = "Invoice_" + billDate.ToString("yyyy-MM-dd") + ".xls"; string path = HttpContext.Current.Server.MapPath("/UserUploadFiles/DeptAExport/" + fileName); FileStream file = new FileStream(path, FileMode.Create); hssfworkbook.Write(file); file.Close(); HttpResponse Response = HttpContext.Current.Response; Response.Clear(); Response.AppendHeader("Content-Disposition", "attachment;filename=" +fileName ); Response.Charset = "gb2312"; Response.ContentType = "application/ms-excel"; Response.WriteFile(path); Response.End(); } private static short GetXLColour(HSSFWorkbook workbook, System.Drawing.Color SystemColour) { short s = 0; HSSFPalette XlPalette = workbook.GetCustomPalette(); HSSFColor XlColour = XlPalette.FindColor(SystemColour.R, SystemColour.G, SystemColour.B); if (XlColour == null) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 255) { if (NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE < 64) { NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE = 64; NPOI.HSSF.Record.PaletteRecord.STANDARD_PALETTE_SIZE += 1; XlColour = XlPalette.AddColor(SystemColour.R, SystemColour.G, SystemColour.B); } else { XlColour = XlPalette.FindSimilarColor(SystemColour.R, SystemColour.G, SystemColour.B); } s = XlColour.GetIndex(); } } else s = XlColour.GetIndex(); return s; } } }