Пример #1
0
        //public void OutputBinayStream(System.IO.FileInfo file, HttpContext context)
        //{
        //    #region Output Binary Stream
        //    context.Response.Clear();
        //    context.Response.Charset = "GB2312";
        //    context.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name));
        //    context.Response.AddHeader("Content-Length", file.Length.ToString());
        //    context.Response.ContentType = "application/ms-excel";
        //    context.Response.WriteFile(file.FullName);
        //    context.Response.End();
        //    #endregion
        //}

        #endregion

        #region  动态转换成excel
        public static bool ToExcelDynamic(string savePath, string SheetName, string HeadName, DataTable table, Dictionary <string, string> excelDataMap)
        {
            try
            {
                List <int> dateCellIndex = new List <int>();//日期格式列
                bool       header        = true;
                //创建工作薄
                HSSFWorkbook wk = new HSSFWorkbook();
                //创建一个名称为mySheet的表
                ISheet tb = wk.CreateSheet(SheetName);
                #region 表头样式
                ICellStyle headStyle = wk.CreateCellStyle();
                headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                headStyle.Alignment           = HorizontalAlignment.Center;
                IFont font = wk.CreateFont();
                font.FontName           = "宋体";
                font.FontHeightInPoints = 12;
                font.Boldweight         = 700;
                headStyle.SetFont(font);
                #endregion


                #region 行样式
                ICellStyle stylerow = wk.CreateCellStyle();
                stylerow.Alignment = HorizontalAlignment.Center;
                IFont fontrow = wk.CreateFont();
                fontrow.FontName           = "宋体";
                fontrow.FontHeightInPoints = 12;
                stylerow.SetFont(fontrow);
                #endregion

                #region 日期行样式
                ICellStyle  dateStyle = wk.CreateCellStyle();
                IDataFormat format    = wk.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
                #endregion

                //合并标题行
                /// <param name="sheet">要合并单元格所在的sheet</param>
                /// <param name="rowstart">开始行的索引</param>
                /// <param name="rowend">结束行的索引</param>
                /// <param name="colstart">开始列的索引</param>
                /// <param name="colend">结束列的索引</param>
                tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));

                IRow rowhead = tb.CreateRow(0);                                              //创建一行
                rowhead.HeightInPoints = 25;                                                 //行高
                ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                cellhead.CellStyle = headStyle;
                cellhead.SetCellValue(HeadName);                                             //写入表头

                //SetCellRangeAddress(tb, 0, 0, 1, 20);
                int rowIndex = 1;
                if (header)
                {
                    IRow row = tb.CreateRow(rowIndex); //创建一行
                    rowIndex++;
                    row.HeightInPoints = 25;           //行高
                    int cellIndex = 0;                 //开始列索引

                    foreach (var item in excelDataMap)
                    {
                        string columsName = item.Value;

                        tb.SetColumnWidth(cellIndex, 16 * 256);                                      //行宽8个汉字
                        ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                        cell.CellStyle = headStyle;
                        if (columsName == "DEPT_NAME")
                        {
                            cell.SetCellValue("单位名称");
                        }
                        else
                        {
                            cell.SetCellValue(table.Columns[columsName].ToString());//循环往第二行的单元格中添加数据
                        }
                        cellIndex++;
                    }
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    IRow row = tb.CreateRow(rowIndex + i); //创建一行
                    row.HeightInPoints = 20;               //行高
                    int cellIndex = 0;                     //开始列索引

                    foreach (var item in excelDataMap)
                    {
                        string columsName = item.Value;
                        ICell  cell       = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格

                        cell.SetCellValue(table.Rows[i][columsName].ToString());                            //循环往第二行的单元格中添加数据
                        cell.CellStyle = stylerow;

                        cellIndex++;
                    }
                }

                using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
                {
                    wk.Write(fs);                                //向打开的这个xls文件中写入mySheet表并保存。
                }
                return(true);
            }
            catch (Exception)
            {
                return(false);
            }
        }
Пример #2
0
        public static bool ToExcelDate(string savePath, string SheetName, string HeadName, IList listJob, string className)
        {
            try
            {
                DataTable  table         = ToDataTable(listJob, className);
                List <int> dateCellIndex = new List <int>();//日期格式列
                bool       header        = true;
                //创建工作薄
                HSSFWorkbook wk = new HSSFWorkbook();
                //创建一个名称为mySheet的表
                ISheet tb = wk.CreateSheet(SheetName);
                #region 表头样式
                ICellStyle headStyle = wk.CreateCellStyle();
                headStyle.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
                headStyle.Alignment           = HorizontalAlignment.Center;
                IFont font = wk.CreateFont();
                font.FontName           = "宋体";
                font.FontHeightInPoints = 12;
                font.Boldweight         = 700;
                headStyle.SetFont(font);
                #endregion


                #region 行样式
                ICellStyle stylerow = wk.CreateCellStyle();
                stylerow.Alignment = HorizontalAlignment.Center;
                IFont fontrow = wk.CreateFont();
                fontrow.FontName           = "宋体";
                fontrow.FontHeightInPoints = 12;
                stylerow.SetFont(fontrow);
                #endregion

                #region 日期行样式
                ICellStyle  dateStyle = wk.CreateCellStyle();
                IDataFormat format    = wk.CreateDataFormat();
                dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
                #endregion
                Dictionary <string, string> excelDataMap = ReadXml(className, false);
                //合并标题行
                /// <param name="sheet">要合并单元格所在的sheet</param>
                /// <param name="rowstart">开始行的索引</param>
                /// <param name="rowend">结束行的索引</param>
                /// <param name="colstart">开始列的索引</param>
                /// <param name="colend">结束列的索引</param>
                tb.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, excelDataMap.Keys.Count));

                IRow rowhead = tb.CreateRow(0);                                              //创建一行
                rowhead.HeightInPoints = 25;                                                 //行高
                ICell cellhead = rowhead.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                cellhead.CellStyle = headStyle;
                cellhead.SetCellValue(HeadName);                                             //写入表头

                //SetCellRangeAddress(tb, 0, 0, 1, 20);
                int rowIndex = 1;
                if (header)
                {
                    IRow row = tb.CreateRow(rowIndex); //创建一行
                    rowIndex++;
                    row.HeightInPoints = 25;           //行高
                    int cellIndex = 0;                 //开始列索引
                    #region 弃用
                    //for (int i = 0; i < table.Columns.Count; i++)
                    //{
                    //    if (!table.Columns[i].ToString().EndsWith("_wennull"))
                    //    {
                    //        if (table.Columns[i].DataType.ToString() == "System.DateTime")//日期型
                    //        {
                    //            tb.SetColumnWidth(cellIndex, 20 * 256);//行宽10个汉字
                    //            dateCellIndex.Add(cellIndex);
                    //        }
                    //        else
                    //        {
                    //            tb.SetColumnWidth(cellIndex, 16 * 256);//行宽8个汉字
                    //        }
                    //        ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                    //        cell.CellStyle = headStyle;
                    //        cell.SetCellValue(table.Columns[i].ToString());//循环往第二行的单元格中添加数据
                    //        cellIndex++;
                    //    }
                    //}
                    #endregion
                    foreach (var item in excelDataMap)
                    {
                        string columsName = item.Value;
                        if (table.Columns[columsName].DataType.ToString() == "System.DateTime") //日期型
                        {
                            tb.SetColumnWidth(cellIndex, 20 * 256);                             //行宽10个汉字
                            dateCellIndex.Add(cellIndex);
                        }
                        else
                        {
                            tb.SetColumnWidth(cellIndex, 16 * 256);                                  //行宽8个汉字
                        }
                        ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                        cell.CellStyle = headStyle;
                        cell.SetCellValue(table.Columns[columsName].ToString());                     //循环往第二行的单元格中添加数据
                        cellIndex++;
                    }
                }
                for (int i = 0; i < table.Rows.Count; i++)
                {
                    IRow row = tb.CreateRow(rowIndex + i); //创建一行
                    row.HeightInPoints = 20;               //行高
                    int cellIndex = 0;                     //开始列索引
                    #region 弃用
                    //for (int c = 0; c < table.Columns.Count; c++)
                    //{
                    //    if (!table.Columns[c].ToString().EndsWith("_wennull"))
                    //    {
                    //        ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                    //        if (table.Columns[c].DataType.ToString() == "System.DateTime")//日期型
                    //        {
                    //            string value = table.Rows[i][c].ToString();
                    //            if (value != "")
                    //            {
                    //                cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));//循环往第二行的单元格中添加数据
                    //            }
                    //            cell.CellStyle = dateStyle;
                    //        }
                    //        else
                    //        {
                    //            cell.SetCellValue(table.Rows[i][c].ToString());//循环往第二行的单元格中添加数据
                    //            cell.CellStyle = stylerow;
                    //        }
                    //        cellIndex++;
                    //    }
                    //}
                    #endregion

                    foreach (var item in excelDataMap)
                    {
                        string columsName = item.Value;
                        ICell  cell       = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                        if (table.Columns[columsName].DataType.ToString() == "System.DateTime")             //日期型
                        {
                            string value = table.Rows[i][columsName].ToString();
                            if (value != "")
                            {
                                cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd"));//循环往第二行的单元格中添加数据
                            }
                            cell.CellStyle = dateStyle;
                        }
                        else
                        {
                            cell.SetCellValue(table.Rows[i][columsName].ToString());//循环往第二行的单元格中添加数据
                            cell.CellStyle = stylerow;
                        }
                        cellIndex++;
                    }
                }
                //foreach (int cellIndex in dateCellIndex)
                //{
                //    for (int y = table.Rows.Count; y < 500; y++)//至少设置500行格式
                //    {
                //        IRow row = tb.CreateRow(rowIndex + y);//创建一行
                //        row.HeightInPoints = 20; //行高
                //        ICell cell = row.GetCell(cellIndex, MissingCellPolicy.CREATE_NULL_AS_BLANK); //在行中创建单元格
                //        //cell.SetCellValue("");//循环往第二行的单元格中添加数据
                //        cell.CellStyle = dateStyle;
                //    }
                //}
                using (FileStream fs = File.OpenWrite(savePath)) //打开一个xls文件,如果没有则自行创建,如果存在myxls.xls文件则在创建是不要打开该文件!
                {
                    wk.Write(fs);                                //向打开的这个xls文件中写入mySheet表并保存。
                }
                return(true);
            }
            catch (Exception)
            {
                return(false);
            }
        }