Пример #1
0
        /// <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 = ReflectionHelper.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);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }
Пример #2
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</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           = 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 CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        //headerRow.Dispose();
                    }
                    #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);
                        }
                        //headerRow.Dispose();
                    }
                    #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":    //日期类型
                        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++;
            }
            //MemoryStream ms = new MemoryStream();
            //workbook.Write(ms);
            //return ms;

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;

                //sheet.Dispose();
                //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                return(ms);
            }
        }
Пример #3
0
        public void TestOptimiseFonts()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            // Add 6 fonts, some duplicates
            IFont f1 = wb.CreateFont();

            f1.FontHeight = ((short)11);
            f1.FontName   = ("Testing");

            IFont f2 = wb.CreateFont();

            f2.FontHeight = ((short)22);
            f2.FontName   = ("Also Testing");

            IFont f3 = wb.CreateFont();

            f3.FontHeight = ((short)33);
            f3.FontName   = ("Unique");

            IFont f4 = wb.CreateFont();

            f4.FontHeight = ((short)11);
            f4.FontName   = ("Testing");

            IFont f5 = wb.CreateFont();

            f5.FontHeight = ((short)22);
            f5.FontName   = ("Also Testing");

            IFont f6 = wb.CreateFont();

            f6.FontHeight = ((short)66);
            f6.FontName   = ("Also Unique");



            // Use all three of the four in cell styles
            Assert.AreEqual(21, wb.NumCellStyles);

            Npoi.Core.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
            cs1.SetFont(f1);
            Assert.AreEqual(5, cs1.FontIndex);

            Npoi.Core.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
            cs2.SetFont(f4);
            Assert.AreEqual(8, cs2.FontIndex);

            Npoi.Core.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
            cs3.SetFont(f5);
            Assert.AreEqual(9, cs3.FontIndex);

            Npoi.Core.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
            cs4.SetFont(f6);
            Assert.AreEqual(10, cs4.FontIndex);

            Assert.AreEqual(25, wb.NumCellStyles);


            // And three in rich text
            Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = s.CreateRow(0);

            HSSFRichTextString rtr1 = new HSSFRichTextString("Test");

            rtr1.ApplyFont(0, 2, f1);
            rtr1.ApplyFont(3, 4, f2);
            r.CreateCell(0).SetCellValue(rtr1);

            HSSFRichTextString rtr2 = new HSSFRichTextString("AlsoTest");

            rtr2.ApplyFont(0, 2, f3);
            rtr2.ApplyFont(3, 5, f5);
            rtr2.ApplyFont(6, 8, f6);
            r.CreateCell(1).SetCellValue(rtr2);


            // Check what we have now
            Assert.AreEqual(10, wb.NumberOfFonts);
            Assert.AreEqual(25, wb.NumCellStyles);

            // Optimise
            HSSFOptimiser.OptimiseFonts(wb);

            // Check font count
            Assert.AreEqual(8, wb.NumberOfFonts);
            Assert.AreEqual(25, wb.NumCellStyles);

            // Check font use in cell styles
            Assert.AreEqual(5, cs1.FontIndex);
            Assert.AreEqual(5, cs2.FontIndex); // duplicate of 1
            Assert.AreEqual(6, cs3.FontIndex); // duplicate of 2
            Assert.AreEqual(8, cs4.FontIndex); // two have gone


            // And in rich text

            // RTR 1 had f1 and f2, unchanged
            Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
            Assert.AreEqual(5, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
            Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
            Assert.AreEqual(6, (r.GetCell(0).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));

            // RTR 2 had f3 (unchanged), f5 (=f2) and f6 (moved down)
            Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(0));
            Assert.AreEqual(7, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(1));
            Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(3));
            Assert.AreEqual(6, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(4));
            Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(6));
            Assert.AreEqual(8, (r.GetCell(1).RichStringCellValue as HSSFRichTextString).GetFontAtIndex(7));
        }
Пример #4
0
        /// <summary>
        /// DataTable导出到Excel的MemoryStream
        /// </summary>
        /// <param name="dtSource">源DataTable</param>
        /// <param name="strHeaderText">表头文本</param>
        public static MemoryStream Export(DataTable dtSource, string strHeaderText)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet    sheet    = workbook.CreateSheet("Sheet1");

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "http://bimpan.iok.la:88/";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = "";                //填加xls文件作者信息
                si.ApplicationName          = "由堃哥编写的Excel导出程序"; //填加xls文件创建程序信息
                si.LastAuthor               = "";                //填加xls文件最后保存者信息
                si.Comments                 = "说明信息";            //填加xls文件作者信息
                si.Title                    = "任务报表";            //填加xls文件标题信息
                si.Subject                  = "任务报表";            //填加文件主题信息
                si.CreateDateTime           = DateTime.Now;
                workbook.SummaryInformation = si;
            }
            #endregion

            HSSFCellStyle  dateStyle = workbook.CreateCellStyle();
            HSSFDataFormat 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 表头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);

                        HSSFCellStyle headStyle = workbook.CreateCellStyle();
                        // headStyle.Alignment = CellHorizontalAlignment.CENTER;
                        HSSFFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        headerRow.GetCell(0).CellStyle = headStyle;

                        sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
                        // headerRow.Dispose();
                    }
                    #endregion


                    #region 列头及样式
                    {
                        HSSFRow headerRow = sheet.CreateRow(1);


                        HSSFCellStyle headStyle = workbook.CreateCellStyle();
                        // headStyle.Alignment = CellHorizontalAlignment.CENTER;
                        HSSFFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 10;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);

                        //将英文列名转为中文
                        XmlDocument xmlDoc  = new XmlDocument();
                        string      xmlPath = string.Format("{0}/{1}", System.Web.HttpContext.Current.Request.PhysicalApplicationPath, "Config/TaskCHSName.xml");
                        xmlDoc.Load(xmlPath);
                        Dictionary <string, string> dicEN2CH = Common.GetNodelist(xmlDoc.InnerXml, "Root");

                        foreach (DataColumn column in dtSource.Columns)
                        {
                            string columnNameCH = dicEN2CH.ContainsKey(column.ColumnName.ToUpper()) ? dicEN2CH[column.ColumnName.ToUpper()] : column.ColumnName;

                            headerRow.CreateCell(column.Ordinal).SetCellValue(columnNameCH);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

                            //设置列宽
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                        }
                        //   headerRow.Dispose();
                    }
                    #endregion

                    rowIndex = 2;
                }
                #endregion


                #region 填充内容
                HSSFRow dataRow = sheet.CreateRow(rowIndex);
                foreach (DataColumn column in dtSource.Columns)
                {
                    HSSFCell 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 = 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();
                //workbook.Dispose();//一般只用写这一个就OK了,他会遍历并释放所有资源,但当前版本有问题所以只释放sheet
                return(ms);
            }
        }
Пример #5
0
        public static bool GetIndividualVisaExcel(List <TravelAgency.Model.VisaInfo> list, string remark, string groupNo)
        {
            //1.创建工作簿对象
            IWorkbook wkbook = new HSSFWorkbook();
            //2.创建工作表对象
            ISheet sheet = wkbook.CreateSheet("签证申请人名单");

            //2.1创建表头

            IRow rowHeader = sheet.CreateRow(0);

            rowHeader.CreateCell(0).SetCellValue("签证申请人名单");
            rowHeader.HeightInPoints = 50;

            IRow row = sheet.CreateRow(1);

            row.CreateCell(0).SetCellValue("编号");
            row.CreateCell(1).SetCellValue("姓名(中文)");
            row.CreateCell(2).SetCellValue("姓名(英文)");
            row.CreateCell(3).SetCellValue("性别");
            row.CreateCell(4).SetCellValue("护照发行地");
            row.CreateCell(5).SetCellValue("居住地点");
            row.CreateCell(6).SetCellValue("出生年月日");
            row.CreateCell(7).SetCellValue("职业");
            row.CreateCell(8).SetCellValue("出境记录");
            row.CreateCell(9).SetCellValue("婚姻");
            row.CreateCell(10).SetCellValue("身份确认");
            row.CreateCell(11).SetCellValue("经济能力确认");
            row.CreateCell(12).SetCellValue("备注");
            row.CreateCell(13).SetCellValue("旅行社意见");
            row.CreateCell(14).SetCellValue("护照号");
            //row.CreateCell(15).SetCellValue("手机号");

            //2.2设置列宽度
            sheet.SetColumnWidth(0, 5 * 256);   //编号
            sheet.SetColumnWidth(1, 15 * 256);  //姓名(中文)
            sheet.SetColumnWidth(2, 20 * 256);  //姓名(英文)
            sheet.SetColumnWidth(3, 5 * 256);   //性别
            sheet.SetColumnWidth(4, 10 * 256);  //护照发行地
            sheet.SetColumnWidth(5, 25 * 256);  //居住地点
            sheet.SetColumnWidth(6, 15 * 256);  //出生年月日
            sheet.SetColumnWidth(7, 10 * 256);  //职业
            sheet.SetColumnWidth(8, 10 * 256);  //出境记录
            sheet.SetColumnWidth(9, 10 * 256);  //婚姻
            sheet.SetColumnWidth(10, 20 * 256); //身份确认
            sheet.SetColumnWidth(11, 25 * 256); //经济能力确认
            sheet.SetColumnWidth(12, 10 * 256); //备注
            sheet.SetColumnWidth(13, 10 * 256); //旅行社意见
            sheet.SetColumnWidth(14, 15 * 256); //护照号
            //sheet.SetColumnWidth(15, 15 * 256);//手机号
            //3.插入行和单元格
            for (int i = 0; i != list.Count; ++i)
            {
                //创建单元格
                row = sheet.CreateRow(i + 2);
                //设置行高
                row.HeightInPoints = 100;
                //设置值
                row.CreateCell(0).SetCellValue(i + 1);
                row.CreateCell(1).SetCellValue(list[i].Name);
                row.CreateCell(2).SetCellValue(list[i].EnglishName);
                row.CreateCell(3).SetCellValue(list[i].Sex);
                row.CreateCell(4).SetCellValue(list[i].IssuePlace);
                row.CreateCell(5).SetCellValue(list[i].Residence);
                row.CreateCell(6).SetCellValue(DateTimeFormator.DateTimeToString(list[i].Birthday));
                row.CreateCell(7).SetCellValue(list[i].Occupation);
                row.CreateCell(8).SetCellValue(list[i].DepartureRecord);
                row.CreateCell(9).SetCellValue(list[i].Marriaged);
                row.CreateCell(10).SetCellValue(list[i].Identification);
                row.CreateCell(11).SetCellValue(list[i].FinancialCapacity);
                row.CreateCell(12).SetCellValue(remark);
                row.CreateCell(13).SetCellValue(list[i].AgencyOpinion);
                row.CreateCell(14).SetCellValue(list[i].PassportNo);
                //row.CreateCell(15).SetCellValue(list[i].Phone);
            }


            //4.2合并单元格
            sheet.AddMergedRegion(new CellRangeAddress(2, sheet.LastRowNum, 12, 12)); //备注列合并
            sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));                 //表头合并

            //4.1设置对齐风格和边框
            ICellStyle style = wkbook.CreateCellStyle();

            style.VerticalAlignment = VerticalAlignment.Center;
            style.Alignment         = HorizontalAlignment.Center;
            style.WrapText          = true; //文本自动换行
            style.BorderTop         = BorderStyle.Thin;
            style.BorderBottom      = BorderStyle.Thin;
            style.BorderLeft        = BorderStyle.Thin;
            style.BorderRight       = BorderStyle.Thin;
            HSSFFont font = (HSSFFont)wkbook.CreateFont();

            font.FontHeightInPoints = 12;
            style.SetFont(font);

            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                for (int c = 0; c < row.LastCellNum; ++c)
                {
                    row.GetCell(c).CellStyle = style;
                }
            }

            ICellStyle headerStyle = wkbook.CreateCellStyle();

            headerStyle.VerticalAlignment = VerticalAlignment.Center;
            headerStyle.Alignment         = HorizontalAlignment.Center;
            headerStyle.BorderTop         = BorderStyle.Thin;
            headerStyle.BorderBottom      = BorderStyle.Thin;
            headerStyle.BorderLeft        = BorderStyle.Thin;
            headerStyle.BorderRight       = BorderStyle.Thin;
            HSSFFont font1 = (HSSFFont)wkbook.CreateFont();

            font1.FontHeightInPoints = 15;
            headerStyle.SetFont(font1);
            sheet.GetRow(0).GetCell(0).CellStyle = headerStyle;


            //5.执行写入磁盘
            string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");

            return(SaveFile(dstName, wkbook));
        }
Пример #6
0
        public Stream ToExcel()
        {
            int rowIndex = 0;

            //创建workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms       = new MemoryStream();
            ISheet       sheet    = workbook.CreateSheet("sheet1");
            IRow         row      = sheet.CreateRow(rowIndex);

            row.Height = 200 * 3;

            //表头样式
            ICellStyle style = workbook.CreateCellStyle();

            style.Alignment = HorizontalAlignment.Left;//居中对齐
            //表头单元格背景色
            style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            //表头单元格边框
            style.BorderBottom      = BorderStyle.Thin;
            style.BorderRight       = BorderStyle.Thin;
            style.BorderBottom      = BorderStyle.Thin;
            style.BorderLeft        = BorderStyle.Thin;
            style.TopBorderColor    = HSSFColor.Black.Index;
            style.RightBorderColor  = HSSFColor.Black.Index;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor   = HSSFColor.Black.Index;
            style.VerticalAlignment = VerticalAlignment.Center;
            //表头字体设置
            IFont font = workbook.CreateFont();

            font.FontHeightInPoints = 12;  //字号
            font.Boldweight         = 600; //加粗
            //font.Color = HSSFColor.WHITE.index;//颜色
            style.SetFont(font);

            //数据样式
            ICellStyle datastyle = workbook.CreateCellStyle();

            datastyle.Alignment = HorizontalAlignment.Left;//左对齐
            //数据单元格的边框
            datastyle.BorderTop    = BorderStyle.Thin;
            datastyle.BorderRight  = BorderStyle.Thin;
            datastyle.BorderBottom = BorderStyle.Thin;
            datastyle.BorderLeft   = BorderStyle.Thin;

            datastyle.TopBorderColor    = HSSFColor.Black.Index;
            datastyle.RightBorderColor  = HSSFColor.Black.Index;
            datastyle.BottomBorderColor = HSSFColor.Black.Index;
            datastyle.LeftBorderColor   = HSSFColor.Black.Index;
            //数据的字体
            IFont datafont = workbook.CreateFont();

            datafont.FontHeightInPoints = 11;//字号
            datastyle.SetFont(datafont);
            //设置列宽
            sheet.SetColumnWidth(0, 20 * 256);
            int colWidth0 = sheet.GetColumnWidth(0);

            sheet.SetColumnWidth(1, 20 * 256);
            int colWidth1 = sheet.GetColumnWidth(1);

            sheet.SetColumnWidth(2, 20 * 256);
            int colWidth2 = sheet.GetColumnWidth(2);



            sheet.SetColumnWidth(9, 40 * 256);
            sheet.DisplayGridlines = false;

            try {
                //表头数据
                for (int i = 0; i < titles.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(titles[i]);
                    cell.CellStyle = style;
                }


                for (int k = 0; k < data.Count; k++)
                {
                    row        = sheet.CreateRow(k + 1);
                    row.Height = 200 * 2;
                    T t = data[k];
                    // 获得此模型的公共属性
                    PropertyInfo[] propertys = t.GetType().GetProperties();
                    for (int j = 0; j < propertys.Length; j++)
                    {
                        var   value = propertys[j].GetValue(null, null).ToString();
                        ICell cell  = row.CreateCell(j);
                        cell.SetCellValue(value);
                        cell.CellStyle = datastyle;
                    }
                }
            } catch (Exception ex) {
            } finally {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                workbook    = null;
                sheet       = null;
                row         = null;
            }



            return(ms);
        }
Пример #7
0
        //public void Create()
        //{
        //    HSSFWorkbook book = new HSSFWorkbook();
        //    ISheet sheet = book.CreateSheet("Sheet1");

        //    IRow row = sheet.CreateRow(20);//index代表多少行
        //    row.HeightInPoints = 35;//行高
        //    ICell cell = row.CreateCell(0);//创建第一列
        //    cell.SetCellValue("设置单元格的值");



        //}

        /// <summary>
        /// 导出基本操作示例方法
        /// </summary>
        public static void ExportExcel()
        {
            //初始化一个新的HSSFWorkbook实例
            HSSFWorkbook hssfworkbook = new HSSFWorkbook();

            //设置excel必须的文件属性(该属性用来存储 如作者、标题、标记、备注、主题等信息,右键可查看的属性信息)
            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

            dsi.Company = "NPOI Team";
            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

            si.Subject = "NPOI SDK Example";
            hssfworkbook.DocumentSummaryInformation = dsi;
            hssfworkbook.SummaryInformation         = si;


            //新建一个Workbook默认都会新建3个Sheet(标准的Excel文件有3个Sheet)。所以必须加入下面的创建Sheet的代码才能保证生成的文件正常
            HSSFSheet sheet = (HSSFSheet)hssfworkbook.CreateSheet("new sheet");
            // hssfworkbook.CreateSheet("Sheet1");
            // hssfworkbook.CreateSheet("Sheet2");
            // hssfworkbook.CreateSheet("Sheet3");

            //建创行
            Row row1 = sheet.CreateRow(0);

            //建单元格,比如创建A1位置的单元格:
            row1.Height = 500;
            CellStyle s = hssfworkbook.CreateCellStyle();

            s.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            s.FillPattern         = FillPatternType.SOLID_FOREGROUND;

            //第一列
            Cell cell1 = row1.CreateCell(0);

            cell1.CellStyle = s;

            Font font = hssfworkbook.CreateFont();

            font.FontName           = "宋体";
            font.FontHeightInPoints = 20;
            //设置字体加粗样式
            font.Boldweight = (short)FontBoldWeight.BOLD;
            cell1.CellStyle.SetFont(font);
            cell1.CellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            cell1.SetCellValue("《大明宫保护办月报月计划》——( 行政管理部)6月工作计划");
            cell1.CellStyle.BorderBottom = CellBorderType.THIN;
            cell1.CellStyle.BorderLeft   = CellBorderType.THIN;
            cell1.CellStyle.BorderRight  = CellBorderType.THIN;
            cell1.CellStyle.BorderTop    = CellBorderType.THIN;

            sheet.SetColumnWidth(0, 4 * 256);
            //第二列
            Cell cell2 = row1.CreateCell(1);

            cell2.CellStyle = s;
            sheet.SetColumnWidth(1, 12 * 256);
            //第三列
            Cell cell3 = row1.CreateCell(2);

            cell3.CellStyle = s;
            sheet.SetColumnWidth(2, 20 * 256);
            //第四列
            Cell cell4 = row1.CreateCell(3);

            cell4.CellStyle = s;
            sheet.SetColumnWidth(3, 25 * 256);
            //第五列
            Cell cell5 = row1.CreateCell(4);

            cell5.CellStyle = s;
            sheet.SetColumnWidth(4, 35 * 256);
            //第六列
            Cell cell6 = row1.CreateCell(5);

            cell6.CellStyle = s;
            sheet.SetColumnWidth(5, 20 * 256);
            //第七列
            Cell cell7 = row1.CreateCell(6);

            cell7.CellStyle = s;
            sheet.SetColumnWidth(6, 20 * 256);
            //第八列
            Cell cell8 = row1.CreateCell(7);

            cell8.CellStyle = s;
            sheet.SetColumnWidth(7, 20 * 256);
            //第9列
            Cell cell9 = row1.CreateCell(8);

            cell9.CellStyle = s;
            sheet.SetColumnWidth(8, 20 * 256);

            //第10列
            Cell cell10 = row1.CreateCell(9);

            cell10.CellStyle = s;
            sheet.SetColumnWidth(9, 20 * 256);

            //第11列
            Cell cell11 = row1.CreateCell(10);

            cell11.CellStyle = s;
            sheet.SetColumnWidth(10, 20 * 256);

            CellRangeAddress r = new CellRangeAddress(0, 0, 0, 10);

            sheet.AddMergedRegion(r);
            //sheet.AddMergedRegion(new NPOI.SS.Util.Region(0, 0, 0, 10));


            CreateRow2(hssfworkbook, sheet);

            CreateRow3_4(hssfworkbook, sheet);

            _1stModule _1StModule = new _1stModule()
            {
                _1stModuleName = "管理工作"
            };

            List <_2ndModule> _2NdModules = new List <_2ndModule>();
            _2ndModule        _2NdModule  = new _2ndModule {
                _2ndModuleName = "与战略地图要求相关"
            };
            DataItem item = new DataItem
            {
                Work           = "保护办十年工作总结",
                Result         = "30日前完成保护办十年工作总结(总结部分)",
                _1stWeek       = "根据主要领导意见进行修改",
                _2ndWeek       = "进行修改",
                _3rdWeek       = "进行修改",
                _4thWeek       = "完成总结",
                PersonInCharge = "雷博",
                Penaty         = "50"
            };

            _2NdModule.datas = new List <DataItem>();
            _2NdModule.datas.Add(item);
            _2NdModules.Add(_2NdModule);

            _2NdModule = new _2ndModule {
                _2ndModuleName = "与制度、流程、标准、工具相关"
            };
            _2NdModule.datas = new List <DataItem>();
            _2NdModules.Add(_2NdModule);
            _2NdModule = new _2ndModule {
                _2ndModuleName = "与企业文化相关"
            };
            _2NdModule.datas = new List <DataItem>();
            _2NdModules.Add(_2NdModule);
            _2NdModule = new _2ndModule {
                _2ndModuleName = "与团队建设相关"
            };
            item = new DataItem
            {
                Work           = "组织公文写作培训",
                Result         = "30日前完成培训",
                _1stWeek       = "",
                _2ndWeek       = "与培训老师确定时间和内容",
                _3rdWeek       = "与培训老师确定时间和内容",
                _4thWeek       = "完成培训",
                PersonInCharge = "王倩",
                Penaty         = "50"
            };
            _2NdModule.datas = new List <DataItem>();
            _2NdModule.datas.Add(item);
            _2NdModules.Add(_2NdModule);

            _1StModule._2ndModules = _2NdModules;

            CreateRowsByModules(_1StModule, sheet, hssfworkbook);


            //把这个HSSFWorkbook实例写入文件
            FileStream file = new FileStream("Example1.xls", FileMode.Create);

            hssfworkbook.Write(file);
            file.Close();
        }
Пример #8
0
        public async Task <ActionResult> ExportToExcel(MaterialUnloadingDetailQueryViewModel model)
        {
            IList <MaterialUnloadingDetail> lst = new List <MaterialUnloadingDetail>();

            using (MaterialUnloadingServiceClient client = new MaterialUnloadingServiceClient())
            {
                await Task.Run(() =>
                {
                    PagingConfig cfg = new PagingConfig()
                    {
                        IsPaging = false,
                        OrderBy  = "CreateTime Desc,Key.UnloadingKey,Key.ItemNo",
                        Where    = GetWhereCondition(model)
                    };
                    MethodReturnResult <IList <MaterialUnloadingDetail> > result = client.GetDetail(ref cfg);

                    if (result.Code == 0)
                    {
                        lst = 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);
            ICell  cell = null;
            IRow   row  = null;
            ISheet ws   = null;

            for (int j = 0; j < lst.Count; j++)
            {
                if (j % 65535 == 0)
                {
                    ws  = wb.CreateSheet();
                    row = ws.CreateRow(0);
                    #region //列名
                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_UnloadingNo);  //下料号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_ItemNo);  //项目号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_RouteOperationName);  //工序

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_ProductionLineCode);  //生产线

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_EquipmentCode);  //设备

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("下料日期");  //下料时间

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_UnloadingTime);  //下料时间

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_Operator);  //操作人

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_LoadingNo);  //对应上料号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_LoadingItemNo);  //对应上料项目号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_LineStoreName);  //线边仓

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingViewModel_OrderNumber);  //工单号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_MaterialCode);  //物料编码

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("物料名称");  //物料名称

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_MaterialLot);  //物料批号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialUnloadingDetailViewModel_UnloadingQty);  //下料数量

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

                MaterialUnloadingDetail obj = lst[j];
                MaterialUnloading       objMaterialUnloading = model.GetMaterialUnloading(obj.Key.UnloadingKey);
                Material m = model.GetMaterial(obj.MaterialCode);
                row = ws.CreateRow(j + 1);

                #region //数据
                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Key.UnloadingKey);  //下料号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Key.ItemNo);  //项目号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(objMaterialUnloading.RouteOperationName);  //工序

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(objMaterialUnloading.ProductionLineCode);  //生产线

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(objMaterialUnloading.EquipmentCode);  //设备

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0:yyyy-MM-dd}", objMaterialUnloading.UnloadingTime));  //下料日期

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", objMaterialUnloading.UnloadingTime));  //下料时间

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(objMaterialUnloading.Operator);  //操作人

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(@obj.LoadingKey);  //对应上料号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.LoadingItemNo);  //对应上料项目号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.LineStoreName);  //线边仓

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.OrderNumber);  //工单号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.MaterialCode);  //物料编码

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(m == null ? string.Empty : m.Name);  //物料名称

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.MaterialLot);  //物料批号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.UnloadingQty);  //下料数量

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Editor);  //编辑人

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime));  //编辑时间
                #endregion
            }

            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return(File(ms, "application/vnd.ms-excel", "MaterialUnloadingData.xls"));
        }
Пример #9
0
        private static void CreateRow3_4(HSSFWorkbook hssfworkbook, HSSFSheet sheet)
        {
            Font font = hssfworkbook.CreateFont();

            font.FontName           = "宋体";
            font.FontHeightInPoints = 12;
            //设置字体加粗样式
            font.Boldweight = (short)FontBoldWeight.BOLD;

            CellStyle style = hssfworkbook.CreateCellStyle();

            //设置边框
            style.BorderTop    = CellBorderType.THIN;
            style.BorderBottom = CellBorderType.THIN;
            style.BorderLeft   = CellBorderType.THIN;
            style.BorderRight  = CellBorderType.THIN;
            //设置单元格的样式:水平对齐居中
            style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
            style.VerticalAlignment = VerticalAlignment.CENTER;

            style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            style.FillPattern         = FillPatternType.SOLID_FOREGROUND;
            //使用SetFont方法将字体样式添加到单元格样式中
            style.SetFont(font);

            //建创行
            Row row2 = sheet.CreateRow(2);

            string[] strs = new string[] { "NO", "一级模块", "二级模块", "工作\n安排", "结果定义" };
            for (int i = 0; i < strs.Length; i++)
            {
                Cell cell = row2.CreateCell(i);
                //cell.CellStyle.SetFont(font);
                cell.SetCellValue(strs[i]);
                cell.CellStyle = style;
            }

            var newcell = row2.CreateCell(5);

            newcell.CellStyle = style;
            newcell.SetCellValue("过程节点和完成时间");
            newcell = row2.CreateCell(6);
            newcell = row2.CreateCell(7);
            newcell = row2.CreateCell(8);
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 5, 2, 8));

            newcell           = row2.CreateCell(9);
            newcell.CellStyle = style;
            newcell.SetCellValue("责任人");

            newcell           = row2.CreateCell(10);
            newcell.CellStyle = style;
            newcell.SetCellValue("自罚承诺");

            Row row3 = sheet.CreateRow(3);

            string[] strs2 = new string[] { "", "", "", "", "可衡量、有价值、看得见、摸得着", "第一周", "第二周", "第三周", "第四周", "", "" };

            for (int i = 0; i < strs2.Length; i++)
            {
                Cell cell = row3.CreateCell(i);
                //cell.CellStyle.SetFont(font);
                cell.SetCellValue(strs2[i]);
                cell.CellStyle = style;
            }

            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 0, 3, 0));
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 1, 3, 1));
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 2, 3, 2));
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 3, 3, 3));
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 9, 3, 9));
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(2, 10, 3, 10));
        }
Пример #10
0
        public static void CreateRow2(HSSFWorkbook hssfworkbook, HSSFSheet sheet)
        {
            NPOI.SS.UserModel.Font font = hssfworkbook.CreateFont();
            font.FontName           = "宋体";
            font.FontHeightInPoints = 12;
            //设置字体加粗样式
            font.Boldweight = (short)FontBoldWeight.BOLD;

            CellStyle style = hssfworkbook.CreateCellStyle();

            //设置边框
            style.BorderTop    = CellBorderType.THIN;
            style.BorderBottom = CellBorderType.THIN;
            style.BorderLeft   = CellBorderType.THIN;
            style.BorderRight  = CellBorderType.THIN;
            //设置单元格的样式:水平对齐居中
            style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;

            style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
            style.FillPattern         = FillPatternType.SOLID_FOREGROUND;
            //使用SetFont方法将字体样式添加到单元格样式中
            style.SetFont(font);


            //建创行
            Row row2 = sheet.CreateRow(1);

            row2.Height = 350;
            //第一列
            Cell cell = row2.CreateCell(0);

            cell.CellStyle.SetFont(font);
            cell.SetCellValue("报告人");
            cell.CellStyle = style;

            cell           = row2.CreateCell(1);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(1, 0, 1, 1));

            cell           = row2.CreateCell(2);
            cell.CellStyle = style;
            cell.SetCellValue("雷博");

            cell           = row2.CreateCell(3);
            cell.CellStyle = style;
            cell.SetCellValue("部门");

            cell           = row2.CreateCell(4);
            cell.CellStyle = style;
            cell.SetCellValue("行政管理部");

            cell           = row2.CreateCell(5);
            cell.CellStyle = style;
            cell.SetCellValue("开始日期");

            cell           = row2.CreateCell(6);
            cell.CellStyle = style;
            cell.SetCellValue("2019.6.1");
            cell           = row2.CreateCell(7);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(1, 6, 1, 7));

            cell           = row2.CreateCell(8);
            cell.CellStyle = style;
            cell.SetCellValue("结束日期");

            cell           = row2.CreateCell(9);
            cell.CellStyle = style;
            cell.SetCellValue("2019.6.30");
            cell           = row2.CreateCell(10);
            cell.CellStyle = style;
            sheet.AddMergedRegion(new NPOI.SS.Util.Region(1, 9, 1, 10));
        }
Пример #11
0
        private static void CreateRowsByModules(_1stModule module, HSSFSheet sheet, HSSFWorkbook hssfworkbook)
        {
            int offset = 4;

            for (int i = 0; i < module._2ndModules.Count; i++)
            {
                for (int j = 0; j < module._2ndModules[i].datas.Count; j++)
                {
                    offset++;
                    Row row2 = sheet.CreateRow(offset);

                    Font font = hssfworkbook.CreateFont();
                    font.FontName           = "宋体";
                    font.FontHeightInPoints = 12;
                    //设置字体加粗样式
                    font.Boldweight = (short)FontBoldWeight.BOLD;

                    CellStyle style = hssfworkbook.CreateCellStyle();
                    //设置边框
                    style.BorderTop    = CellBorderType.THIN;
                    style.BorderBottom = CellBorderType.THIN;
                    style.BorderLeft   = CellBorderType.THIN;
                    style.BorderRight  = CellBorderType.THIN;
                    style.WrapText     = true;
                    //设置单元格的样式:水平对齐居中
                    style.Alignment         = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
                    style.VerticalAlignment = VerticalAlignment.CENTER;

                    style.FillForegroundColor = HSSFColor.LIGHT_GREEN.index;
                    style.FillPattern         = FillPatternType.SOLID_FOREGROUND;
                    //使用SetFont方法将字体样式添加到单元格样式中
                    style.SetFont(font);

                    var cell = row2.CreateCell(3);
                    cell.SetCellValue(module._2ndModules[i].datas[j].Work);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(4);
                    cell.SetCellValue(module._2ndModules[i].datas[j].Result);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(5);
                    cell.SetCellValue(module._2ndModules[i].datas[j]._1stWeek);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(6);
                    cell.SetCellValue(module._2ndModules[i].datas[j]._2ndWeek);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(7);
                    cell.SetCellValue(module._2ndModules[i].datas[j]._3rdWeek);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(8);
                    cell.SetCellValue(module._2ndModules[i].datas[j]._4thWeek);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(9);
                    cell.SetCellValue(module._2ndModules[i].datas[j].PersonInCharge);
                    cell.CellStyle = style;

                    cell = row2.CreateCell(10);
                    cell.SetCellValue(module._2ndModules[i].datas[j].Penaty);
                    cell.CellStyle = style;
                }
            }
        }
Пример #12
0
        /// <summary>
        /// 生成EXECL文件,通过读取DataTable和列头映射信息
        /// </summary>
        /// <param name="dt">数据源</param>
        /// <param name="ColumnInfoList">列字段映射信息</param>
        /// <returns>文件流</returns>
        public static MemoryStream Export(DataTable dt, List <ColumnInfo> ColumnInfoList)
        {
            if (dt == null || ColumnInfoList == null)
            {
                throw new ArgumentNullException();
            }
            int rowHeight = 20;
            //每个标签页最多行数
            int          sheetRow = 65536;
            HSSFWorkbook workbook = new HSSFWorkbook();

            //文本样式
            ICellStyle centerStyle = workbook.CreateCellStyle();

            centerStyle.VerticalAlignment = VerticalAlignment.CENTER;
            centerStyle.Alignment         = HorizontalAlignment.CENTER;

            ICellStyle leftStyle = workbook.CreateCellStyle();

            leftStyle.VerticalAlignment = VerticalAlignment.CENTER;
            leftStyle.Alignment         = HorizontalAlignment.LEFT;

            ICellStyle rightStyle = workbook.CreateCellStyle();

            rightStyle.VerticalAlignment = VerticalAlignment.CENTER;
            rightStyle.Alignment         = HorizontalAlignment.RIGHT;

            //寻找列头和DataTable之间映射关系
            foreach (DataColumn col in dt.Columns)
            {
                ColumnInfo info = ColumnInfoList.FirstOrDefault <ColumnInfo>(e => e.Field.Equals(col.ColumnName, StringComparison.OrdinalIgnoreCase));
                if (info != null)
                {
                    switch (info.Align.ToLower())
                    {
                    case "left":
                        info.Style = leftStyle;
                        break;

                    case "center":
                        info.Style = centerStyle;
                        break;

                    case "right":
                        info.Style = rightStyle;
                        break;
                    }
                    info.IsMapDT = true;
                }
            }

            int sheetNum = (int)Math.Ceiling(dt.Rows.Count * 1.0 / MAX_ROW_INDEX);

            //最多生成5个标签页的数据
            sheetNum = sheetNum > 3 ? 3 : (sheetNum == 0 ? 1 : sheetNum);
            ICell  cell      = null;
            object cellValue = null;

            for (int sheetIndex = 0; sheetIndex < sheetNum; sheetIndex++)
            {
                ISheet sheet = workbook.CreateSheet();
                sheet.CreateFreezePane(0, 1, 0, 1);
                //输出表头
                IRow headerRow = sheet.CreateRow(0);
                //设置行高
                headerRow.HeightInPoints = rowHeight;
                //首行样式
                ICellStyle HeaderStyle = workbook.CreateCellStyle();
                HeaderStyle.FillPattern         = FillPatternType.SOLID_FOREGROUND;
                HeaderStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.GREY_25_PERCENT.index;
                IFont font = workbook.CreateFont();
                font.Boldweight = short.MaxValue;
                HeaderStyle.SetFont(font);
                HeaderStyle.VerticalAlignment = VerticalAlignment.CENTER;;
                HeaderStyle.Alignment         = HorizontalAlignment.CENTER;


                //输出表头信息 并设置表头样式
                int i = 0;
                foreach (var data in ColumnInfoList)
                {
                    cell = headerRow.CreateCell(i);
                    cell.SetCellValue(data.Header.Trim());
                    cell.CellStyle = HeaderStyle;
                    i++;
                }

                //开始循环所有行
                int iRow = 1;

                int startRow = sheetIndex * (sheetRow - 1);
                int endRow   = (sheetIndex + 1) * (sheetRow - 1);
                endRow = endRow <= dt.Rows.Count ? endRow : dt.Rows.Count;

                for (int rowIndex = startRow; rowIndex < endRow; rowIndex++)
                {
                    IRow row = sheet.CreateRow(iRow);
                    row.HeightInPoints = rowHeight;
                    i = 0;
                    foreach (var item in ColumnInfoList)
                    {
                        cell = row.CreateCell(i);
                        if (item.IsMapDT)
                        {
                            cellValue = dt.Rows[rowIndex][item.Field];
                            cell.SetCellValue(cellValue != DBNull.Value ? cellValue.ToString() : string.Empty);
                            cell.CellStyle = item.Style;
                        }
                        i++;
                    }
                    iRow++;
                }

                //自适应列宽度
                for (int j = 0; j < ColumnInfoList.Count; j++)
                {
                    sheet.AutoSizeColumn(j);
                    int width = sheet.GetColumnWidth(j) + 2560;
                    sheet.SetColumnWidth(j, width > MAX_COLUMN_WIDTH ? MAX_COLUMN_WIDTH : width);
                }
            }

            MemoryStream ms = new MemoryStream();

            workbook.Write(ms);
            return(ms);
        }
Пример #13
0
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            _cusName    = DTRequest.GetFormString("txtCusName");
            _cid        = DTRequest.GetFormString("hCusId");
            _customer   = DTRequest.GetFormString("txtCustomer");
            _hcustomer  = DTRequest.GetFormString("hCustomer");
            _type       = DTRequest.GetFormString("ddltype");
            _sign       = DTRequest.GetFormString("ddlsign");
            _money1     = DTRequest.GetFormString("txtMoney1");
            _nature     = DTRequest.GetFormString("ddlnature");
            _sdate      = DTRequest.GetFormString("txtsDate");
            _edate      = DTRequest.GetFormString("txteDate");
            _sdate1     = DTRequest.GetFormString("txtsDate1");
            _edate1     = DTRequest.GetFormString("txteDate1");
            _name       = DTRequest.GetFormString("txtName");
            _address    = DTRequest.GetFormString("txtAddress");
            _sign1      = DTRequest.GetFormString("ddlsign1");
            _money2     = DTRequest.GetFormString("txtMoney2");
            _person1    = DTRequest.GetFormString("txtPerson1");
            _person2    = DTRequest.GetFormString("txtPerson2");
            _person3    = DTRequest.GetFormString("txtPerson3");
            _person4    = DTRequest.GetFormString("txtPerson4");
            _person5    = DTRequest.GetFormString("txtPerson5");
            _oid        = DTRequest.GetFormString("txtOrderID");
            _chk        = DTRequest.GetFormString("txtChk");
            _status     = DTRequest.GetFormString("ddlstatus");
            _lockstatus = DTRequest.GetFormString("ddllock");
            _area       = DTRequest.GetFormString("ddlarea");
            _sdate2     = DTRequest.GetFormString("txtsDate2");
            _edate2     = DTRequest.GetFormString("txteDate2");
            _check      = DTRequest.GetFormString("ddlcheck");
            _self       = DTRequest.GetFormString("self");
            _detail     = DTRequest.GetFormString("txtDetails");
            string _where = "";
            Dictionary <string, string> dict = getDict(out _where);
            DataTable dt = new BLL.finance().getReconciliationDetail(dict, this.pageSize, this.page, "o_id asc", out this.totalCount, out _p24, out _p25, out _p26, out _p13, out _p14, false).Tables[0];

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=客户对账明细.xlsx"); //HttpUtility.UrlEncode(fileName));
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet       sheet        = hssfworkbook.CreateSheet("对账明细");
            IFont        font         = hssfworkbook.CreateFont();

            font.Boldweight         = short.MaxValue;
            font.FontHeightInPoints = 11;

            #region 表格样式
            //设置单元格的样式:水平垂直对齐居中
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            cellStyle.Alignment         = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.BorderBottom      = BorderStyle.Thin;
            cellStyle.BorderLeft        = BorderStyle.Thin;
            cellStyle.BorderRight       = BorderStyle.Thin;
            cellStyle.BorderTop         = BorderStyle.Thin;
            cellStyle.BottomBorderColor = HSSFColor.Black.Index;
            cellStyle.LeftBorderColor   = HSSFColor.Black.Index;
            cellStyle.RightBorderColor  = HSSFColor.Black.Index;
            cellStyle.TopBorderColor    = HSSFColor.Black.Index;
            cellStyle.WrapText          = true;//自动换行

            //设置表头的样式:水平垂直对齐居中,加粗
            ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
            titleCellStyle.Alignment           = HorizontalAlignment.Center;
            titleCellStyle.VerticalAlignment   = VerticalAlignment.Center;
            titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
            titleCellStyle.FillPattern         = FillPattern.SparseDots;        //图案样式
            titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
                                                                                //设置边框
            titleCellStyle.BorderBottom      = BorderStyle.Thin;
            titleCellStyle.BorderLeft        = BorderStyle.Thin;
            titleCellStyle.BorderRight       = BorderStyle.Thin;
            titleCellStyle.BorderTop         = BorderStyle.Thin;
            titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
            titleCellStyle.LeftBorderColor   = HSSFColor.Black.Index;
            titleCellStyle.RightBorderColor  = HSSFColor.Black.Index;
            titleCellStyle.TopBorderColor    = HSSFColor.Black.Index;
            //设置字体
            titleCellStyle.SetFont(font);
            #endregion
            //表头
            IRow headRow = sheet.CreateRow(0);
            headRow.HeightInPoints = 25;

            headRow.CreateCell(0).SetCellValue("订单号");
            headRow.CreateCell(1).SetCellValue("客源");
            headRow.CreateCell(2).SetCellValue("活动日期");
            headRow.CreateCell(3).SetCellValue("活动地点/活动名称");
            headRow.CreateCell(4).SetCellValue("对账标识");
            headRow.CreateCell(5).SetCellValue("对账金额");
            headRow.CreateCell(6).SetCellValue("业务性质/明细");
            headRow.CreateCell(7).SetCellValue("业务说明");
            headRow.CreateCell(8).SetCellValue("表达式");
            headRow.CreateCell(9).SetCellValue("审核");
            headRow.CreateCell(10).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "应收" : "应付"));
            headRow.CreateCell(11).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "已收" : "已付"));
            headRow.CreateCell(12).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "对账金额/") + (_type == "True" ? "未收" : "未付"));

            headRow.GetCell(0).CellStyle  = titleCellStyle;
            headRow.GetCell(1).CellStyle  = titleCellStyle;
            headRow.GetCell(2).CellStyle  = titleCellStyle;
            headRow.GetCell(3).CellStyle  = titleCellStyle;
            headRow.GetCell(4).CellStyle  = titleCellStyle;
            headRow.GetCell(5).CellStyle  = titleCellStyle;
            headRow.GetCell(6).CellStyle  = titleCellStyle;
            headRow.GetCell(7).CellStyle  = titleCellStyle;
            headRow.GetCell(8).CellStyle  = titleCellStyle;
            headRow.GetCell(9).CellStyle  = titleCellStyle;
            headRow.GetCell(10).CellStyle = titleCellStyle;
            headRow.GetCell(11).CellStyle = titleCellStyle;
            headRow.GetCell(12).CellStyle = titleCellStyle;

            sheet.SetColumnWidth(0, 15 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 20 * 256);
            sheet.SetColumnWidth(5, 10 * 256);
            sheet.SetColumnWidth(6, 20 * 256);
            sheet.SetColumnWidth(7, 15 * 256);
            sheet.SetColumnWidth(8, 15 * 256);
            sheet.SetColumnWidth(9, 20 * 256);
            sheet.SetColumnWidth(10, 15 * 256);
            sheet.SetColumnWidth(11, 15 * 256);
            sheet.SetColumnWidth(12, 15 * 256);

            if (dt != null)
            {
                int       rowIndex = 1;
                DataTable finDt    = null;
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    DataSet ds = new BLL.finance().GetList(0, "fin_oid='" + dt.Rows[i]["o_id"] + "'" + _where, _chk, "");
                    if (ds != null && ds.Tables.Count > 0)
                    {
                        finDt = ds.Tables[0];
                        if (finDt != null && finDt.Rows.Count > 0)
                        {
                            for (int j = 0; j < finDt.Rows.Count; j++)
                            {
                                IRow row = sheet.CreateRow(rowIndex);
                                row.HeightInPoints = 22;
                                row.CreateCell(0).SetCellValue(dt.Rows[i]["o_id"].ToString());
                                row.CreateCell(1).SetCellValue(dt.Rows[i]["c_name"].ToString());
                                row.CreateCell(2).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["o_sdate"]).Value.ToString("yyyy-MM-dd") + "/" + ConvertHelper.toDate(dt.Rows[i]["o_edate"]).Value.ToString("yyyy-MM-dd"));
                                row.CreateCell(3).SetCellValue(dt.Rows[i]["o_address"].ToString() + "/" + dt.Rows[i]["o_content"].ToString());
                                row.CreateCell(4).SetCellValue(finDt.Rows[j]["chk"].ToString());
                                row.CreateCell(5).SetCellValue(finDt.Rows[j]["chkMoney"].ToString());
                                row.CreateCell(6).SetCellValue(finDt.Rows[j]["na_name"] + "/" + finDt.Rows[j]["fin_detail"]);
                                row.CreateCell(7).SetCellValue(finDt.Rows[j]["fin_illustration"].ToString());
                                row.CreateCell(8).SetCellValue(finDt.Rows[j]["fin_expression"] + "=" + finDt.Rows[j]["fin_money"]);
                                row.CreateCell(9).SetCellValue(BusinessDict.checkStatus()[Utils.ObjToByte(finDt.Rows[j]["fin_flag"])]);
                                row.CreateCell(10).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["fcMoney"] + "/") + dt.Rows[i]["fin_money"]);
                                row.CreateCell(11).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["chkMoney"] + "/") + dt.Rows[i]["rpd_money"]);
                                row.CreateCell(12).SetCellValue((string.IsNullOrEmpty(_chk) || _chk == "空" ? "" : "" + dt.Rows[i]["unChkMoney"] + "/") + dt.Rows[i]["unReceiptPay"]);

                                row.GetCell(0).CellStyle  = cellStyle;
                                row.GetCell(1).CellStyle  = cellStyle;
                                row.GetCell(2).CellStyle  = cellStyle;
                                row.GetCell(3).CellStyle  = cellStyle;
                                row.GetCell(4).CellStyle  = cellStyle;
                                row.GetCell(5).CellStyle  = cellStyle;
                                row.GetCell(6).CellStyle  = cellStyle;
                                row.GetCell(7).CellStyle  = cellStyle;
                                row.GetCell(8).CellStyle  = cellStyle;
                                row.GetCell(9).CellStyle  = cellStyle;
                                row.GetCell(10).CellStyle = cellStyle;
                                row.GetCell(11).CellStyle = cellStyle;
                                row.GetCell(12).CellStyle = cellStyle;
                                rowIndex++;
                            }
                        }
                    }
                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }
Пример #14
0
        protected void btnExcel_Click(object sender, EventArgs e)
        {
            _cusName   = DTRequest.GetString("txtCusName");
            _cid       = DTRequest.GetString("hCusId");
            _method    = DTRequest.GetFormString("ddlmethod");
            _isconfirm = DTRequest.GetFormString("ddlisConfirm");
            _sforedate = DTRequest.GetFormString("txtsforedate");
            _eforedate = DTRequest.GetFormString("txteforedate");
            _sdate     = DTRequest.GetFormString("txtsdate");
            _edate     = DTRequest.GetFormString("txtedate");
            _num       = DTRequest.GetFormString("txtNum");
            _chk       = DTRequest.GetFormString("txtChk");
            _numdate   = DTRequest.GetFormString("txtNumDate");
            _self      = DTRequest.GetFormString("self");
            _sign      = DTRequest.GetFormString("ddlsign");
            _money     = DTRequest.GetFormString("txtMoney");
            _moneyType = DTRequest.GetFormString("ddlmoneyType");
            _type      = DTRequest.GetFormString("ddlType");
            _addperson = DTRequest.GetFormString("txtAddPerson");
            BLL.ReceiptPay bll = new BLL.ReceiptPay();
            DataTable      dt  = bll.GetList(this.pageSize, this.page, "rp_type=1 " + CombSqlTxt(), "isnull(rp_date,'3000-01-01') desc,isnull(pm_sort,-1) asc,rp_id desc", out this.totalCount, out _tmoney, out _tunmoney, false).Tables[0];

            HttpContext.Current.Response.Clear();
            HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=收款通知列表.xlsx"); //HttpUtility.UrlEncode(fileName));
            HttpContext.Current.Response.ContentType = "application/vnd.ms-excel";

            HSSFWorkbook hssfworkbook = new HSSFWorkbook();
            ISheet       sheet        = hssfworkbook.CreateSheet("明细");
            IFont        font         = hssfworkbook.CreateFont();

            font.Boldweight         = short.MaxValue;
            font.FontHeightInPoints = 11;

            #region 表格样式
            //设置单元格的样式:水平垂直对齐居中
            ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
            cellStyle.Alignment         = HorizontalAlignment.Center;
            cellStyle.VerticalAlignment = VerticalAlignment.Center;
            cellStyle.BorderBottom      = BorderStyle.Thin;
            cellStyle.BorderLeft        = BorderStyle.Thin;
            cellStyle.BorderRight       = BorderStyle.Thin;
            cellStyle.BorderTop         = BorderStyle.Thin;
            cellStyle.BottomBorderColor = HSSFColor.Black.Index;
            cellStyle.LeftBorderColor   = HSSFColor.Black.Index;
            cellStyle.RightBorderColor  = HSSFColor.Black.Index;
            cellStyle.TopBorderColor    = HSSFColor.Black.Index;
            cellStyle.WrapText          = true;//自动换行

            //设置表头的样式:水平垂直对齐居中,加粗
            ICellStyle titleCellStyle = hssfworkbook.CreateCellStyle();
            titleCellStyle.Alignment           = HorizontalAlignment.Center;
            titleCellStyle.VerticalAlignment   = VerticalAlignment.Center;
            titleCellStyle.FillForegroundColor = HSSFColor.Grey25Percent.Index; //图案颜色
            titleCellStyle.FillPattern         = FillPattern.SparseDots;        //图案样式
            titleCellStyle.FillBackgroundColor = HSSFColor.Grey25Percent.Index; //背景颜色
            //设置边框
            titleCellStyle.BorderBottom      = BorderStyle.Thin;
            titleCellStyle.BorderLeft        = BorderStyle.Thin;
            titleCellStyle.BorderRight       = BorderStyle.Thin;
            titleCellStyle.BorderTop         = BorderStyle.Thin;
            titleCellStyle.BottomBorderColor = HSSFColor.Black.Index;
            titleCellStyle.LeftBorderColor   = HSSFColor.Black.Index;
            titleCellStyle.RightBorderColor  = HSSFColor.Black.Index;
            titleCellStyle.TopBorderColor    = HSSFColor.Black.Index;
            //设置字体
            titleCellStyle.SetFont(font);
            #endregion
            //表头
            IRow headRow = sheet.CreateRow(0);
            headRow.HeightInPoints = 25;

            headRow.CreateCell(0).SetCellValue("收款对象");
            headRow.CreateCell(1).SetCellValue("凭证");
            headRow.CreateCell(2).SetCellValue("收款内容");
            headRow.CreateCell(3).SetCellValue("收款金额");
            headRow.CreateCell(4).SetCellValue("未分配金额");
            headRow.CreateCell(5).SetCellValue("预收日期");
            headRow.CreateCell(6).SetCellValue("收款方式");
            headRow.CreateCell(7).SetCellValue("实收日期");
            headRow.CreateCell(8).SetCellValue("申请人");
            headRow.CreateCell(9).SetCellValue("确认收款");

            headRow.GetCell(0).CellStyle = titleCellStyle;
            headRow.GetCell(1).CellStyle = titleCellStyle;
            headRow.GetCell(2).CellStyle = titleCellStyle;
            headRow.GetCell(3).CellStyle = titleCellStyle;
            headRow.GetCell(4).CellStyle = titleCellStyle;
            headRow.GetCell(5).CellStyle = titleCellStyle;
            headRow.GetCell(6).CellStyle = titleCellStyle;
            headRow.GetCell(7).CellStyle = titleCellStyle;
            headRow.GetCell(8).CellStyle = titleCellStyle;
            headRow.GetCell(9).CellStyle = titleCellStyle;

            sheet.SetColumnWidth(0, 15 * 256);
            sheet.SetColumnWidth(1, 20 * 256);
            sheet.SetColumnWidth(2, 20 * 256);
            sheet.SetColumnWidth(3, 20 * 256);
            sheet.SetColumnWidth(4, 20 * 256);
            sheet.SetColumnWidth(5, 15 * 256);
            sheet.SetColumnWidth(6, 20 * 256);
            sheet.SetColumnWidth(7, 20 * 256);
            sheet.SetColumnWidth(8, 20 * 256);
            sheet.SetColumnWidth(9, 20 * 256);

            if (dt != null)
            {
                for (int i = 0; i < dt.Rows.Count; i++)
                {
                    IRow row = sheet.CreateRow(i + 1);
                    row.HeightInPoints = 22;
                    row.CreateCell(0).SetCellValue(dt.Rows[i]["c_name"].ToString() + (Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isExpect"]), false)?"[预]":""));
                    row.CreateCell(1).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["ce_num"]));
                    row.CreateCell(2).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_content"]));
                    row.CreateCell(3).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_money"]));
                    row.CreateCell(4).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["undistribute"]));
                    row.CreateCell(5).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_foredate"]).Value.ToString("yyyy-MM-dd"));
                    row.CreateCell(6).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["pm_name"]));
                    row.CreateCell(7).SetCellValue(ConvertHelper.toDate(dt.Rows[i]["rp_date"]) == null ? "" : ConvertHelper.toDate(dt.Rows[i]["rp_date"]).Value.ToString("yyyy-MM-dd"));
                    row.CreateCell(8).SetCellValue(Utils.ObjectToStr(dt.Rows[i]["rp_personName"]));
                    row.CreateCell(9).SetCellValue(Utils.StrToBool(Utils.ObjectToStr(dt.Rows[i]["rp_isConfirm"]), false) ? "已收款" : "待收款");

                    row.GetCell(0).CellStyle = cellStyle;
                    row.GetCell(1).CellStyle = cellStyle;
                    row.GetCell(2).CellStyle = cellStyle;
                    row.GetCell(3).CellStyle = cellStyle;
                    row.GetCell(4).CellStyle = cellStyle;
                    row.GetCell(5).CellStyle = cellStyle;
                    row.GetCell(6).CellStyle = cellStyle;
                    row.GetCell(7).CellStyle = cellStyle;
                    row.GetCell(8).CellStyle = cellStyle;
                    row.GetCell(9).CellStyle = cellStyle;
                }
            }

            MemoryStream file = new MemoryStream();
            hssfworkbook.Write(file);

            HttpContext.Current.Response.BinaryWrite(file.GetBuffer());
            HttpContext.Current.Response.End();
        }
Пример #15
0
        public static Dictionary <TextStyle, CellStyle> CreateDefaultStyels(HSSFWorkbook hssfworkbook)
        {
            var styles = new Dictionary <TextStyle, CellStyle>();

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 16;
                font.Boldweight         = 3000;
                font.FontName           = "Arial";
                style.SetFont(font);

                //style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                //style.FillPattern = FillPatternType.SOLID_FOREGROUND;

                styles.Add(TextStyle.Header1, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 18;
                font.Boldweight         = 2500;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.Alignment = HorizontalAlignment.CENTER;

                styles.Add(TextStyle.BigHeader, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 14;
                font.Boldweight         = 2000;
                font.FontName           = "Arial";
                style.SetFont(font);

                //style.FillForegroundColor = HSSFColor.GREY_25_PERCENT.index;
                //style.FillPattern = FillPatternType.SOLID_FOREGROUND;

                styles.Add(TextStyle.Header2, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 14;
                font.Boldweight         = 2000;
                font.FontName           = "Arial";
                style.SetFont(font);
                style.Alignment = HorizontalAlignment.CENTER;

                styles.Add(TextStyle.Header2Simple, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 12;
                font.Boldweight         = 1000;
                font.FontName           = "Arial";
                style.SetFont(font);

                //style.FillForegroundColor = HSSFColor.WHITE.index;
                //style.FillPattern =  FillPatternType.FINE_DOTS;
                //style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                styles.Add(TextStyle.Header3, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 12;
                font.Boldweight         = 1000;
                font.FontName           = "Arial";
                style.SetFont(font);
                style.Alignment = HorizontalAlignment.CENTER;

                styles.Add(TextStyle.Header3Simple, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 12;
                font.Boldweight         = 1000;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.FINE_DOTS;
                style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.Alignment = HorizontalAlignment.CENTER;

                styles.Add(TextStyle.Header3Center, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 12;
                font.Boldweight         = 1000;
                font.FontName           = "Arial";
                style.SetFont(font);

                //style.FillForegroundColor = HSSFColor.WHITE.index;
                //style.FillPattern = HSSFCellStyle.LESS_DOTS;
                //style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.WrapText = true;

                styles.Add(TextStyle.Header3Wrap, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);
                style.Alignment = HorizontalAlignment.LEFT;

                styles.Add(TextStyle.NormalText, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                font.IsItalic           = true;

                style.SetFont(font);
                style.Alignment = HorizontalAlignment.LEFT;

                styles.Add(TextStyle.NormalTextItalic, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.FINE_DOTS;
                style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.BorderTop    = CellBorderType.THIN;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderLeft   = CellBorderType.THIN;
                style.BorderRight  = CellBorderType.THIN;

                style.VerticalAlignment = VerticalAlignment.CENTER;

                style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
                style.TopBorderColor    = HSSFColor.GREY_50_PERCENT.index;
                style.LeftBorderColor   = HSSFColor.GREY_50_PERCENT.index;
                style.RightBorderColor  = HSSFColor.GREY_50_PERCENT.index;

                style.WrapText = true;

                styles.Add(TextStyle.TableRowDark, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.FINE_DOTS;
                style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.Alignment         = HorizontalAlignment.CENTER;
                style.VerticalAlignment = VerticalAlignment.TOP;

                styles.Add(TextStyle.TableRowDarkCenter, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.FINE_DOTS;
                style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.Alignment         = HorizontalAlignment.RIGHT;
                style.VerticalAlignment = VerticalAlignment.TOP;

                DataFormat format = hssfworkbook.CreateDataFormat();
                style.DataFormat = format.GetFormat("# ### ### ##0.00");

                styles.Add(TextStyle.TableRowDarkMoney, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.FINE_DOTS;
                style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.Alignment         = HorizontalAlignment.LEFT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.WrapText          = true;

                styles.Add(TextStyle.NormalDarkLeftTopWrap, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.SOLID_FOREGROUND;

                style.VerticalAlignment = VerticalAlignment.CENTER;

                style.BorderTop    = CellBorderType.THIN;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderLeft   = CellBorderType.THIN;
                style.BorderRight  = CellBorderType.THIN;

                style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
                style.TopBorderColor    = HSSFColor.GREY_50_PERCENT.index;
                style.LeftBorderColor   = HSSFColor.GREY_50_PERCENT.index;
                style.RightBorderColor  = HSSFColor.GREY_50_PERCENT.index;

                style.WrapText = true;

                styles.Add(TextStyle.TableRowNormal, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.WrapText = true;

                styles.Add(TextStyle.WrapText, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.Boldweight         = 3000;
                font.FontName           = "Arial";
                style.SetFont(font);

                styles.Add(TextStyle.BoldText, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.Boldweight         = 3000;
                font.FontName           = "Arial";
                style.Alignment         = HorizontalAlignment.CENTER;
                style.SetFont(font);

                styles.Add(TextStyle.BoldTextAlignCenter, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.Alignment         = HorizontalAlignment.CENTER;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                styles.Add(TextStyle.NormalAlignCenter, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.Alignment         = HorizontalAlignment.LEFT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                styles.Add(TextStyle.NormalLeftTop, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.Alignment         = HorizontalAlignment.LEFT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                style.WrapText = true;

                styles.Add(TextStyle.NormalLeftTopWrap, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.Alignment         = HorizontalAlignment.RIGHT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                styles.Add(TextStyle.NormalRightTop, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                font.Boldweight         = 3000;

                style.Alignment         = HorizontalAlignment.RIGHT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                styles.Add(TextStyle.BoldRightTop, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                //font.Underline
                font.Color = HSSFColor.BLUE.index;

                style.SetFont(font);

                styles.Add(TextStyle.Hyperlink, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.Boldweight         = 3000;
                font.FontName           = "Arial";
                style.Alignment         = HorizontalAlignment.CENTER;
                style.SetFont(font);

                styles.Add(TextStyle.TableHeader, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);
                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy");
                styles.Add(TextStyle.SysDateTime, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);
                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
                styles.Add(TextStyle.SysNumber, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);
                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

                styles.Add(TextStyle.NormalPercent, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                style.SetFont(font);
                DataFormat format = hssfworkbook.CreateDataFormat();
                style.DataFormat = format.GetFormat("# ### ### ##0.00");
                styles.Add(TextStyle.SysMoney, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                font.Boldweight         = 3000;

                style.Alignment         = HorizontalAlignment.RIGHT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                style.BorderTop    = CellBorderType.THIN;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderLeft   = CellBorderType.THIN;
                style.BorderRight  = CellBorderType.THIN;

                style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
                style.TopBorderColor    = HSSFColor.GREY_50_PERCENT.index;
                style.LeftBorderColor   = HSSFColor.GREY_50_PERCENT.index;
                style.RightBorderColor  = HSSFColor.GREY_50_PERCENT.index;

                styles.Add(TextStyle.TableTotal, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.FontName           = "Arial";
                font.Boldweight         = 3000;

                style.Alignment         = HorizontalAlignment.RIGHT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.SetFont(font);

                style.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");

                style.BorderTop    = CellBorderType.THIN;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderLeft   = CellBorderType.THIN;
                style.BorderRight  = CellBorderType.THIN;

                style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
                style.TopBorderColor    = HSSFColor.GREY_50_PERCENT.index;
                style.LeftBorderColor   = HSSFColor.GREY_50_PERCENT.index;
                style.RightBorderColor  = HSSFColor.GREY_50_PERCENT.index;

                styles.Add(TextStyle.TableTotalPercent, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 10;
                font.Boldweight         = 3000;
                font.FontName           = "Arial";

                style.Alignment         = HorizontalAlignment.CENTER;
                style.VerticalAlignment = VerticalAlignment.CENTER;

                style.BorderTop    = CellBorderType.THIN;
                style.BorderBottom = CellBorderType.THIN;
                style.BorderLeft   = CellBorderType.THIN;
                style.BorderRight  = CellBorderType.THIN;

                style.BottomBorderColor = HSSFColor.GREY_50_PERCENT.index;
                style.TopBorderColor    = HSSFColor.GREY_50_PERCENT.index;
                style.LeftBorderColor   = HSSFColor.GREY_50_PERCENT.index;
                style.RightBorderColor  = HSSFColor.GREY_50_PERCENT.index;

                style.FillForegroundColor = HSSFColor.WHITE.index;
                style.FillPattern         = FillPatternType.FINE_DOTS;
                style.FillBackgroundColor = HSSFColor.GREY_25_PERCENT.index;

                style.WrapText = true;

                style.SetFont(font);

                styles.Add(TextStyle.TableHeaderGreyCenterdBorder, style);
            }


            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 8;
                font.FontName           = "Arial";
                style.SetFont(font);

                styles.Add(TextStyle.SmallText, style);
            }

            {
                Font      font  = hssfworkbook.CreateFont();
                CellStyle style = hssfworkbook.CreateCellStyle();

                font.Color = HSSFColor.BLACK.index;
                font.FontHeightInPoints = 8;
                font.FontName           = "Arial";
                style.SetFont(font);

                style.Alignment         = HorizontalAlignment.LEFT;
                style.VerticalAlignment = VerticalAlignment.TOP;
                style.WrapText          = true;

                styles.Add(TextStyle.SmallTextWrap, style);
            }

            return(styles);
        }
Пример #16
0
    /// <summary>
    /// Create an Excel document with a nice breakdown of Trello.
    /// </summary>
    /// <returns></returns>
    public static byte[] ToExcel()
    {
        List <Board> _boards = ParseBoardData();

        // New workbook
        var workbook = new HSSFWorkbook();

        foreach (Board _boardInfo in _boards)
        {
            JavaScriptSerializer _worker = new JavaScriptSerializer();
            _worker.MaxJsonLength = int.MaxValue;
            File.WriteAllText(HttpContext.Current.Server.MapPath("") + @"\output\" + _boardInfo.name.ToString() + ".json", _worker.Serialize(_boardInfo));

            // Create a sheet
            var sheet = workbook.CreateSheet(_boardInfo.name.ToString());
            // Kill everything not supposed to be in there.
            var rowIndex = 0;

            #region HEADING STYLE

            HSSFFont _headingFont = (HSSFFont)workbook.CreateFont();
            _headingFont.Boldweight = (short)FontBoldWeight.Bold;
            _headingFont.Color      = HSSFColor.Grey80Percent.Index;
            //_headingFont.FontHeight = 280;
            HSSFCellStyle _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            _headingStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            _headingStyle.VerticalAlignment = VerticalAlignment.Center;
            _headingStyle.SetFont(_headingFont);

            #endregion

            // Create the heading
            var row = sheet.CreateRow(rowIndex);
            row.Height = 350;
            HSSFCell cell = (HSSFCell)row.CreateCell(0);
            cell.SetCellValue("Card name");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(1);
            cell.SetCellValue("Date created");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(2);
            cell.SetCellValue("Pending");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(3);
            cell.SetCellValue("In Progress");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(4);
            cell.SetCellValue("In Testing");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(5);
            cell.SetCellValue("Released");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(6);
            cell.SetCellValue("On Hold");
            cell.CellStyle = _headingStyle;
            cell           = (HSSFCell)row.CreateCell(7);
            cell.SetCellValue("Description");
            cell.CellStyle = _headingStyle;
            // Next row
            rowIndex++;

            #region ROW STYLE

            HSSFCellStyle _rowStyle = (HSSFCellStyle)workbook.CreateCellStyle();
            _rowStyle.VerticalAlignment = VerticalAlignment.Center;
            IDataFormat _dataFormat = workbook.CreateDataFormat();

            #endregion

            if (_boardInfo.Lists != null)
            {
                foreach (Board.List _listData in _boardInfo.Lists)
                {
                    if (_listData.Cards != null)
                    {
                        foreach (Board.List.Card _cardData in _listData.Cards)
                        {
                            // Add the row
                            row        = sheet.CreateRow(rowIndex);
                            row.Height = 350;

                            // Card name
                            cell = (HSSFCell)row.CreateCell(0);
                            cell.SetCellValue(_cardData.name.ToString());
                            cell.CellStyle = _rowStyle;

                            // Created
                            cell = (HSSFCell)row.CreateCell(1);
                            DateTime _startDate = new DateTime(1970, 01, 01);
                            cell.SetCellValue(_startDate.AddSeconds(int.Parse(_cardData.id.ToString().Substring(0, 8), System.Globalization.NumberStyles.HexNumber)).ToString("dd/MM/yyyy HH:mm:ss"));
                            cell.CellStyle = _rowStyle;

                            // Actions
                            foreach (Board.List.Card.Action _actionData in _cardData.Actions)
                            {
                                if (_actionData.data != null)
                                {
                                    if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("pending"))
                                    {
                                        cell = (HSSFCell)row.CreateCell(2);
                                        cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
                                        cell.CellStyle = _rowStyle;
                                    }
                                    else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("progress"))
                                    {
                                        cell = (HSSFCell)row.CreateCell(3);
                                        cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
                                        cell.CellStyle = _rowStyle;
                                    }
                                    else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("testing"))
                                    {
                                        cell = (HSSFCell)row.CreateCell(4);
                                        cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
                                        cell.CellStyle = _rowStyle;
                                    }
                                    else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("release"))
                                    {
                                        cell = (HSSFCell)row.CreateCell(5);
                                        cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
                                        cell.CellStyle = _rowStyle;
                                    }
                                    else if (_actionData.data.listAfter != null && _actionData.data.listAfter.name.ToString().ToLower().Contains("hold"))
                                    {
                                        cell = (HSSFCell)row.CreateCell(6);
                                        cell.SetCellValue(DateTime.Parse(_actionData.date.ToString()).ToString("dd/MM/yyyy HH:mm:ss"));
                                        cell.CellStyle = _rowStyle;
                                    }
                                }
                            }

                            // Description
                            cell = (HSSFCell)row.CreateCell(7);
                            cell.SetCellValue(_cardData.desc.ToString());
                            cell.CellStyle = _rowStyle;

                            // Next row.
                            rowIndex++;
                        }
                    }
                }

                // Size it
                sheet.AutoSizeColumn(0);
                sheet.AutoSizeColumn(1);
                sheet.AutoSizeColumn(2);
                sheet.AutoSizeColumn(3);
                sheet.AutoSizeColumn(4);
                sheet.AutoSizeColumn(5);
                sheet.AutoSizeColumn(6);
                sheet.AutoSizeColumn(7);

                sheet.CreateFreezePane(0, 1);
            }
        }

        #region OUTPUT

        // Save the Excel spreadsheet to a MemoryStream and return it to the client
        using (var exportData = new MemoryStream()) {
            workbook.Write(exportData);
            return(exportData.GetBuffer());
        }

        #endregion
    }
Пример #17
0
        /// <summary>
        /// 导出list数据到Excel
        /// </summary>
        /// <typeparam name="T">实体</typeparam>
        /// <param name="exportDatas">导出的list数据</param>
        /// <param name="templateModels">Excel模版信息</param>
        /// <param name="title">Sheet名称</param>
        /// <param name="multiHeaderInfos">多表头定义信息</param>
        /// <returns></returns>
        public static HSSFWorkbook Export <T>(List <T> exportDatas, List <ExcelTemplate> templateModels, string title, List <List <MultiHeaderInfo> > multiHeaderInfos = null)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            workbook.SetSheetName(0, title);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

            dsi.Company = Company;
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

            si.Author          = Author;
            si.ApplicationName = ApplicationName;
            si.Title           = title;

            si.CreateDateTime           = DateTime.Now;
            workbook.SummaryInformation = si;

            //取得列宽
            int[] arrColWidth = new int[templateModels.Count];
            int   columnIndex = 0;

            foreach (var templateModel in templateModels)
            {
                arrColWidth[columnIndex] = templateModel.CellLength > 0 ? templateModel.CellLength * 2 : Encoding.UTF8.GetBytes(templateModel.Name.ToString()).Length;
                columnIndex++;
            }

            int rowIndex = 0;

            foreach (var exportData in exportDatas)
            {
                #region 新建表,填充表头,填充列头,样式

                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)
                    {
                        sheet = workbook.CreateSheet();
                    }

                    if (multiHeaderInfos != null && multiHeaderInfos.Count > 0) // 复杂表头合并等
                    {
                        List <int>[] usedCellIndexs = new List <int> [multiHeaderInfos.Count];
                        for (var i = 0; i < multiHeaderInfos.Count; i++)
                        {
                            usedCellIndexs[i] = new List <int>();
                        }
                        for (var i = 0; i < multiHeaderInfos.Count; i++)
                        {
                            var colIndex  = 0;
                            var headerRow = sheet.CreateRow(i);
                            var headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment         = HorizontalAlignment.Center;
                            headStyle.VerticalAlignment = VerticalAlignment.Center;
                            var font = workbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.IsBold             = true;
                            headStyle.SetFont(font);
                            foreach (var multiHeaderInfo in multiHeaderInfos[i])
                            {
                                while (true) // 找未使用的第一个单元格
                                {
                                    if (!usedCellIndexs[i].Contains(colIndex))
                                    {
                                        break;
                                    }
                                    colIndex++;
                                }
                                headerRow.CreateCell(colIndex).SetCellValue(multiHeaderInfo.Name);
                                var oldColIndex = colIndex;
                                if (multiHeaderInfo.ColSpan > 1 || multiHeaderInfo.RowSpan > 1)
                                {
                                    sheet.AddMergedRegion(new CellRangeAddress(i, i + multiHeaderInfo.RowSpan - 1, colIndex, colIndex + multiHeaderInfo.ColSpan - 1));
                                    if (multiHeaderInfo.RowSpan > 1)
                                    {
                                        for (var j = 1; j < multiHeaderInfo.RowSpan; j++)
                                        {
                                            for (var k = colIndex; k < colIndex + multiHeaderInfo.ColSpan; k++)
                                            {
                                                usedCellIndexs[i + j].Add(k);
                                            }
                                        }
                                    }
                                    colIndex = colIndex + multiHeaderInfo.ColSpan;
                                }
                                else
                                {
                                    colIndex++;
                                }
                                headerRow.GetCell(oldColIndex).CellStyle = headStyle;
                            }
                        }
                        rowIndex = multiHeaderInfos.Count;
                    }
                    else
                    {
                        #region 列头及样式
                        {
                            var headerRow = sheet.CreateRow(0);
                            var headStyle = workbook.CreateCellStyle();
                            headStyle.Alignment = HorizontalAlignment.Center;
                            var font = workbook.CreateFont();
                            font.FontHeightInPoints = 10;
                            font.IsBold             = true;
                            headStyle.SetFont(font);
                            columnIndex = 0;
                            foreach (var templateModel in templateModels)
                            {
                                headerRow.CreateCell(columnIndex).SetCellValue(templateModel.Name);
                                headerRow.GetCell(columnIndex).CellStyle = headStyle;

                                //设置列宽
                                sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
                                columnIndex++;
                            }
                        }
                        #endregion

                        rowIndex = 1;
                    }
                }


                #endregion


                #region 填充内容
                var dataRow = sheet.CreateRow(rowIndex);
                columnIndex = 0;
                foreach (var templateModel in templateModels)
                {
                    var newCell = dataRow.CreateCell(columnIndex);

                    var objValue = typeof(T).GetProperty(templateModel.Field).GetValue(exportData)?.ToString();

                    switch (templateModel.FieldType)
                    {
                    case EFieldType.Int:
                        int intV = 0;
                        int.TryParse(objValue, out intV);
                        newCell.SetCellValue(intV);
                        break;

                    case EFieldType.Double:
                        double doubV = 0;
                        double.TryParse(objValue, out doubV);
                        newCell.SetCellValue(doubV);
                        break;

                    case EFieldType.Guid:
                        newCell.SetCellValue(objValue);
                        break;

                    case EFieldType.Bool:
                        bool boolV = false;
                        bool.TryParse(objValue, out boolV);
                        newCell.SetCellValue(boolV);
                        break;

                    case EFieldType.Date:
                        DateTime.TryParse(objValue, out var dateV);
                        if (dateV != DateTime.MinValue)
                        {
                            newCell.SetCellValue(dateV.ToString("yyyy-MM-dd"));
                        }
                        else
                        {
                            newCell.SetCellValue("");
                        }
                        break;

                    case EFieldType.DateTime:
                        DateTime.TryParse(objValue, out var datetimeV);
                        if (datetimeV != DateTime.MinValue)
                        {
                            newCell.SetCellValue(datetimeV.ToString("yyyy-MM-dd HH:mm:ss"));
                        }
                        else
                        {
                            newCell.SetCellValue("");
                        }
                        //newCell.SetCellType(CellType.String);
                        //newCell.SetCellValue(datetimeV.ToString("yyyy-MM-dd HH:mm:ss"));
                        break;

                    case EFieldType.String:
                        newCell.SetCellValue(objValue);
                        break;

                    default:
                        newCell.SetCellValue(objValue);
                        break;
                    }

                    columnIndex++;
                }
                #endregion

                rowIndex++;
            }

            for (int i = 0; i < templateModels.Count; i++)
            {
                sheet.AutoSizeColumn(i);
            }

            return(workbook);
        }
Пример #18
0
        /// <summary>
        /// 每日个签送钱客人情况表
        /// </summary>
        /// <param name="visaList"></param>
        /// <param name="visaInfoList"></param>
        /// <returns></returns>
        public static bool GetEverydayExcel(List <Model.Visa> visaList, List <List <VisaInfo> > visaInfoList)
        {
            //1.创建工作簿对象
            IWorkbook wkbook = new HSSFWorkbook();
            //2.创建工作表对象
            ISheet sheet = wkbook.CreateSheet("每日送签客人情况");

            //2.1创建表头
            IRow row = sheet.CreateRow(0);

            row.CreateCell(0).SetCellValue("");
            row.CreateCell(1).SetCellValue("姓名");
            row.CreateCell(2).SetCellValue("签发地");
            row.CreateCell(3).SetCellValue("居住地");
            row.CreateCell(4).SetCellValue("签证类型");
            row.CreateCell(5).SetCellValue("归国时间");
            row.CreateCell(6).SetCellValue("关系");
            row.CreateCell(7).SetCellValue("");

            //2.2设置列宽度
            sheet.SetColumnWidth(0, 5 * 256);
            sheet.SetColumnWidth(1, 10 * 256);
            sheet.SetColumnWidth(2, 10 * 256);
            sheet.SetColumnWidth(3, 10 * 256);
            sheet.SetColumnWidth(4, 10 * 256);
            sheet.SetColumnWidth(5, 13 * 256);
            sheet.SetColumnWidth(6, 10 * 256);
            sheet.SetColumnWidth(7, 35 * 256);

            //3.插入行和单元格
            int rowNum = 0;

            for (int i = 0; i != visaList.Count; ++i)
            {
                for (int j = 0; j < visaInfoList[i].Count; j++)
                {
                    ++rowNum;
                    row = sheet.CreateRow(rowNum);
                    row.CreateCell(0).SetCellValue(rowNum);
                    row.CreateCell(1).SetCellValue(visaInfoList[i][j].Name);
                    row.CreateCell(2).SetCellValue(visaInfoList[i][j].IssuePlace);
                    string residence = visaInfoList[i][j].Residence;
                    if (visaInfoList[i][j].Residence.Contains(" "))
                    {
                        residence = visaInfoList[i][j].Residence.Split(' ')[0];
                        if (residence.EndsWith("省") || residence.EndsWith("市"))
                        {
                            residence = residence.Substring(0, residence.Length - 1);
                        }
                    }

                    row.CreateCell(3).SetCellValue(residence);
                    row.CreateCell(4).SetCellValue(visaList[i].DepartureType);
                    row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(visaInfoList[i][j].ReturnTime)); //归国时间先不设置
                    row.CreateCell(6).SetCellValue(visaList[i].Remark);
                    row.CreateCell(7).SetCellValue(visaInfoList[i][j].Identification);
                }
                //创建单元格

                //设置行高
                //row.HeightInPoints = 50;
                //设置值
            }

            HSSFFont font = (HSSFFont)wkbook.CreateFont();

            font.FontName           = "宋体";
            font.FontHeightInPoints = 11;

            //4.1设置对齐风格和边框
            ICellStyle style = wkbook.CreateCellStyle();

            style.SetFont(font);
            style.BorderTop    = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft   = BorderStyle.Thin;
            style.BorderRight  = BorderStyle.Thin;
            for (int i = 0; i <= sheet.LastRowNum; i++)
            {
                row = sheet.GetRow(i);
                for (int c = 0; c < row.LastCellNum; ++c)
                {
                    row.GetCell(c).CellStyle = style;
                }
            }

            //4.2合并单元格
            int dp = 1;

            for (int i = 0; i != visaList.Count; ++i)
            {
                sheet.AddMergedRegion(new CellRangeAddress(dp, dp + visaInfoList[i].Count - 1, 6, 6));

                //单独处理合并区域的单元格格式
                ICellStyle mergeStyle = wkbook.CreateCellStyle();
                mergeStyle.SetFont(font);
                mergeStyle.VerticalAlignment        = VerticalAlignment.Center;
                mergeStyle.Alignment                = HorizontalAlignment.Center;
                mergeStyle.BorderTop                = BorderStyle.Thin;
                mergeStyle.BorderBottom             = BorderStyle.Thin;
                mergeStyle.BorderLeft               = BorderStyle.Thin;
                mergeStyle.BorderRight              = BorderStyle.Thin;
                sheet.GetRow(dp).Cells[6].CellStyle = mergeStyle;
                dp += visaInfoList[i].Count;
            }


            //5.执行写入磁盘
            string dstName = GlobalUtils.OpenSaveFileDlg("每日送签客人情况表.xls", "office 2003 excel|*.xls");

            return(SaveFile(dstName, wkbook));
        }
Пример #19
0
        /// <summary>
        /// 生成导入模版Excel信息
        /// </summary>
        /// <param name="templateModels">模板定义信息</param>
        /// <param name="title">Sheet名称</param>
        /// <returns></returns>
        public static HSSFWorkbook ExportTemplate(List <ExcelTemplate> templateModels, string title)
        {
            HSSFWorkbook workbook = new HSSFWorkbook();
            ISheet       sheet    = workbook.CreateSheet();

            workbook.SetSheetName(0, title);

            DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();

            dsi.Company = Company;
            workbook.DocumentSummaryInformation = dsi;

            SummaryInformation si = PropertySetFactory.CreateSummaryInformation();

            si.Author          = Author;
            si.ApplicationName = ApplicationName;
            si.Title           = title;

            si.CreateDateTime           = DateTime.Now;
            workbook.SummaryInformation = si;

            //取得列宽
            int[] arrColWidth = new int[templateModels.Count];
            int   columnIndex = 0;

            foreach (var templateModel in templateModels)
            {
                arrColWidth[columnIndex] = templateModel.CellLength > 0 ? templateModel.CellLength * 2 : Encoding.UTF8.GetBytes(templateModel.Name.ToString()).Length;
                columnIndex++;
            }

            var headerRow = sheet.CreateRow(0);

            columnIndex = 0;
            foreach (var templateModel in templateModels)
            {
                var cell = headerRow.CreateCell(columnIndex);
                if (!string.IsNullOrEmpty(templateModel.ExportComments))
                {
                    HSSFPatriarch patr    = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    HSSFComment   comment = (HSSFComment)patr.CreateCellComment(new HSSFClientAnchor(0, 0, 0, 0, 1, 2, 4, 16));
                    comment.String   = new HSSFRichTextString(templateModel.ExportComments);
                    comment.Author   = ApplicationName;
                    cell.CellComment = comment;
                }

                if (templateModel.DictionaryItems != null && templateModel.DictionaryItems.Count > 0)
                {
                    DVConstraint         constraint = DVConstraint.CreateExplicitListConstraint(templateModel.DictionaryItems.ToArray());
                    CellRangeAddressList regions    = new CellRangeAddressList(1, 65535, columnIndex, columnIndex);
                    IDataValidation      validation = new HSSFDataValidation(regions, constraint);
                    sheet.AddValidationData(validation);
                }

                cell.SetCellValue(templateModel.Name);
                if (templateModel.IsRequred)
                {
                    var headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    var font = workbook.CreateFont();
                    font.Color = HSSFColor.Red.Index;
                    font.FontHeightInPoints = 10;
                    font.IsBold             = true;
                    headStyle.SetFont(font);
                    cell.CellStyle = headStyle;
                }
                else
                {
                    var headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.Center;
                    var font = workbook.CreateFont();
                    font.Color = HSSFColor.Black.Index;
                    font.FontHeightInPoints = 10;
                    font.IsBold             = true;
                    headStyle.SetFont(font);
                    cell.CellStyle = headStyle;
                }

                //设置列宽
                sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
                columnIndex++;
            }

            return(workbook);
        }
Пример #20
0
        /// <summary>
        /// 导出数据到excel文件
        /// </summary>
        /// <param name="titleList">特殊处理的数据的标题集合</param>
        /// <returns></returns>
        public Stream CommonToExcel(List <string> titleList)
        {
            int rowIndex = 0;

            //创建workbook
            HSSFWorkbook workbook = new HSSFWorkbook();
            MemoryStream ms       = new MemoryStream();
            ISheet       sheet    = workbook.CreateSheet("sheet1");
            IRow         row      = sheet.CreateRow(rowIndex);

            row.Height = 200 * 3;

            //表头样式
            ICellStyle style = workbook.CreateCellStyle();

            style.Alignment = HorizontalAlignment.Left;//居中对齐
            //表头单元格背景色
            style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
            style.FillPattern         = FillPattern.SolidForeground;
            //表头单元格边框
            style.BorderTop    = BorderStyle.Thin;
            style.BorderRight  = BorderStyle.Thin;
            style.BorderBottom = BorderStyle.Thin;
            style.BorderLeft   = BorderStyle.Thin;

            style.TopBorderColor    = HSSFColor.Black.Index;
            style.RightBorderColor  = HSSFColor.Black.Index;
            style.BottomBorderColor = HSSFColor.Black.Index;
            style.LeftBorderColor   = HSSFColor.Black.Index;
            style.VerticalAlignment = VerticalAlignment.Center;
            //表头字体设置
            IFont font = workbook.CreateFont();

            font.FontHeightInPoints = 12;  //字号
            font.Boldweight         = 600; //加粗
            //font.Color = HSSFColor.WHITE.index;//颜色
            style.SetFont(font);

            //数据样式
            ICellStyle datastyle = workbook.CreateCellStyle();

            datastyle.Alignment = HorizontalAlignment.Left;//左对齐
            //数据单元格的边框
            datastyle.BorderTop    = BorderStyle.Thin;
            datastyle.BorderRight  = BorderStyle.Thin;
            datastyle.BorderBottom = BorderStyle.Thin;
            datastyle.BorderLeft   = BorderStyle.Thin;

            datastyle.TopBorderColor    = HSSFColor.Black.Index;
            datastyle.RightBorderColor  = HSSFColor.Black.Index;
            datastyle.BottomBorderColor = HSSFColor.Black.Index;
            datastyle.LeftBorderColor   = HSSFColor.Black.Index;
            //数据的字体
            IFont datafont = workbook.CreateFont();

            datafont.FontHeightInPoints = 11;//字号
            datastyle.SetFont(datafont);
            //设置列宽
            for (int i = 0; i < columsWidth.Length; i++)
            {
                //sheet.SetColumnWidth(i, columsWidth[i] * 256);
                sheet.SetColumnWidth(i, columsWidth[i] * 58);
            }
            sheet.DisplayGridlines = false;

            try {
                //表头数据
                for (int i = 0; i < titles.Length; i++)
                {
                    ICell cell = row.CreateCell(i);
                    cell.SetCellValue(titles[i]);
                    cell.CellStyle = style;
                }

                for (int k = 0; k < data.Count; k++)
                {
                    row        = sheet.CreateRow(k + 1);
                    row.Height = 200 * 2;
                    T t = data[k];
                    // 获得此模型的公共属性
                    PropertyInfo[] propertys = t.GetType().GetProperties();
                    for (int j = 0; j < colums.Length; j++)
                    {
                        var   temp = propertys.ToList().Where(p => p.Name == colums[j]).Single();
                        ICell cell = row.CreateCell(j);

                        var value = temp.GetValue(t, null);

                        if (value == null)
                        {
                            value = "";
                        }
                        else
                        {
                            if (value.GetType() == Type.GetType("System.DateTime"))
                            {
                                if (titleList != null && titleList.Contains(colums[j]))
                                {
                                    value = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd HH:mm");
                                }
                                else
                                {
                                    value = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd");
                                }
                            }
                        }
                        cell.SetCellValue(value.ToString());
                        cell.CellStyle = datastyle;
                    }
                }
            } catch (Exception ex) {
            } finally {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                workbook    = null;
                sheet       = null;
                row         = null;
            }



            return(ms);
        }
Пример #21
0
 // Token: 0x06000042 RID: 66 RVA: 0x000064B8 File Offset: 0x000046B8
 protected override void View()
 {
     this.examinfo = DbHelper.ExecuteModel <ExamInfo>(this.examid);
     if (this.examinfo.id == 0)
     {
         this.ShowErr("对不起,该试卷不存在或已被删除。");
     }
     else
     {
         this.sortid   = this.examinfo.sortid;
         this.sortinfo = SortBll.GetSortInfo(this.sortid);
         if (this.ispost)
         {
             if (this.action == "delete")
             {
                 string @string = FPRequest.GetString("chkid");
                 if (DbHelper.ExecuteDelete <ExamResult>(@string) > 0)
                 {
                     SqlParam sqlParam = DbHelper.MakeAndWhere("resultid", WhereType.In, @string);
                     DbHelper.ExecuteDelete <ExamResultTopic>(new SqlParam[]
                     {
                         sqlParam
                     });
                 }
             }
         }
         if (this.examinfo.examdeparts == "" && this.examinfo.examuser == "" && this.examinfo.examroles == "")
         {
             List <SqlParam> list = new List <SqlParam>();
             list.Add(DbHelper.MakeAndWhere("examid", this.examid));
             if (this.keyword != "")
             {
                 string          text      = "0";
                 SqlParam        sqlParam2 = DbHelper.MakeAndWhere(string.Format("([username] LIKE '%{0}%' OR [realname] LIKE '%{0}%')", this.keyword), WhereType.Custom, "");
                 List <UserInfo> list2     = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
                 {
                     sqlParam2
                 });
                 foreach (UserInfo userInfo in list2)
                 {
                     if (text != "")
                     {
                         text += ",";
                     }
                     text += userInfo.id;
                 }
                 list.Add(DbHelper.MakeAndWhere("uid", WhereType.In, text));
             }
             if (this.action == "export")
             {
                 OrderByParam[] orderbys = new OrderByParam[]
                 {
                     DbHelper.MakeOrderBy("score", OrderBy.DESC),
                     DbHelper.MakeOrderBy("id", OrderBy.ASC)
                 };
                 this.examresultlist = DbHelper.ExecuteList <ExamResult>(orderbys, list.ToArray());
             }
             else
             {
                 this.examresultlist = DbHelper.ExecuteList <ExamResult>(this.pager, list.ToArray());
             }
         }
         else
         {
             string text = "";
             if (this.examinfo.examroles != "")
             {
                 SqlParam        sqlParam2 = DbHelper.MakeAndWhere("roleid", WhereType.In, this.examinfo.examroles);
                 List <UserInfo> list2     = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
                 {
                     sqlParam2
                 });
                 foreach (UserInfo userInfo in list2)
                 {
                     if (!FPUtils.InArray(userInfo.id, text))
                     {
                         ExamResult examResult = new ExamResult();
                         examResult.uid    = userInfo.id;
                         examResult.examid = this.examid;
                         examResult.status = -1;
                         this.examresultlist.Add(examResult);
                         if (text != "")
                         {
                             text += ",";
                         }
                         text += userInfo.id;
                     }
                 }
             }
             if (this.examinfo.examdeparts != "")
             {
                 SqlParam        sqlParam2 = DbHelper.MakeAndWhere("departid", WhereType.In, this.examinfo.examdeparts);
                 List <UserInfo> list2     = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
                 {
                     sqlParam2
                 });
                 foreach (UserInfo userInfo in list2)
                 {
                     if (!FPUtils.InArray(userInfo.id, text))
                     {
                         ExamResult examResult = new ExamResult();
                         examResult.uid    = userInfo.id;
                         examResult.examid = this.examid;
                         examResult.status = -1;
                         this.examresultlist.Add(examResult);
                         if (text != "")
                         {
                             text += ",";
                         }
                         text += userInfo.id;
                     }
                 }
             }
             if (this.examinfo.examuser != "")
             {
                 SqlParam        sqlParam2 = DbHelper.MakeAndWhere("id", WhereType.In, this.examinfo.examuser);
                 List <UserInfo> list2     = DbHelper.ExecuteList <UserInfo>(new SqlParam[]
                 {
                     sqlParam2
                 });
                 foreach (UserInfo userInfo in list2)
                 {
                     if (!FPUtils.InArray(userInfo.id, text))
                     {
                         ExamResult examResult = new ExamResult();
                         examResult.uid    = userInfo.id;
                         examResult.examid = this.examid;
                         examResult.status = -1;
                         this.examresultlist.Add(examResult);
                         if (text != "")
                         {
                             text += ",";
                         }
                         text += userInfo.id;
                     }
                 }
             }
             SqlParam          sqlParam3 = DbHelper.MakeAndWhere("examid", this.examid);
             OrderByParam      orderby   = DbHelper.MakeOrderBy("id", OrderBy.ASC);
             List <ExamResult> list3     = DbHelper.ExecuteList <ExamResult>(orderby, new SqlParam[]
             {
                 sqlParam3
             });
             int num = 0;
             foreach (ExamResult examResult2 in this.examresultlist)
             {
                 foreach (ExamResult examResult3 in list3)
                 {
                     if (examResult3.uid == examResult2.uid)
                     {
                         this.examresultlist[num].id           = examResult3.id;
                         this.examresultlist[num].score        = examResult3.score;
                         this.examresultlist[num].starttime    = examResult3.starttime;
                         this.examresultlist[num].examdatetime = examResult3.examdatetime;
                         this.examresultlist[num].utime        = examResult3.utime;
                         this.examresultlist[num].status       = examResult3.status;
                         this.examresultlist[num].questions++;
                         this.examresultlist[num].ip = examResult3.ip;
                     }
                 }
                 num++;
             }
             if (this.keyword != "")
             {
                 list3 = new List <ExamResult>();
                 foreach (ExamResult examResult2 in this.examresultlist)
                 {
                     if (examResult2.IUser.username.Contains(this.keyword) || examResult2.IUser.realname.Contains(this.keyword))
                     {
                         list3.Add(examResult2);
                     }
                 }
                 this.examresultlist = new List <ExamResult>();
                 foreach (ExamResult examResult2 in list3)
                 {
                     this.examresultlist.Add(examResult2);
                 }
             }
             if (this.action != "export" && this.action != "report")
             {
                 this.pager.total = this.examresultlist.Count;
                 int num2  = (this.pager.pageindex - 1) * this.pager.pagesize;
                 int count = this.pager.pagesize;
                 if (num2 + this.pager.pagesize > this.pager.total)
                 {
                     count = this.pager.total - num2;
                 }
                 this.examresultlist = this.examresultlist.GetRange(num2, count);
             }
         }
         if (this.ispost)
         {
             if (this.action == "export")
             {
                 HSSFWorkbook  hssfworkbook  = new HSSFWorkbook();
                 HSSFSheet     hssfsheet     = hssfworkbook.CreateSheet("Sheet1");
                 HSSFCellStyle hssfcellStyle = hssfworkbook.CreateCellStyle();
                 hssfcellStyle.Alignment         = CellHorizontalAlignment.CENTER;
                 hssfcellStyle.VerticalAlignment = CellVerticalAlignment.CENTER;
                 hssfcellStyle.BorderTop         = CellBorderType.THIN;
                 hssfcellStyle.BorderRight       = CellBorderType.THIN;
                 hssfcellStyle.BorderLeft        = CellBorderType.THIN;
                 hssfcellStyle.BorderBottom      = CellBorderType.THIN;
                 hssfcellStyle.DataFormat        = 0;
                 HSSFFont hssffont = hssfworkbook.CreateFont();
                 hssffont.Boldweight = short.MaxValue;
                 hssfcellStyle.SetFont(hssffont);
                 HSSFRow hssfrow = hssfsheet.CreateRow(0);
                 hssfrow.CreateCell(0).SetCellValue("用户名");
                 hssfrow.CreateCell(1).SetCellValue("姓名");
                 hssfrow.CreateCell(2).SetCellValue("所在部门");
                 hssfrow.CreateCell(3).SetCellValue("考试得分");
                 hssfrow.CreateCell(4).SetCellValue("开始时间");
                 hssfrow.CreateCell(5).SetCellValue("考试用时");
                 hssfrow.CreateCell(6).SetCellValue("考试状态");
                 hssfrow.CreateCell(7).SetCellValue("");
                 hssfrow.Height = 400;
                 hssfsheet.SetColumnWidth(2, 6000);
                 hssfsheet.SetColumnWidth(4, 6000);
                 for (int i = 0; i < 7; i++)
                 {
                     hssfrow.Cells[i].CellStyle = hssfcellStyle;
                 }
                 HSSFCellStyle hssfcellStyle2 = hssfworkbook.CreateCellStyle();
                 hssfcellStyle2.Alignment         = CellHorizontalAlignment.CENTER;
                 hssfcellStyle2.VerticalAlignment = CellVerticalAlignment.CENTER;
                 hssfcellStyle2.BorderTop         = CellBorderType.THIN;
                 hssfcellStyle2.BorderRight       = CellBorderType.THIN;
                 hssfcellStyle2.BorderLeft        = CellBorderType.THIN;
                 hssfcellStyle2.BorderBottom      = CellBorderType.THIN;
                 hssfcellStyle2.DataFormat        = 0;
                 int num3 = 1;
                 foreach (ExamResult examResult2 in this.examresultlist)
                 {
                     HSSFRow hssfrow2 = hssfsheet.CreateRow(num3);
                     hssfrow2.Height = 300;
                     hssfrow2.CreateCell(0).SetCellValue(examResult2.IUser.username);
                     hssfrow2.CreateCell(1).SetCellValue(examResult2.IUser.realname);
                     hssfrow2.CreateCell(2).SetCellValue(examResult2.IUser.Department.name);
                     hssfrow2.CreateCell(3).SetCellValue(examResult2.score.ToString());
                     if (examResult2.status >= 0)
                     {
                         hssfrow2.CreateCell(4).SetCellValue(examResult2.examdatetime.ToString("yyyy-MM-dd HH:mm:dd"));
                         hssfrow2.CreateCell(5).SetCellValue((examResult2.utime / 60 + 1).ToString() + "分钟");
                     }
                     else
                     {
                         hssfrow2.CreateCell(4).SetCellValue("");
                         hssfrow2.CreateCell(5).SetCellValue("");
                     }
                     if (examResult2.status == 1)
                     {
                         hssfrow2.CreateCell(6).SetCellValue("已交卷");
                     }
                     else if (examResult2.status == 2)
                     {
                         hssfrow2.CreateCell(6).SetCellValue("已阅卷");
                     }
                     else if (examResult2.status == 0)
                     {
                         hssfrow2.CreateCell(6).SetCellValue("未交卷");
                     }
                     else
                     {
                         hssfrow2.CreateCell(6).SetCellValue("缺考");
                     }
                     hssfrow2.CreateCell(7).SetCellValue("");
                     for (int i = 0; i < 7; i++)
                     {
                         hssfrow2.Cells[i].CellStyle = hssfcellStyle2;
                     }
                     num3++;
                 }
                 using (MemoryStream memoryStream = new MemoryStream())
                 {
                     hssfworkbook.Write(memoryStream);
                     memoryStream.Flush();
                     memoryStream.Position = 0L;
                     hssfsheet.Dispose();
                     hssfworkbook.Dispose();
                     base.Response.ContentType     = "application/vnd.ms-excel";
                     base.Response.ContentEncoding = Encoding.UTF8;
                     base.Response.Charset         = "";
                     base.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(this.examinfo.name + "成绩表.xls"));
                     base.Response.BinaryWrite(memoryStream.GetBuffer());
                     base.Response.Flush();
                     base.Response.End();
                 }
             }
             else if (this.action == "report")
             {
                 AsposeWordApp asposeWordApp = new AsposeWordApp();
                 asposeWordApp.Open(FPUtils.GetMapPath("images\\examreport.doc"));
                 asposeWordApp.InsertText("examtitle", this.examinfo.name);
                 asposeWordApp.InsertText("username", this.user.realname);
                 asposeWordApp.InsertText("total", this.examinfo.total.ToString() + "分");
                 if (this.examinfo.islimit == 1)
                 {
                     asposeWordApp.InsertText("examtime", this.examinfo.starttime.ToString("yyyy-MM-dd HH:mm"));
                 }
                 else
                 {
                     asposeWordApp.InsertText("examtime", "不限制");
                 }
                 asposeWordApp.InsertText("exampass", (this.examinfo.passmark * this.examinfo.total / 100.0).ToString() + "分");
                 asposeWordApp.InsertText("qtime", this.examinfo.examtime.ToString() + "分钟");
                 asposeWordApp.InsertText("examuser", this.examinfo.exams.ToString() + "人");
                 if (this.examinfo.exams > 0)
                 {
                     asposeWordApp.InsertText("examavg", (this.examinfo.score / (double)this.examinfo.exams).ToString("0.0"));
                 }
                 else
                 {
                     asposeWordApp.InsertText("examavg", "0");
                 }
                 int[] array = new int[5];
                 foreach (ExamResult examResult2 in this.examresultlist)
                 {
                     if (examResult2.score < 60.0)
                     {
                         array[0]++;
                     }
                     else if (examResult2.score >= 60.0 && examResult2.score < 70.0)
                     {
                         array[1]++;
                     }
                     else if (examResult2.score >= 70.0 && examResult2.score < 80.0)
                     {
                         array[2]++;
                     }
                     else if (examResult2.score >= 80.0 && examResult2.score < 90.0)
                     {
                         array[3]++;
                     }
                     else if (examResult2.score >= 90.0)
                     {
                         array[4]++;
                     }
                 }
                 int i = 1;
                 foreach (int num4 in array)
                 {
                     asposeWordApp.InsertText("s" + i, num4.ToString() + "人");
                     asposeWordApp.InsertText("p" + i, (num4 / this.examinfo.exams * 100).ToString("0.0") + "%");
                     i++;
                 }
                 asposeWordApp.Save(base.Response, this.examinfo.name + "_考试分析报告.doc");
             }
         }
         base.SaveRightURL();
     }
 }
Пример #22
0
        public void TestOptimiseStyles()
        {
            HSSFWorkbook wb = new HSSFWorkbook();

            // Two fonts
            Assert.AreEqual(4, wb.NumberOfFonts);

            IFont f1 = wb.CreateFont();

            f1.FontHeight = ((short)11);
            f1.FontName   = ("Testing");

            IFont f2 = wb.CreateFont();

            f2.FontHeight = ((short)22);
            f2.FontName   = ("Also Testing");

            Assert.AreEqual(6, wb.NumberOfFonts);


            // Several styles
            Assert.AreEqual(21, wb.NumCellStyles);

            Npoi.Core.SS.UserModel.ICellStyle cs1 = wb.CreateCellStyle();
            cs1.SetFont(f1);

            Npoi.Core.SS.UserModel.ICellStyle cs2 = wb.CreateCellStyle();
            cs2.SetFont(f2);

            Npoi.Core.SS.UserModel.ICellStyle cs3 = wb.CreateCellStyle();
            cs3.SetFont(f1);

            Npoi.Core.SS.UserModel.ICellStyle cs4 = wb.CreateCellStyle();
            cs4.SetFont(f1);
            cs4.Alignment = HorizontalAlignment.CenterSelection;// ((short)22);

            Npoi.Core.SS.UserModel.ICellStyle cs5 = wb.CreateCellStyle();
            cs5.SetFont(f2);
            cs5.Alignment = HorizontalAlignment.Fill; //((short)111);

            Npoi.Core.SS.UserModel.ICellStyle cs6 = wb.CreateCellStyle();
            cs6.SetFont(f2);

            Assert.AreEqual(27, wb.NumCellStyles);


            // Use them
            Npoi.Core.SS.UserModel.ISheet s = wb.CreateSheet();
            IRow r = s.CreateRow(0);

            r.CreateCell(0).CellStyle = (cs1);
            r.CreateCell(1).CellStyle = (cs2);
            r.CreateCell(2).CellStyle = (cs3);
            r.CreateCell(3).CellStyle = (cs4);
            r.CreateCell(4).CellStyle = (cs5);
            r.CreateCell(5).CellStyle = (cs6);
            r.CreateCell(6).CellStyle = (cs1);
            r.CreateCell(7).CellStyle = (cs2);

            Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
            Assert.AreEqual(26, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
            Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);


            // Optimise
            HSSFOptimiser.OptimiseCellStyles(wb);


            // Check
            Assert.AreEqual(6, wb.NumberOfFonts);
            Assert.AreEqual(25, wb.NumCellStyles);

            // cs1 -> 21
            Assert.AreEqual(21, ((HSSFCell)r.GetCell(0)).CellValueRecord.XFIndex);
            // cs2 -> 22
            Assert.AreEqual(22, ((HSSFCell)r.GetCell(1)).CellValueRecord.XFIndex);
            Assert.AreEqual(22, r.GetCell(1).CellStyle.GetFont(wb).FontHeight);
            // cs3 = cs1 -> 21
            Assert.AreEqual(21, ((HSSFCell)r.GetCell(2)).CellValueRecord.XFIndex);
            // cs4 --> 24 -> 23
            Assert.AreEqual(23, ((HSSFCell)r.GetCell(3)).CellValueRecord.XFIndex);
            // cs5 --> 25 -> 24
            Assert.AreEqual(24, ((HSSFCell)r.GetCell(4)).CellValueRecord.XFIndex);
            // cs6 = cs2 -> 22
            Assert.AreEqual(22, ((HSSFCell)r.GetCell(5)).CellValueRecord.XFIndex);
            // cs1 -> 21
            Assert.AreEqual(21, ((HSSFCell)r.GetCell(6)).CellValueRecord.XFIndex);
            // cs2 -> 22
            Assert.AreEqual(22, ((HSSFCell)r.GetCell(7)).CellValueRecord.XFIndex);


            // Add a new duplicate, and two that aren't used
            HSSFCellStyle csD = (HSSFCellStyle)wb.CreateCellStyle();

            csD.SetFont(f1);
            r.CreateCell(8).CellStyle = (csD);

            HSSFFont f3 = (HSSFFont)wb.CreateFont();

            f3.FontHeight = ((short)23);
            f3.FontName   = ("Testing 3");
            HSSFFont f4 = (HSSFFont)wb.CreateFont();

            f4.FontHeight = ((short)24);
            f4.FontName   = ("Testing 4");

            HSSFCellStyle csU1 = (HSSFCellStyle)wb.CreateCellStyle();

            csU1.SetFont(f3);
            HSSFCellStyle csU2 = (HSSFCellStyle)wb.CreateCellStyle();

            csU2.SetFont(f4);

            // Check before the optimise
            Assert.AreEqual(8, wb.NumberOfFonts);
            Assert.AreEqual(28, wb.NumCellStyles);

            // Optimise, should remove the two un-used ones and the one duplicate
            HSSFOptimiser.OptimiseCellStyles(wb);

            // Check
            Assert.AreEqual(8, wb.NumberOfFonts);
            Assert.AreEqual(25, wb.NumCellStyles);

            // csD -> cs1 -> 21
            Assert.AreEqual(21, ((HSSFCell)r.GetCell(8)).CellValueRecord.XFIndex);
        }
Пример #23
0
        static void Main(string[] args)
        {
            InitializeWorkbook();

            ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");

            //font style1: underlined, italic, red color, fontsize=20
            IFont font1 = hssfworkbook.CreateFont();

            font1.Color              = HSSFColor.Red.Index;
            font1.IsItalic           = true;
            font1.Underline          = FontUnderlineType.Double;
            font1.FontHeightInPoints = 20;

            //bind font with style 1
            ICellStyle style1 = hssfworkbook.CreateCellStyle();

            style1.SetFont(font1);

            //font style2: strikeout line, green color, fontsize=15, fontname='宋体'
            IFont font2 = hssfworkbook.CreateFont();

            font2.Color              = HSSFColor.OliveGreen.Index;
            font2.IsStrikeout        = true;
            font2.FontHeightInPoints = 15;
            font2.FontName           = "宋体";

            //bind font with style 2
            ICellStyle style2 = hssfworkbook.CreateCellStyle();

            style2.SetFont(font2);

            //apply font styles
            ICell cell1 = CellUtil.CreateCell(sheet1.CreateRow(1), 1, "Hello World!");

            cell1.CellStyle = style1;
            ICell cell2 = CellUtil.CreateCell(sheet1.CreateRow(3), 1, "早上好!");

            cell2.CellStyle = style2;

            //cell with rich text
            ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
            HSSFRichTextString richtext = new HSSFRichTextString("Microsoft OfficeTM");

            //apply font to "Microsoft Office"
            IFont font4 = hssfworkbook.CreateFont();

            font4.FontHeightInPoints = 12;
            richtext.ApplyFont(0, 16, font4);
            //apply font to "TM"
            IFont font3 = hssfworkbook.CreateFont();

            font3.TypeOffset         = FontSuperScript.Super;
            font3.IsItalic           = true;
            font3.Color              = HSSFColor.Blue.Index;
            font3.FontHeightInPoints = 8;
            richtext.ApplyFont(16, 18, font3);

            cell3.SetCellValue(richtext);

            WriteToFile();
        }
Пример #24
0
        /// <summary>
        /// 生成Excel
        /// </summary>
        /// <returns>生成的Excel文件</returns>
        public virtual byte[] GenerateExcel()
        {
            NeedPage = false;
            if (GridHeaders == null)
            {
                GetHeaders();
            }
            if (IsSearched == false)
            {
                DoSearch();
            }
            HSSFWorkbook     workbook = new HSSFWorkbook();
            List <HSSFSheet> sheets   = new List <NPOI.HSSF.UserModel.HSSFSheet>();
            //准备好sheet,每6万行生成一个sheet
            var sheetno     = ((EntityList.Count - 1) / 60000) + 1;
            var headerStyle = workbook.CreateCellStyle();

            //设定单元格边框
            headerStyle.BorderBottom = BorderStyle.Thin;
            headerStyle.BorderLeft   = BorderStyle.Thin;
            headerStyle.BorderRight  = BorderStyle.Thin;
            headerStyle.BorderTop    = BorderStyle.Thin;
            //用灰色填充背景
            var headerbg = HSSFColor.Grey25Percent.Index;

            headerStyle.FillForegroundColor = headerbg;
            headerStyle.FillPattern         = FillPattern.SolidForeground;
            headerStyle.FillBackgroundColor = headerbg;
            //去掉 Id 列和动作列
            RemoveActionAndIdColumn();
            //循环生成所有sheet,并为每个sheet添加表头
            var headerrows = 0;

            for (int i = 1; i <= sheetno; i++)
            {
                HSSFSheet sheet = workbook.CreateSheet("Sheet" + i) as HSSFSheet;
                //生成表头
                headerrows = MakeExcelHeader(sheet, GridHeaders, 0, 0, headerStyle);
                sheets.Add(sheet);
            }

            var rowIndex = headerrows;
            var colIndex = 0;

            //Excel中用到的style,每种前景色和背景色的组合为一个style。Nopi值支持4000个style,所以同样的style要重复使用
            Dictionary <string, ICellStyle> styles = new Dictionary <string, ICellStyle>();
            //Excel中用到的font,主要是为了更改字体颜色
            Dictionary <string, IFont> fonts = new Dictionary <string, IFont>();

            //循环数据
            foreach (var row in EntityList)
            {
                var sheetindex = ((rowIndex - headerrows) / 60000);
                colIndex = 0;
                string bgColor = "";
                string fColor  = "";
                //获取设定的行背景色
                bgColor = SetFullRowBgColor(row);
                //获取设定的行前景色
                fColor = SetFullRowColor(row);
                var dr = sheets[sheetindex].CreateRow(rowIndex - sheetindex * 60000) as HSSFRow;
                foreach (var baseCol in GridHeaders)
                {
                    //处理枚举变量的多语言
                    bool IsEmunBoolParp = false;
                    var  proType        = baseCol.FieldType;
                    if (proType.IsEnumOrNullableEnum())
                    {
                        IsEmunBoolParp = true;
                    }

                    foreach (var col in baseCol.BottomChildren)
                    {
                        //获取数据,并过滤特殊字符
                        string text = Regex.Replace(col.GetText(row).ToString(), @"<[^>]*>", String.Empty);

                        //处理枚举变量的多语言

                        if (IsEmunBoolParp)
                        {
                            if (int.TryParse(text, out int enumvalue))
                            {
                                text = PropertyHelper.GetEnumDisplayName(proType, enumvalue);
                            }
                        }


                        //建立excel单元格
                        var        cell     = dr.CreateCell(colIndex);
                        ICellStyle style    = null;
                        IFont      font     = null;
                        var        styleKey = string.Empty;
                        //获取设定的单元格背景色
                        string backColor = col.GetBackGroundColor(row);
                        //获取设定的单元格前景色
                        string foreColor = col.GetForeGroundColor(row);
                        //如果行背景色或单元格背景色有值,则用颜色的ARGB的值作为style的key
                        if (bgColor != "" || backColor != "")
                        {
                            styleKey = backColor == "" ? bgColor : backColor;
                        }
                        //如果行前景色或单元格前景色有值,则用颜色的ARGB加上背景色的ARGB作为style的key
                        if (fColor != "" || foreColor != "")
                        {
                            styleKey += foreColor == "" ? foreColor : fColor;
                        }
                        //如果已经有符合条件的style,则使用
                        if (styles.ContainsKey(styleKey))
                        {
                            style = styles[styleKey];
                        }
                        //如果没有,则新建一个style
                        else
                        {
                            var newKey     = "";
                            var newFontKey = "";
                            //新建style
                            style = workbook.CreateCellStyle();
                            //设定单元格边框
                            style.BorderBottom = BorderStyle.Thin;
                            style.BorderLeft   = BorderStyle.Thin;
                            style.BorderRight  = BorderStyle.Thin;
                            style.BorderTop    = BorderStyle.Thin;
                            //如果行前景色或单元格前景色有值,则设定单元格的填充颜色
                            if (bgColor != "" || backColor != "")
                            {
                                newKey = backColor == "" ? bgColor : backColor;
                                var ci = Utils.GetExcelColor(backColor == "" ? bgColor : backColor);
                                style.FillForegroundColor = ci;
                                style.FillPattern         = FillPattern.SolidForeground;
                                style.FillBackgroundColor = ci;
                            }
                            //如果行前景色或单元格前景色有值,则设定单元格的字体的颜色
                            if (fColor != "" || foreColor != "")
                            {
                                newFontKey = foreColor == "" ? foreColor : fColor;
                                newKey    += foreColor == "" ? foreColor : fColor;
                                //如果已经有符合条件的字体,则使用
                                if (fonts.ContainsKey(newFontKey))
                                {
                                    font = fonts[newFontKey];
                                }
                                //如果没有,则新建
                                else
                                {
                                    //新建字体
                                    font = workbook.CreateFont();
                                    //设定字体颜色
                                    font.Color = Utils.GetExcelColor(foreColor == "" ? fColor : foreColor);
                                    //向集合中添加新字体
                                    fonts.Add(newFontKey, font);
                                }
                                //设定style中的字体
                                style.SetFont(font);
                            }
                            //将新建的style添加到集合中
                            styles.Add(newKey, style);
                        }
                        cell.CellStyle = style;
                        cell.SetCellValue(text);
                        colIndex++;
                    }
                }
                rowIndex++;
            }

            //获取Excel文件的二进制数据
            byte[] rv = new byte[] { };
            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                rv          = ms.ToArray();
            }
            return(rv);
        }
Пример #25
0
        /// <summary>
        /// 大数据量多个sheet导出
        /// </summary>
        /// <typeparam name="T">数据源实体类</typeparam>
        /// <param name="objList">数据源</param>
        /// <param name="fileName">文件名称</param>
        /// <param name="btyBytes">导出数据流</param>
        /// <param name="columnInfo">显示列对应数据字典</param>
        /// <param name="listCount">每个sheet包含数据条数</param>
        /// <returns></returns>
        public void ExportExcelTest <T>(List <T> objList, string fileName, Dictionary <string, string> columnInfo = null)
        {
            //在内存中生成一个Excel文件:
            IWorkbook book = new HSSFWorkbook();

            if (objList != null && objList.Count > 0)
            {
                var    sheetName  = new string[] { "日租", "月租", "季度租", "半年租", "年租" };
                double sheetCount = sheetName.Count();
                for (int i = 0; i < sheetCount - 1; i++)
                {
                    ISheet   sheet = book.CreateSheet(sheetName[i]);
                    List <T> list  = objList.ToList();

                    int  rowIndex = 0;
                    Type myType   = objList[0].GetType();

                    //创建表头样式
                    ICellStyle style = book.CreateCellStyle();
                    style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
                    style.WrapText  = true;
                    IFont font = book.CreateFont();
                    font.FontHeightInPoints = 16;
                    font.Boldweight         = (short)FontBoldWeight.Bold;
                    font.FontName           = "微软雅黑";
                    style.SetFont(font);//HEAD 样式

                    //根据反射从传递进来的属性名信息得到要显示的属性
                    List <PropertyInfo> myPro = new List <PropertyInfo>();

                    #region 定义表头
                    int m = 0;
                    if (columnInfo != null)
                    {
                        var rowheader = sheet.CreateRow(0);
                        rowheader.Height = 20 * 20;
                        foreach (string cName in columnInfo.Keys)
                        {
                            PropertyInfo p = myType.GetProperty(cName);
                            if (p != null)
                            {
                                myPro.Add(p);
                                rowheader.CreateCell(m).SetCellValue(columnInfo[cName]);
                                m++;
                            }
                        }
                    }
                    #endregion
                    #region 定义表体并赋值
                    //如果没有找到可用的属性则结束
                    if (myPro.Count == 0)
                    {
                        return;
                    }
                    foreach (T obj in list)
                    {
                        int n = 0;
                        if (sheet != null)
                        {
                            rowIndex++;
                            var sheetrow = sheet.CreateRow(rowIndex);
                            sheetrow.Height = 20 * 20;
                            foreach (PropertyInfo p in myPro)
                            {
                                dynamic val     = p.GetValue(obj, null) ?? "";
                                string  valtype = val.GetType().ToString();
                                if (valtype.ToLower().IndexOf("decimal", StringComparison.Ordinal) > -1)
                                {
                                    val = Convert.ToDouble(val);
                                }
                                sheetrow.CreateCell(n).SetCellValue(val);
                                n++;
                            }
                        }
                    }
                    #endregion
                }
            }
            else
            {
                //在工作薄中建立工作表
                XSSFSheet sheet = book.CreateSheet() as XSSFSheet;
                sheet.SetColumnWidth(0, 30 * 256);
                if (sheet != null)
                {
                    sheet.CreateRow(0).CreateCell(0).SetCellValue("暂无数据!");
                }
            }

            if (File.Exists(fileName))
            {
                //存在
                File.Delete(fileName);
            }

            var fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
            fs.Flush();
            book.Write(fs);
            fs.Close();
        }
Пример #26
0
        public async Task <ActionResult> ExportToExcel(MaterialReceiptDetailQueryViewModel model)
        {
            IList <MaterialReceiptDetail> lst = new List <MaterialReceiptDetail>();

            using (MaterialReceiptServiceClient client = new MaterialReceiptServiceClient())
            {
                await Task.Run(() =>
                {
                    PagingConfig cfg = new PagingConfig()
                    {
                        IsPaging = false,
                        OrderBy  = "CreateTime Desc,Key.ReceiptNo,Key.ItemNo",
                        Where    = GetWhereCondition(model)
                    };
                    MethodReturnResult <IList <MaterialReceiptDetail> > result = client.GetDetail(ref cfg);

                    if (result.Code == 0)
                    {
                        lst = 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);
            ICell  cell = null;
            IRow   row  = null;
            ISheet ws   = null;

            for (int j = 0; j < lst.Count; j++)
            {
                if (j % 65535 == 0)
                {
                    ws  = wb.CreateSheet();
                    row = ws.CreateRow(0);
                    #region //列名
                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptNo);  //领料号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_OrderNumber);  //工单号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptDate);  //领料日期

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_ItemNo);  //项目号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_LineStoreName);  //线别仓

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialCode);  //物料编码

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue("物料名称");  //物料名称

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialLot);  //物料批号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_Qty);  //数量

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierMaterialLot);  //供应商批号

                    cell           = row.CreateCell(row.Cells.Count);
                    cell.CellStyle = style;
                    cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierCode);  //供应商编码

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

                MaterialReceiptDetail obj   = lst[j];
                MaterialReceipt       mrObj = model.GetMaterialReceipt(obj.Key.ReceiptNo);
                Material m = model.GetMaterial(obj.MaterialCode);
                Supplier s = model.GetSupplier(obj.SupplierCode);
                row = ws.CreateRow(j + 1);

                #region //数据
                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Key.ReceiptNo);  //领料号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(mrObj == null ? string.Empty : mrObj.OrderNumber);  //工单号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(mrObj == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", mrObj.ReceiptDate));  //领料日期

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Key.ItemNo);  //项目号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.LineStoreName);  //线别仓

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.MaterialCode);  //物料编码

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(m == null ? string.Empty : m.Name);  //物料名称

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.MaterialLot);  //物料批号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Qty);  //数量

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.SupplierMaterialLot);  //供应商批号

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.SupplierCode);  //供应商编码

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称


                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Description);  //描述

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(obj.Editor);  //编辑人

                cell           = row.CreateCell(row.Cells.Count);
                cell.CellStyle = style;
                cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime));  //编辑时间
                #endregion
            }

            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return(File(ms, "application/vnd.ms-excel", "MaterialReceiptData.xls"));
        }
Пример #27
0
        ///  <summary>
        ///從datatable中導出到excel
        ///  </summary>
        ///  <param name="strFileName"> excel文件名</param>
        ///  <param name="dtSource"> datatabe源數據</param>
        ///  <param name="strHeaderText">表名</param>
        ///  <param name="sheetnum"> sheet的編號</param>
        ///  <returns></returns>
        static MemoryStream ExportDT(String strFileName, DataTable dtSource, string strHeaderText, Dictionary <string, string> dir, int sheetnum)
        {
            //創建工作簿和sheet
            IWorkbook workbook = new HSSFWorkbook();

            using (Stream writefile = new FileStream(strFileName, FileMode.OpenOrCreate, FileAccess.Read))
            {
                if (writefile.Length > 0 && sheetnum > 0)
                {
                    workbook = WorkbookFactory.Create(writefile);
                }
            }

            ISheet      sheet     = null;
            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 == 0)
                {
                    string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString());
                    if (workbook.GetSheetIndex(sheetName) >= 0)
                    {
                        workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName));
                    }
                    sheet = workbook.CreateSheet(sheetName);
                    #region 表頭及樣式
                    {
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
                        IRow headerRow = sheet.CreateRow(0);
                        headerRow.HeightInPoints = 25;
                        headerRow.CreateCell(0).SetCellValue(strHeaderText);
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = Horizo​​ntalAlignment.Center;
                        IFont font = workbook.CreateFont();
                        font.FontHeightInPoints = 20;
                        font.Boldweight         = 700;
                        headStyle.SetFont(font);
                        headerRow.GetCell(0).CellStyle = headStyle;

                        rowIndex = 1;
                    }
                    #endregion

                    #region 列頭及樣式

                    if (rowIndex == 1)
                    {
                        IRow       headerRow = sheet.CreateRow(1); //第二行設置列名
                        ICellStyle headStyle = workbook.CreateCellStyle();
                        headStyle.Alignment = Horizo​​ntalAlignment.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(dir[column.ColumnName]);
                            headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
                            //設置列寬
                            sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2);
                        }
                        rowIndex = 2;
                    }
                    #endregion
                }
                #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 ":     //字符串類型
                        double result;
                        if (isNumeric(drValue, out result))
                        {
                            //數字字符串
                            double.TryParse(drValue, out result);
                            newCell.SetCellValue(result);
                            break;
                        }
                        else
                        {
                            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(drValue.ToString());
                        break;
                    }
                }
                #endregion
                rowIndex++;
            }

            using (MemoryStream ms = new MemoryStream())
            {
                workbook.Write(ms);
                ms.Flush();
                ms.Position = 0;
                return(ms);
            }
        }
Пример #28
0
        public string ExportList <T>(T model,
                                     string fileName,
                                     string userId  = "",
                                     string account = "",
                                     bool exportPdf = false)
        {
            try
            {
                #region write dictionary

                var dicValue         = new Dictionary <string, string>();
                var dicValueProperty = new Dictionary <string, string>();
                var properties       = typeof(T).GetProperties(BindingFlags.Instance | BindingFlags.Public);
                properties.ForEach(item =>
                {
                    var attribute = (DescriptionAttribute)item.GetCustomAttribute(typeof(DescriptionAttribute), false);
                    if (attribute != null)
                    {
                        var des = attribute.Description.Split('!');
                        if (des.Length > 2)
                        {
                            if (!dicValue.ContainsKey(des[0]) && !dicValueProperty.ContainsKey(des[0]))
                            {
                                dicValue.Add(des[0], des[1]);
                                dicValueProperty.Add(des[0], des[2]);
                            }
                        }
                    }
                });

                #endregion write dictionary

                #region userPath

                var userPath = GetExportFilePath(fileName);
                if (userPath.Equals(string.Empty))
                {
                    return(string.Empty);
                }

                #endregion

                #region create hssfWorkbook

                HSSFWorkbook hssfWorkbook;

                using (var file = new FileStream(userPath, FileMode.Open, FileAccess.Read))
                {
                    hssfWorkbook = new HSSFWorkbook(file);
                }

                #endregion create hssfWorkbook

                #region cellstyle

                var cellstyle = hssfWorkbook.CreateCellStyle();
                var format    = hssfWorkbook.CreateDataFormat();
                cellstyle.DataFormat          = format.GetFormat("@");
                cellstyle.BorderBottom        = BorderStyle.Thin;
                cellstyle.BorderTop           = BorderStyle.Thin;
                cellstyle.BorderLeft          = BorderStyle.Thin;
                cellstyle.BorderRight         = BorderStyle.Thin;
                cellstyle.VerticalAlignment   = VerticalAlignment.Center;
                cellstyle.FillBackgroundColor = HSSFColor.Red.Index;
                cellstyle.WrapText            = true;

                var font = hssfWorkbook.CreateFont();
                font.Boldweight         = (short)FontBoldWeight.Normal;
                font.FontHeightInPoints = 11;
                cellstyle.SetFont(font);


                var contentstyle = hssfWorkbook.CreateCellStyle();
                contentstyle.DataFormat          = format.GetFormat("@");
                contentstyle.BorderBottom        = BorderStyle.Thin;
                contentstyle.BorderTop           = BorderStyle.Thin;
                contentstyle.BorderLeft          = BorderStyle.Thin;
                contentstyle.BorderRight         = BorderStyle.Thin;
                contentstyle.VerticalAlignment   = VerticalAlignment.Center;
                contentstyle.FillBackgroundColor = HSSFColor.Red.Index;
                contentstyle.WrapText            = true;

                var contentFont = hssfWorkbook.CreateFont();
                contentFont.Boldweight         = (short)FontBoldWeight.Normal;
                contentFont.FontHeightInPoints = 9;
                contentstyle.SetFont(contentFont);


                var titlecellstyle = hssfWorkbook.CreateCellStyle();
                titlecellstyle.BorderBottom        = BorderStyle.Thin;
                titlecellstyle.BorderTop           = BorderStyle.Thin;
                titlecellstyle.BorderLeft          = BorderStyle.Thin;
                titlecellstyle.BorderRight         = BorderStyle.Thin;
                titlecellstyle.VerticalAlignment   = VerticalAlignment.Center;
                titlecellstyle.FillBackgroundColor = HSSFColor.Red.Index;
                titlecellstyle.WrapText            = true;

                var titlefont = hssfWorkbook.CreateFont();
                titlefont.Boldweight         = (short)FontBoldWeight.Bold;
                titlefont.FontHeightInPoints = 11;
                titlecellstyle.SetFont(titlefont);


                var maintitlecellstyle = hssfWorkbook.CreateCellStyle();
                maintitlecellstyle.BorderBottom        = BorderStyle.None;
                maintitlecellstyle.BorderTop           = BorderStyle.None;
                maintitlecellstyle.BorderLeft          = BorderStyle.None;
                maintitlecellstyle.BorderRight         = BorderStyle.None;
                maintitlecellstyle.VerticalAlignment   = VerticalAlignment.Center;
                maintitlecellstyle.Alignment           = HorizontalAlignment.Center;
                maintitlecellstyle.FillBackgroundColor = HSSFColor.Red.Index;
                maintitlecellstyle.WrapText            = true;

                var maintitlefont = hssfWorkbook.CreateFont();
                maintitlefont.Boldweight         = (short)FontBoldWeight.Bold;
                maintitlefont.FontHeightInPoints = 20;
                maintitlecellstyle.SetFont(maintitlefont);


                var subtitlecellstyle = hssfWorkbook.CreateCellStyle();
                subtitlecellstyle.BorderBottom        = BorderStyle.None;
                subtitlecellstyle.BorderTop           = BorderStyle.None;
                subtitlecellstyle.BorderLeft          = BorderStyle.None;
                subtitlecellstyle.BorderRight         = BorderStyle.None;
                subtitlecellstyle.VerticalAlignment   = VerticalAlignment.Center;
                subtitlecellstyle.FillBackgroundColor = HSSFColor.Red.Index;
                subtitlecellstyle.WrapText            = true;

                var subtitleFont = hssfWorkbook.CreateFont();
                subtitleFont.Boldweight         = (short)FontBoldWeight.Normal;
                subtitleFont.FontHeightInPoints = 12;
                subtitlecellstyle.SetFont(subtitleFont);

                #endregion cellstyle


                ISheet sheet;

                sheet = hssfWorkbook.GetSheetAt(0);
                hssfWorkbook.SetSheetName(0, "sheetName");


                var childRowCount = 0;
                for (var rownum = 0; rownum < 30; rownum++)
                {
                    var keyarray = dicValue.Keys.Where(c => c.Split(',')[0] == rownum.ToString()).ToArray();

                    if (!keyarray.Any())
                    {
                        continue;
                    }

                    var row = sheet.CreateRow(rownum + childRowCount);

                    if (keyarray.Count() == 1) //该行只包含一个元素
                    {
                        var key                = keyarray[0];
                        var colrow             = key.Split(',')[0].GetInt() + childRowCount;
                        var valueproperty      = dicValueProperty[key];
                        var valuearray         = dicValue[key].Split(',');
                        var valuePropertyArray = valueproperty.Split(',');
                        var valueHeight        = valuePropertyArray[0].GetInt();
                        var valueWidth         = valuePropertyArray[1].GetInt();

                        if (valuearray.Length == 2)
                        {
                            if (valuearray[1] == "Title")
                            {
                                //设置标题
                                MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, maintitlecellstyle);
                                var maincell       = row.CreateCell(0);
                                var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
                                if (mainchildvalue != null)
                                {
                                    maincell.SetCellValue(mainchildvalue.ToString());
                                }
                                maincell.CellStyle          = maintitlecellstyle;
                                sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
                                //sheet.SetColumnWidth(1, 100 * valueWidth);
                                continue;
                            }
                        }

                        if (valuearray.Length == 2)
                        {
                            MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, titlecellstyle);
                            var maincell       = row.CreateCell(0);
                            var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
                            if (mainchildvalue != null)
                            {
                                maincell.SetCellValue(mainchildvalue.ToString());
                            }
                            maincell.CellStyle = cellstyle;
                            sheet.GetRow(row.RowNum).Height = (short)(100 * valueHeight);
                            sheet.SetColumnWidth(0, int.Parse(valuearray[0]) * DefaultWidth * valueWidth);
                            continue;
                        }

                        if (valuearray.Length == 3) //该行只包含一个元素
                        {
                            MergedCell(sheet, colrow, colrow, 1, valuearray[0].GetInt() - 1, titlecellstyle);

                            var cell = row.CreateCell(0);
                            cell.SetCellValue(valuearray[2]);
                            cell.CellStyle = cellstyle;


                            var cellValue = row.CreateCell(1);
                            cellValue.SetCellValue(model.GetType().GetProperty(valuearray[1]).GetValue(model, null)
                                                   .ToString());
                            cellValue.CellStyle = contentstyle;

                            sheet.GetRow(row.RowNum).Height = (short)(100 * valueHeight);
                        }

                        if (valuearray.Length == 4)
                        {
                            #region DisplayName

                            MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, titlecellstyle);
                            var cell = row.CreateCell(0);
                            cell.SetCellValue(valuearray[2]);
                            cell.CellStyle = cellstyle;
                            sheet.GetRow(colrow).Height = (short)(100 * valueHeight);

                            #endregion DisplayName

                            #region DisplayValue

                            if (!model.GetType().GetProperty(valuearray[1]).PropertyType.Name.Equals("String"))
                            {
                                var list =
                                    model.GetType().GetProperty(valuearray[1]).GetValue(model, null) as
                                    IEnumerable <object>;
                                if (list != null)
                                {
                                    PropertyInfo[] childProperties = null;
                                    list.ForEach(item =>
                                    {
                                        childProperties = item.GetType()
                                                          .GetProperties(BindingFlags.Instance | BindingFlags.Public);
                                    });

                                    var dicListValue         = new Dictionary <string, string>();
                                    var dicListValueProperty = new Dictionary <string, string>();

                                    childProperties?.ToArray().ForEach(item =>
                                    {
                                        var attribute =
                                            (DescriptionAttribute)item.GetCustomAttribute(typeof(DescriptionAttribute),
                                                                                          false);
                                        if (attribute != null)
                                        {
                                            var description = attribute.Description;
                                            var des         = description.Split('!');
                                            if (des.Length == 3)
                                            {
                                                if (!dicListValue.ContainsKey(des[0]))
                                                {
                                                    dicListValue.Add(des[0], des[1]);
                                                    dicListValueProperty.Add(des[0], des[2]);
                                                }
                                            }
                                        }
                                    });

                                    childRowCount++;
                                    var titleRow = sheet.CreateRow(rownum + childRowCount);
                                    dicListValue.ForEach(item =>
                                    {
                                        var mergedColumn = int.Parse(item.Key.Split(',')[1]);
                                        var cellcol      = int.Parse(item.Key.Split(',')[0]);
                                        var newcell      = titleRow.CreateCell(cellcol);
                                        newcell.SetCellValue(item.Value.Split(',')[1]);
                                        newcell.CellStyle = cellstyle;

                                        if (mergedColumn > 1)
                                        {
                                            MergedCell(sheet, titleRow.RowNum, titleRow.RowNum, cellcol,
                                                       cellcol + mergedColumn - 1, cellstyle);
                                        }

                                        dicListValueProperty.ForEach(p =>
                                        {
                                            if (p.Key == item.Key)
                                            {
                                                sheet.GetRow(titleRow.RowNum).Height =
                                                    (short)(100 * int.Parse(p.Value.Split(',')[0]));
                                                sheet.SetColumnWidth(cellcol,
                                                                     DefaultWidth * int.Parse(p.Value.Split(',')[1]));
                                            }
                                        });
                                    });


                                    list.ForEach(item =>
                                    {
                                        childRowCount++;
                                        var newrow = sheet.GetRow(rownum + childRowCount) ??
                                                     sheet.CreateRow(rownum + childRowCount);
                                        dicListValue.ForEach(info =>
                                        {
                                            var cellcol = int.Parse(info.Key.Split(',')[0]);
                                            var newcell = newrow.CreateCell(cellcol);
                                            newcell.SetCellValue(item.GetType().GetProperty(info.Value.Split(',')[0])
                                                                 .GetValue(item, null).ToString());
                                            newcell.CellStyle = contentstyle;
                                            var mergedColumn  = int.Parse(info.Key.Split(',')[1]);
                                            if (mergedColumn > 1)
                                            {
                                                MergedCell(sheet, newrow.RowNum, newrow.RowNum, cellcol,
                                                           cellcol + mergedColumn - 1, contentstyle);
                                            }
                                            dicListValueProperty.ForEach(p =>
                                            {
                                                if (p.Key == info.Key)
                                                {
                                                    sheet.GetRow(newrow.RowNum).Height =
                                                        (short)(100 * int.Parse(p.Value.Split(',')[0]));
                                                }
                                            });
                                        });
                                    });
                                }
                            }
                            else
                            {
                                cell = row.CreateCell(1);
                                var childvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
                                if (childvalue != null)
                                {
                                    cell.SetCellValue(childvalue.ToString());
                                    cell.CellStyle = contentstyle;
                                }
                            }

                            #endregion DisplayValue
                        }
                    }
                    else
                    {
                        for (var colnum = 0; colnum < keyarray.Count(); colnum++)
                        {
                            var key = keyarray[colnum];

                            var colrow = key.Split(',')[0].GetInt() + childRowCount; //行号
                            var col    = key.Split(',')[1].GetInt();                 //列号

                            var valueproperty      = dicValueProperty[key];          //valuepproperty
                            var valuearray         = dicValue[key].Split(',');
                            var valuePropertyArray = valueproperty.Split(',');
                            var valueHeight        = valuePropertyArray[0].GetInt();
                            var valueWidth         = valuePropertyArray[1].GetInt();

                            if (valuearray[1] == "SubTitle")
                            {
                                //设置副标题
                                MergedCell(sheet, colrow, colrow, 0, valuearray[0].GetInt() - 1, subtitlecellstyle);
                                var maincell       = row.CreateCell(0);
                                var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
                                if (mainchildvalue != null)
                                {
                                    maincell.SetCellValue(mainchildvalue.ToString());
                                }
                                maincell.CellStyle          = subtitlecellstyle;
                                sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
                                continue;
                            }

                            if (valuearray[1] == "Number")
                            {
                                var cellNum = int.Parse(keyarray[1].Split(',')[1]);
                                MergedCell(sheet, colrow, colrow, cellNum, cellNum + valuearray[0].GetInt() - 1,
                                           subtitlecellstyle);
                                var maincell       = row.CreateCell(cellNum);
                                var mainchildvalue = model.GetType().GetProperty(valuearray[1]).GetValue(model, null);
                                if (mainchildvalue != null)
                                {
                                    maincell.SetCellValue(mainchildvalue.ToString());
                                }
                                maincell.CellStyle          = subtitlecellstyle;
                                sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
                                continue;
                            }


                            #region DisplayName

                            var cell = row.CreateCell(col);
                            cell.SetCellValue(valuearray[2]);
                            cell.CellStyle = cellstyle;
                            sheet.GetRow(rownum).Height = (short)(100 * valueHeight);
                            sheet.SetColumnWidth(col, DefaultWidth * valueWidth);

                            #endregion DisplayName

                            #region DisplayValue

                            cell = row.CreateCell(col + 1);

                            var value = model.GetType()?.GetProperty(valuearray[1])?.GetValue(model, null);
                            if (value != null)
                            {
                                cell.SetCellValue(value.ToString());
                            }

                            cell.CellStyle = contentstyle;

                            #endregion DisplayValue

                            #region marge

                            if (valuearray[0].GetInt() > 2)
                            {
                                MergedCell(sheet, colrow, colrow, col + 1, col + valuearray[0].GetInt() - 1,
                                           contentstyle);
                            }

                            var margedCol = valuearray[0].GetInt() - 1;
                            for (var i = 0; i < margedCol; i++)
                            {
                                sheet.SetColumnWidth(col + 1 + i, DefaultWidth * valueWidth);
                            }

                            #endregion
                        }
                    }
                }

                using (var fs = File.OpenWrite(userPath))
                {
                    hssfWorkbook.Write(fs);
                }

                if (exportPdf && ExportPdfFile.Equals("是"))
                {
                    userPath = CreateOfficePdfFrom(userPath);
                }
                if (ExportZipFile.Equals("是") && !exportPdf)
                {
                    userPath = CreateExcelZipFile(userPath, "xls", account);
                }

                return(userPath);
            }
            catch (Exception exception)
            {
                return(exception.Message);
            }
        }
Пример #29
0
        public async Task <ActionResult> ExportToExcel(LotPackageQueryViewModel model)
        {
            IList <PackageDetail> lstLotPackage = new List <PackageDetail>();

            ZPVMLotPackageViewModel m = new ZPVMLotPackageViewModel();

            using (PackageQueryServiceClient client = new PackageQueryServiceClient())
            {
                await Task.Run(() =>
                {
                    PagingConfig cfg = new PagingConfig()
                    {
                        IsPaging = false,
                        OrderBy  = "Key.PackageNo,ItemNo",
                        Where    = GetQueryCondition(model)
                    };
                    MethodReturnResult <IList <PackageDetail> > result = client.GetDetail(ref cfg);

                    if (result.Code == 0)
                    {
                        lstLotPackage = 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 < lstLotPackage.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(StringResource.ItemNo);  //项目号

                    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;
                }
                PackageDetail obj     = lstLotPackage[j];
                IRow          rowData = ws.CreateRow(j + 1);
                Lot           lot     = m.GetLotData(obj.Key.ObjectNumber);
                IVTestData    ivtest  = m.GetIVTestData(obj.Key.ObjectNumber);
                List <string> dic     = null;
                string        ff      = "";
                OemData       oemData = m.GetOemData(obj.Key.ObjectNumber);
                if (oemData != null)
                {
                    dic = m.GetCodeAndItemNo(oemData);
                    ff  = (oemData.FF * 100).ToString("F4");
                }
                else
                {
                    lot    = m.GetLotData(obj.Key.ObjectNumber);
                    ivtest = m.GetIVTestData(obj.Key.ObjectNumber);
                }

                #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.PackageNo);

                cellData           = rowData.CreateCell(rowData.Cells.Count);
                cellData.CellStyle = style;
                cellData.SetCellValue(obj.ItemNo);

                cellData           = rowData.CreateCell(rowData.Cells.Count);
                cellData.CellStyle = style;
                cellData.SetCellValue(obj.Key.ObjectNumber);

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

                if (oemData != null)
                {
                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.Grade);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.Color);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.PMAX);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.ISC);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.IPM);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.VOC);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.VPM);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ff);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.PnName == null ? string.Empty : oemData.PnName);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(oemData.PsSubCode);
                }
                else
                {
                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(lot != null ? lot.Grade : string.Empty);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(lot != null ? lot.Color : string.Empty);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.CoefPM : 0);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.CoefISC : 0);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.CoefIPM : 0);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.CoefVOC : 0);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.CoefVPM : 0);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.CoefFF : 0);

                    string powerName = string.Empty;
                    if (ivtest != null &&
                        !string.IsNullOrEmpty(ivtest.PowersetCode) &&
                        ivtest.PowersetItemNo != null)
                    {
                        powerName = m.GetPowersetName(ivtest.Key.LotNumber, ivtest.PowersetCode, ivtest.PowersetItemNo.Value);
                    }
                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(powerName);

                    cellData           = rowData.CreateCell(rowData.Cells.Count);
                    cellData.CellStyle = style;
                    cellData.SetCellValue(ivtest != null ? ivtest.PowersetSubCode : string.Empty);
                }

                cellData           = rowData.CreateCell(rowData.Cells.Count);
                cellData.CellStyle = style;
                cellData.SetCellValue(string.Format("{0:yyyy-MM-dd}", obj.CreateTime));
                #endregion
            }

            MemoryStream ms = new MemoryStream();
            wb.Write(ms);
            ms.Flush();
            ms.Position = 0;
            return(File(ms, "application/vnd.ms-excel", "LotPackageData.xls"));
        }
Пример #30
0
        /// <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>
    /// DataTable导出到Excel的MemoryStream Export()
    /// </summary>
    /// <param name="dtSource">DataTable数据源</param>
    /// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
    public static MemoryStream ExportMemoryStream(DataTable[] dtSource, ExcelConfig[] excelConfig)
    {
        HSSFWorkbook workbook = new HSSFWorkbook();
        MemoryStream ms       = new MemoryStream();

        for (int dtIndex = 0, length = dtSource.Length; dtIndex < length; dtIndex++)
        {
            // int colint = 0;
            DataTable data       = dtSource[dtIndex].Copy();
            var       tatalCount = dtSource[dtIndex].Columns.Count;
            // var index = 0;

            /* for (int i = 0; i < dtSource[dtIndex].Columns.Count;)
             * {
             *   index++;
             *   DataColumn column = dtSource[dtIndex].Columns[i];
             *   if (excelConfig[dtIndex].ColumnEntity[colint].Column != column.ColumnName)
             *   {
             *       dtSource[dtIndex].Columns.Remove(column.ColumnName);
             *   }
             *   else
             *   {
             *       i++;
             *       colint++;
             *       if (colint == excelConfig[dtIndex].ColumnEntity.Count)
             *       {
             *           for (var j = index; j < tatalCount; j++)
             *           {
             *               DataColumn column1 = data.Columns[j];
             *               dtSource[dtIndex].Columns.Remove(column1.ColumnName);
             *           }
             *           break;
             *       }
             *   }
             * }*/
            ISheet sheet = workbook.CreateSheet((dtIndex + 1).ToString());

            #region 右击文件 属性信息
            {
                DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
                dsi.Company = "NPOI";
                workbook.DocumentSummaryInformation = dsi;

                SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
                si.Author                   = "administrator";    //填加xls文件作者信息
                si.ApplicationName          = "ZoonTop";          //填加xls文件创建程序信息
                si.LastAuthor               = "administrator";    //填加xls文件最后保存者信息
                si.Comments                 = "ZoonTop自动生成excel"; //填加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[dtIndex].Columns.Count];
            string[]       arrColName  = new string[dtSource[dtIndex].Columns.Count];       //列名
            ColumnEntity[] columnModel = new ColumnEntity[dtSource[dtIndex].Columns.Count]; //航头属性

            ICellStyle[] arryColumStyle = new ICellStyle[dtSource[dtIndex].Columns.Count];  //样式表


            if (excelConfig[dtIndex].Background != new Color())
            {
                if (excelConfig[dtIndex].Background != new Color())
                {
                    headStyle.FillPattern         = FillPattern.SolidForeground;
                    headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig[dtIndex].Background);
                }
            }
            //title文字
            IFont font = workbook.CreateFont();
            font.FontHeightInPoints = excelConfig[dtIndex].TitlePoint;
            if (excelConfig[dtIndex].ForeColor != new Color())
            {
                font.Color = GetXLColour(workbook, excelConfig[dtIndex].ForeColor);
            }
            font.Boldweight         = 700;
            font.FontHeightInPoints = 20;
            headStyle.SetFont(font);
            headStyle.ShrinkToFit = true;
            //垂直居中,水平居中
            headStyle.VerticalAlignment = VerticalAlignment.Center;
            headStyle.Alignment         = HorizontalAlignment.Center;
            //边框样式
            headStyle.BorderLeft   = BorderStyle.Thin;
            headStyle.BorderRight  = BorderStyle.Thin;
            headStyle.BorderTop    = BorderStyle.Thin;
            headStyle.BorderBottom = BorderStyle.Thin;
            #endregion

            #region 列头及样式
            ICellStyle cHeadStyle = workbook.CreateCellStyle();
            cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
            IFont cfont = workbook.CreateFont();
            cfont.FontHeightInPoints = 15;                     // excelConfig.HeadPoint;
            cHeadStyle.SetFont(cfont);
            //边框样式
            cHeadStyle.BorderLeft   = BorderStyle.Thin;
            cHeadStyle.BorderRight  = BorderStyle.Thin;
            cHeadStyle.BorderTop    = BorderStyle.Thin;
            cHeadStyle.BorderBottom = BorderStyle.Thin;
            //垂直居中
            cHeadStyle.VerticalAlignment = VerticalAlignment.Center;
            #endregion

            #region 设置内容单元格样式
            foreach (DataColumn item in dtSource[dtIndex].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[dtIndex].ColumnEntity != null)
                {
                    ColumnEntity columnentity = excelConfig[dtIndex].ColumnEntity.Find(t => t.Column == item.ColumnName);
                    if (columnentity != null)
                    {
                        arrColName[item.Ordinal]  = columnentity.ExcelColumn;
                        columnModel[item.Ordinal] = columnentity;
                        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);
                    }
                }
                //边框样式
                columnStyle.BorderLeft   = BorderStyle.Thin;
                columnStyle.BorderRight  = BorderStyle.Thin;
                columnStyle.BorderTop    = BorderStyle.Thin;
                columnStyle.BorderBottom = BorderStyle.Thin;
                //单元格文字
                IFont columnsFont = workbook.CreateFont();
                columnsFont.FontHeightInPoints = 12;
                columnStyle.SetFont(columnsFont);
                //columnStyle.ShrinkToFit = true;
                columnStyle.WrapText = true;//自动换行
                //垂直居中
                columnStyle.VerticalAlignment = VerticalAlignment.Center;

                arryColumStyle[item.Ordinal] = columnStyle;
            }
            if (excelConfig[dtIndex].IsAllSizeColumn)
            {
                #region 根据列中最长列的长度取得列宽
                for (int i = 0; i < dtSource[dtIndex].Rows.Count; i++)
                {
                    for (int j = 0; j < dtSource[dtIndex].Columns.Count; j++)
                    {
                        if (arrColWidth[j] != 0)
                        {
                            int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource[dtIndex].Rows[i][j].ToString()).Length;
                            if (intTemp > arrColWidth[j])
                            {
                                arrColWidth[j] = intTemp;
                            }
                        }
                    }
                }
                #endregion
            }
            #endregion

            #region 填充数据
            ICellStyle  dateStyle = workbook.CreateCellStyle();
            IDataFormat format    = workbook.CreateDataFormat();
            dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");

            string xh          = dtSource[dtIndex].Columns[0].ToString();  //第一个参数,用来合并单元格
            int    rowIndex    = 0;
            int    createIndex = 0;                                        //已经创建的行数
            int    dataIndex   = 1;
            int[]  mergeRows   = new int[dtSource[dtIndex].Columns.Count]; //需要合并的行数

            IRow[]   headerRows;
            IRow     headerRow;
            bool     IsCellRangeAddress;
            int      titleRow   = 0;
            double[] totalCount = new double[dtSource[dtIndex].Columns.Count];
            if (dtSource[dtIndex].Rows.Count == 0)//当列表头大于65536行时会报错,但是....
            {
                #region 新建表,填充表头,填充列头,样式
                #region 表头及样式
                if (excelConfig[dtIndex].Title != null)
                {
                    headerRow = sheet.CreateRow(0);
                    if (excelConfig[dtIndex].TitleHeight != 0)
                    {
                        headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
                    }
                    headerRow.HeightInPoints = 25;
                    headerRow.Height         = 45 * 20;
                    headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
                    headerRow.GetCell(0).CellStyle = headStyle;
                    //标题的宽高
                    sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
                }
                #endregion

                #region 列头及样式
                IsCellRangeAddress = false;
                rowIndex           = 1;
                foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
                {
                    if (columnEntity.IsCellRangeAddress)
                    {
                        rowIndex           = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
                        IsCellRangeAddress = true;
                    }
                }
                headerRows = new IRow[rowIndex];
                for (int hi = 0; hi < rowIndex; hi++)
                {
                    headerRow        = sheet.CreateRow(hi + 1);
                    headerRow.Height = 30 * 20;
                    headerRows[hi]   = headerRow;
                }

                #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出
                if (!IsCellRangeAddress)//简单表头
                {
                    foreach (DataColumn column in dtSource[dtIndex].Columns)
                    {
                        headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
                        headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
                        sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
                    }
                }
                else//复杂表头
                {
                    int[] indexs = new int[rowIndex];
                    int   max    = 0;
                    foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
                    {
                        int headerIndex = columnEntity.Top - 1;
                        int index       = indexs[headerIndex];
                        for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
                        {
                            headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
                            headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
                            indexs[headerIndex]++;
                        }
                        headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
                        headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
                        sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
                        if (columnEntity.IsCellRangeAddress)
                        {
                            //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域
                            //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
                            sheet.AddMergedRegion(new CellRangeAddress(
                                                      columnEntity.Top,
                                                      columnEntity.Bottom,
                                                      columnEntity.Left,
                                                      columnEntity.Right
                                                      ));
                        }
                        indexs[headerIndex]++;
                        max = max > indexs[headerIndex] ? max : indexs[headerIndex];
                    }
                    //填充表头的空缺
                    for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
                    {
                        for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
                        {
                            headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
                            headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
                        }
                    }
                    #endregion
                }
                rowIndex   += 1;
                createIndex = rowIndex;
                #endregion

                #endregion

                #region 合计
                IRow totalRow = sheet.CreateRow(rowIndex);
                totalRow.Height = 30 * 20;
                foreach (DataColumn column in dtSource[dtIndex].Columns)
                {
                    if (column.Ordinal == 0)
                    {
                        totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
                    }
                    else
                    {
                        if (column.DataType.FullName.Equals("System.Double"))
                        {
                            totalRow.CreateCell(column.Ordinal).SetCellValue(0);
                        }
                        else
                        {
                            totalRow.CreateCell(column.Ordinal).SetCellValue("");
                        }
                    }
                    totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
                    sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
                }
                #endregion
            }
            foreach (DataRow row in dtSource[dtIndex].Rows)
            {
                #region 新建表,填充表头,填充列头,样式 如果没有数据表头为空
                if (rowIndex == 65535 || rowIndex == 0)
                {
                    if (rowIndex != 0)//数据过多创建新的sheet页
                    {
                        sheet = workbook.CreateSheet();
                    }

                    #region 表头及样式
                    if (excelConfig[dtIndex].Title != null)
                    {
                        headerRow = sheet.CreateRow(0);
                        if (excelConfig[dtIndex].TitleHeight != 0)
                        {
                            headerRow.Height = (short)(excelConfig[dtIndex].TitleHeight * 20);
                        }
                        headerRow.HeightInPoints = 25;
                        headerRow.Height         = 45 * 20;
                        headerRow.CreateCell(0).SetCellValue(excelConfig[dtIndex].Title);
                        headerRow.GetCell(0).CellStyle = headStyle;
                        //标题的宽高
                        sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource[dtIndex].Columns.Count - 1)); // ------------------
                    }
                    #endregion

                    #region 列头及样式
                    IsCellRangeAddress = false;
                    rowIndex           = 1;
                    foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
                    {
                        if (columnEntity.IsCellRangeAddress)
                        {
                            rowIndex           = rowIndex > columnEntity.Bottom ? rowIndex : columnEntity.Bottom;
                            IsCellRangeAddress = true;
                        }
                    }
                    headerRows = new IRow[rowIndex];
                    for (int hi = 0; hi < rowIndex; hi++)
                    {
                        headerRow        = sheet.CreateRow(hi + 1);
                        headerRow.Height = 30 * 20;
                        headerRows[hi]   = headerRow;
                    }

                    #region 如果设置了列标题就按列标题定义列头,没定义直接按字段名输出
                    if (!IsCellRangeAddress)//简单表头
                    {
                        foreach (DataColumn column in dtSource[dtIndex].Columns)
                        {
                            headerRows[0].CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
                            headerRows[0].GetCell(column.Ordinal).CellStyle = cHeadStyle;
                            //设置列宽 // 第二个参数的单位是1/256个字符宽度,但与前端不一致,故改为48
                            //sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
                            sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
                        }
                    }
                    else//复杂表头
                    {
                        int[] indexs = new int[rowIndex];
                        int   max    = 0;
                        foreach (ColumnEntity columnEntity in excelConfig[dtIndex].ColumnEntity)
                        {
                            int headerIndex = columnEntity.Top - 1;
                            int index       = indexs[headerIndex];
                            for (int i = 0; i < columnEntity.Left - index; i++)//填充合并项的空缺
                            {
                                headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue("");
                                headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
                                indexs[headerIndex]++;
                            }
                            headerRows[headerIndex].CreateCell(indexs[headerIndex]).SetCellValue(columnEntity.ExcelColumn);
                            headerRows[headerIndex].GetCell(indexs[headerIndex]).CellStyle = cHeadStyle;
                            sheet.SetColumnWidth(indexs[headerIndex], columnEntity.Width * 32);
                            if (columnEntity.IsCellRangeAddress)
                            {
                                //设置一个合并单元格区域,使用上下左右定义CellRangeAddress区域
                                //CellRangeAddress四个参数为:起始行,结束行,起始列,结束列
                                sheet.AddMergedRegion(new CellRangeAddress(
                                                          columnEntity.Top,
                                                          columnEntity.Bottom,
                                                          columnEntity.Left,
                                                          columnEntity.Right
                                                          ));
                            }
                            indexs[headerIndex]++;
                            max = max > indexs[headerIndex] ? max : indexs[headerIndex];
                        }
                        //填充表头的空缺
                        for (int headerDefectCell = 0; headerDefectCell < indexs.Length; headerDefectCell++)
                        {
                            for (int headerDefectCellNum = indexs[headerDefectCell]; headerDefectCellNum < max; headerDefectCellNum++)
                            {
                                headerRows[headerDefectCell].CreateCell(headerDefectCellNum).SetCellValue("");
                                headerRows[headerDefectCell].GetCell(headerDefectCellNum).CellStyle = cHeadStyle;
                            }
                        }
                        #endregion
                    }
                    rowIndex   += 1;
                    createIndex = rowIndex;
                    titleRow    = rowIndex;
                    #endregion
                }
                #endregion

                #region 填充内容

                if (createIndex <= rowIndex)
                {
                    IRow dataRow = sheet.CreateRow(rowIndex);
                    dataRow.Height = 30 * 20;
                    createIndex++;
                }
                foreach (DataColumn column in dtSource[dtIndex].Columns)
                {
                    if (columnModel[column.Ordinal].Merge == "row")//合并行,行和列不可能同时出现在一个单元格
                    {
                        ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
                        newCell.CellStyle = arryColumStyle[column.Ordinal];
                        string drValue = row[column].ToString();
                        if (mergeRows[column.Ordinal] == 0)//计算要合并几行
                        {
                            //不计算父子关系,同级关系,只要和下一个相同,就合并 to edit
                            //改成只有序号一样的合并 to edit
                            //增加父级序号也要一致(父级序号是子级的子集:父级:B,子级BB)
                            for (int subDataIndex = dataIndex, total = dtSource[dtIndex].Rows.Count; subDataIndex < total; subDataIndex++)
                            {
                                /*if (drValue.Equals(dtSource[dtIndex].Rows[subDataIndex][column].ToString()))
                                 * {
                                 *  mergeRows[column.Ordinal]++;
                                 * }*/
                                string[] arr  = column.ColumnName.ToString().Split('_');
                                string   mark = "";

                                if (arr.Length > 1)
                                {
                                    int  markCount    = 0;
                                    int  markLength   = arr[0].Length;
                                    bool isAllEqually = true;
                                    foreach (DataColumn parentColumn in dtSource[dtIndex].Columns)
                                    {
                                        if (markCount <= markLength)
                                        {
                                            mark = markCount > 0 ? arr[0].Substring(0, markCount) + "_XH" : "XH";
                                            if (parentColumn.ColumnName.Equals(mark))
                                            {
                                                if (!row[parentColumn.ColumnName].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][parentColumn.ColumnName].ToString()))
                                                {
                                                    isAllEqually = false;
                                                }
                                                markCount++;
                                            }
                                        }
                                        else
                                        {
                                            break;
                                        }
                                    }
                                    if (isAllEqually)
                                    {
                                        mergeRows[column.Ordinal]++;
                                    }
                                }
                                else
                                {
                                    mark = "XH";
                                    //当前序号一致 最外层
                                    if (row[mark].ToString().Equals(dtSource[dtIndex].Rows[subDataIndex][mark].ToString()))
                                    {
                                        mergeRows[column.Ordinal]++;
                                    }
                                }
                            }
                            for (int i = 1; i <= mergeRows[column.Ordinal]; i++)//预创建行
                            {
                                if (sheet.GetRow(rowIndex + i) != null)
                                {
                                    IRow dataRow = sheet.CreateRow(rowIndex + i);
                                    dataRow.Height = 30 * 20;
                                    createIndex++;
                                }
                            }
                            sheet.AddMergedRegion(new CellRangeAddress(
                                                      rowIndex,
                                                      rowIndex + mergeRows[column.Ordinal],
                                                      column.Ordinal,
                                                      column.Ordinal
                                                      ));
                        }
                        else
                        {
                            drValue = "";
                            mergeRows[column.Ordinal]--;
                        }
                        SetCell(newCell, dateStyle, column.DataType, drValue);
                    }
                    else if (columnModel[column.Ordinal].Merge == "col")//合并列
                    {
                    }
                    else//无合并单元格
                    {
                        ICell newCell = sheet.GetRow(rowIndex).CreateCell(column.Ordinal);
                        newCell.CellStyle = arryColumStyle[column.Ordinal];
                        string drValue = row[column].ToString();
                        SetCell(newCell, dateStyle, column.DataType, drValue);
                    }
                    if (column.DataType.FullName.Equals("System.Double"))
                    {
                        double value;
                        try
                        {
                            value = double.Parse(row[column].ToString());
                            totalCount[column.Ordinal] += value;
                        }
                        catch
                        {
                            totalCount[column.Ordinal] += 0;
                        }
                    }
                }
                #endregion

                #region 合计
                if (rowIndex == 65534 || dtSource[dtIndex].Rows.Count + titleRow == rowIndex + 1)//最后一行 或者所有数据填充完
                {
                    IRow totalRow = sheet.CreateRow(rowIndex + 1);
                    totalRow.Height = 30 * 20;
                    foreach (DataColumn column in dtSource[dtIndex].Columns)
                    {
                        if (column.Ordinal == 0)
                        {
                            totalRow.CreateCell(column.Ordinal).SetCellValue("合计");
                        }
                        else
                        {
                            if (column.DataType.FullName.Equals("System.Double"))
                            {
                                if (column.ColumnName.IndexOf("_") > 0)//子表 包含小计
                                {
                                    totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal] / 2);
                                }
                                else
                                {
                                    totalRow.CreateCell(column.Ordinal).SetCellValue(totalCount[column.Ordinal]);
                                }
                            }
                            else
                            {
                                totalRow.CreateCell(column.Ordinal).SetCellValue("");
                            }
                        }
                        totalRow.GetCell(column.Ordinal).CellStyle = arryColumStyle[column.Ordinal];
                        sheet.SetColumnWidth(column.Ordinal, arrColWidth[column.Ordinal] * 48);
                    }
                    rowIndex++;
                }
                #endregion

                rowIndex++;
                dataIndex++;
            }
            #endregion
        }
        workbook.Write(ms);
        ms.Flush();
        ms.Position = 0;
        return(ms);
    }