NPOI 导入 导出 Excel

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


    }
}