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;
}
}
}