NOPI导出execl 多个sheet,一列图片

2023-12-27 21:15:03

NPOI API: http://www.cnblogs.com/atao/archive/2009/11/15/1603528.html

http://blog.csdn.net/pan_junbiao/article/details/39717443 -- NPOI使用手册

http://www.cnblogs.com/wei325/p/4748324.html

每一张表只能有一个HSSFPatriarch对象，如果把它的创建放到了setPic方法中，那么一行只会出现一张图片，最后的图片会消掉之前的图片。也不能放到for循环里

HSSFClientAnchor(dx1,dy1,dx2,dy2,col1,row1,col2,row2)的参数，有必要在这里说明一下：
dx1：起始单元格的x偏移量，如例子中的255表示直线起始位置距A1单元格左侧的距离；
dy1：起始单元格的y偏移量，如例子中的125表示直线起始位置距A1单元格上侧的距离；
dx2：终止单元格的x偏移量，如例子中的1023表示直线起始位置距C3单元格左侧的距离；
dy2：终止单元格的y偏移量，如例子中的150表示直线起始位置距C3单元格上侧的距离；
col1：起始单元格列序号，从0开始计算；
row1：起始单元格行序号，从0开始计算，如例子中col1=0,row1=0就表示起始单元格为A1；
col2：终止单元格列序号，从0开始计算；
row2：终止单元格行序号，从0开始计算，如例子中col2=2,row2=2就表示起始单元格为C3；

//添加多个图片时:多个pic应该share同一个DrawingPatriarch在同一个sheet里面。

        //获取枚举类型的Display特性的name值
        public string GetEnumTxt(Enum eEnum)
        {
            var enumType = eEnum.GetType();
            var field = enumType.GetField(eEnum.ToString());
            var display = field.GetCustomAttributes(typeof(DisplayAttribute), false).FirstOrDefault() as DisplayAttribute;
            return display != null ? display.Name : eEnum.ToString();
        }
    </span><span style="color: #0000ff;">private</span> <span style="color: #0000ff;">void</span> setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch,<span style="color: #0000ff;">string</span> path, ISheet sheet, <span style="color: #0000ff;">int</span> rowline, <span style="color: #0000ff;">int</span><span style="color: #000000;"> col)
    {
        </span><span style="color: #0000ff;">if</span>(<span style="color: #0000ff;">string</span>.IsNullOrEmpty(path))<span style="color: #0000ff;">return</span><span style="color: #000000;">;
        </span><span style="color: #0000ff;">byte</span>[] bytes =<span style="color: #000000;"> System.IO.File.ReadAllBytes(Server.MapPath(path));
        </span><span style="color: #0000ff;">int</span> pictureIdx =<span style="color: #000000;"> workbook.AddPicture(bytes, PictureType.JPEG);
        </span><span style="color: #008000;">//</span><span style="color: #008000;"> 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释</span>
        HSSFClientAnchor anchor = <span style="color: #0000ff;">new</span> HSSFClientAnchor(<span style="color: #800080;">70</span>, <span style="color: #800080;">10</span>, <span style="color: #800080;">0</span>, <span style="color: #800080;">0</span>, col, rowline, col+<span style="color: #800080;">1</span>, rowline + <span style="color: #800080;">1</span><span style="color: #000000;">);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">把图片插到相应的位置</span>
        HSSFPicture pict =<span style="color: #000000;"> (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
    }</span></pre>

View Code

private void setPic(HSSFWorkbook workbook, HSSFPatriarch patriarch,string path, ISheet sheet, int rowline, int col)
        {
            if(string.IsNullOrEmpty(path))return;
            byte[] bytes = System.IO.File.ReadAllBytes(Server.MapPath(path));
            int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);
            // 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2) 后面再作解释
            HSSFClientAnchor anchor = new HSSFClientAnchor(70, 10, 0, 0, col, rowline, col+1, rowline + 1);
            //把图片插到相应的位置
            HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
        }

View Code

导出多个sheet,一列图片

        /// <summary>
        /// 导出EXCEL,可以导出多个sheet(图片)
        /// </summary>
        /// <param name="dtSources">sheet数据源</param>
        /// <param name="lstColname">sheet列名数据</param>
        /// <param name="sheetname">sheet名</param>
        /// <param name="filename">文件名</param>
        /// <param name="mColImage">图像列</param>
        /// <returns></returns>
        public static string ExporMultiSheetImage(List<DataTable> dtSources, List<List<string>> lstColname, List<string> sheetname, string filename,int mColImage)
        {
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook();
            string FileName = filename + ".xls";
            for (int k = 0; k < dtSources.Count; k++)
            {
                //DataTable dt = dtSources[k];
                int iSheetCount = Convert.ToInt32(Math.Ceiling(Convert.ToDouble(dtSources[k].Rows.Count) / 10000));
                iSheetCount = iSheetCount == 0 ? 1 : iSheetCount;
            </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> iSheetIndex = <span style="color: #800080;">0</span>; iSheetIndex &lt; iSheetCount; iSheetIndex++<span style="color: #000000;">)
            {
                </span><span style="color: #0000ff;">#region</span> 图最大列

                <span style="color: #0000ff;">int</span> mImageColMerage = <span style="color: #800080;">0</span>;    <span style="color: #008000;">//</span><span style="color: #008000;">  图最大列</span>
                Dictionary&lt;<span style="color: #0000ff;">int</span>, List&lt;<span style="color: #0000ff;">string</span>&gt;&gt; dicImagePath = <span style="color: #0000ff;">new</span> Dictionary&lt;<span style="color: #0000ff;">int</span>, List&lt;<span style="color: #0000ff;">string</span>&gt;&gt;<span style="color: #000000;">();

                </span><span style="color: #0000ff;">if</span> (mColImage &gt; -<span style="color: #800080;">1</span><span style="color: #000000;">)
                {
                    List</span>&lt;<span style="color: #0000ff;">int</span>&gt; lstImageColLen = <span style="color: #0000ff;">new</span> List&lt;<span style="color: #0000ff;">int</span>&gt;<span style="color: #000000;">();

                    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = iSheetIndex * <span style="color: #800080;">10000</span>; i &lt; dtSources[k].Rows.Count; i++<span style="color: #000000;">)
                    {
                        List</span>&lt;<span style="color: #0000ff;">string</span>&gt; lstImagePath = dtSources[k].Rows[i][mColImage].ToString().Trim(<span style="color: #800000;">'</span><span style="color: #800000;">,</span><span style="color: #800000;">'</span>).Split(<span style="color: #0000ff;">new</span> <span style="color: #0000ff;">char</span>[] { <span style="color: #800000;">'</span><span style="color: #800000;">,</span><span style="color: #800000;">'</span><span style="color: #000000;"> }, StringSplitOptions.RemoveEmptyEntries).ToList();
                        dicImagePath[i] </span>=<span style="color: #000000;"> lstImagePath;
                        lstImageColLen.Add(lstImagePath.Count());
                    }
                    mImageColMerage </span>=<span style="color: #000000;"> lstImageColLen.Max();
                }

                </span><span style="color: #0000ff;">#endregion</span>

                <span style="color: #008000;">//</span><span style="color: #008000;">create sheet </span>
                <span style="color: #0000ff;">string</span> sheetName = <span style="color: #0000ff;">string</span><span style="color: #000000;">.Empty; 
                </span><span style="color: #0000ff;">if</span> (iSheetCount&gt;<span style="color: #800080;">1</span><span style="color: #000000;">)
                {
                    sheetName </span>= sheetname[k] +<span style="color: #000000;"> iSheetIndex;
                }
                </span><span style="color: #0000ff;">else</span><span style="color: #000000;">
                {
                    sheetName </span>=<span style="color: #000000;"> sheetname[k];
                }
                ISheet sheet </span>=<span style="color: #000000;"> workbook.CreateSheet(sheetName);
                sheet.PrintSetup.Landscape </span>= <span style="color: #0000ff;">true</span>;     <span style="color: #008000;">//</span><span style="color: #008000;">是否横向排版</span>
                sheet.FitToPage = <span style="color: #0000ff;">false</span>;               <span style="color: #008000;">//</span><span style="color: #008000;">是否自适应页面</span>
                sheet.PrintSetup.Scale = <span style="color: #800080;">50</span>;           <span style="color: #008000;">//</span><span style="color: #008000;">缩放比例

                </span><span style="color: #008000;">//</span><span style="color: #008000;">填充列标题以及样式  </span>
                <span style="color: #0000ff;">int</span> rowsNum = <span style="color: #800080;">0</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号  </span>
                IRow headerRow =<span style="color: #000000;"> sheet.CreateRow(rowsNum);
                ICellStyle headStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
                headStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.CENTER;
                headerRow.HeightInPoints </span>= <span style="color: #800080;">20</span><span style="color: #000000;">;
                headStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.CENTER;
                </span><span style="color: #008000;">//</span><span style="color: #008000;">换行
                </span><span style="color: #008000;">//</span><span style="color: #008000;">headStyle.WrapText = true;</span>


                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 13;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);
                </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i &lt; lstColname[k].Count; i++<span style="color: #000000;">)
                {
                    </span><span style="color: #0000ff;">if</span> (i &lt;<span style="color: #000000;"> mColImage)
                    {
                        </span><span style="color: #008000;">//</span><span style="color: #008000;">合并列前</span>

                            headerRow.CreateCell(i, CellType.STRING).SetCellValue(lstColname[k][i]);

headerRow.GetCell(i).CellStyle = headStyle;

}

else if (mColImage > -1 && i == mColImage)

{

headerRow.CreateCell(i, CellType.STRING).SetCellValue(lstColname[k][i]);

headerRow.GetCell(i).CellStyle = headStyle;

sheet.AddMergedRegion(new CellRangeAddress(0, 0, mColImage, mColImage + mImageColMerage));

}

else

{

//合并列后

headerRow.CreateCell(i + mImageColMerage, CellType.STRING).SetCellValue(lstColname[k][i]);

headerRow.GetCell(i + mImageColMerage).CellStyle = headStyle;

}

//sheet.AutoSizeColumn(i);

sheet.SetColumnWidth(i, 5000);  //列宽

//headerRow.Cells[i].SetCellValue(new HSSFRichTextString("\r\n"));  //强制换行

                    }
                ICellStyle cellStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
                cellStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.CENTER;
                cellStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.CENTER;
                IFont cellfont </span>=<span style="color: #000000;"> workbook.CreateFont();
                cellfont.FontHeightInPoints </span>= <span style="color: #800080;">10</span><span style="color: #000000;">;
                cellStyle.SetFont(cellfont);
                cellStyle.WrapText </span>= <span style="color: #0000ff;">true</span><span style="color: #000000;">;

                </span><span style="color: #0000ff;">#region</span> MyRegion

                <span style="color: #008000;">//</span><span style="color: #008000;">填充数据行  </span>
                IRow dataRow = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
                rowsNum </span>= <span style="color: #800080;">1</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号，从第2行开始  </span>
                <span style="color: #808080;">///</span><span style="color: #008000;"> patriarch 画图的*管理器，一个sheet只能获取一个（一定要注意这点）</span>
                HSSFPatriarch patriarch =<span style="color: #000000;"> (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                </span><span style="color: #008000;">//</span><span style="color: #008000;">填充内容</span>
                <span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = iSheetIndex * <span style="color: #800080;">10000</span>; i &lt; dtSources[k].Rows.Count; i++<span style="color: #000000;">)
                {
                    dataRow </span>= sheet.CreateRow(i + <span style="color: #800080;">1</span><span style="color: #000000;">);

                    </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j &lt; dtSources[k].Columns.Count; j++<span style="color: #000000;">)
                    {
                        </span><span style="color: #008000;">//</span><span style="color: #008000;">sheet.SetColumnWidth(j, 5000);
                        </span><span style="color: #008000;">//</span><span style="color: #008000;">sheet.AutoSizeColumn(j);</span>
                        <span style="color: #0000ff;">if</span> (j &lt;<span style="color: #000000;"> mColImage)
                        {
                            </span><span style="color: #008000;">//</span><span style="color: #008000;">合并列前</span>

                                dataRow.CreateCell(j).SetCellValue(dtSources[k].Rows[i][j].ToString());

dataRow.GetCell(j).CellStyle = cellStyle;

}

else if (mColImage > -1 && j == mColImage)

{

int mImgIndex = 0;

foreach (var item in dicImagePath[i])

{

string imgPath = HttpContext.Current.Server.MapPath(item);

if (File.Exists(imgPath))

{

byte[] bytes = System.IO.File.ReadAllBytes(imgPath);

int pictureIdx = workbook.AddPicture(bytes, PictureType.JPEG);

///// patriarch 画图的*管理器，一个sheet只能获取一个（一定要注意这点）

//HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();

// 插图片的位置  HSSFClientAnchor（dx1,dy1,dx2,dy2,col1,row1,col2,row2)

//图片左上角为（col, row）第row+1行col+1列，右下角为（ col +1, row +1）第 col +1+1行row +1+1列，宽为100，高为50

HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1000, 250, mColImage + mImgIndex, i + 1, mColImage + mImgIndex, i + 1);

//把图片插到相应的位置

HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);

//pict.Resize(0.7);   //用图片原始大小来显示

                                    }

mImgIndex++;

}

dataRow.CreateCell(j).SetCellValue("");

dataRow.GetCell(j).CellStyle = cellStyle;

sheet.AddMergedRegion(new CellRangeAddress(i + 1, i + 1, mColImage, mColImage + mImageColMerage));

}

else

{

//合并列后

dataRow.CreateCell(j + mImageColMerage).SetCellValue(dtSources[k].Rows[i][j].ToString());

dataRow.GetCell(j + mImageColMerage).CellStyle = cellStyle;

}
                    }
                }

                </span><span style="color: #0000ff;">#endregion</span><span style="color: #000000;">
            }
        }

        </span><span style="color: #0000ff;">string</span> strPhysicsPath = HttpContext.Current.Server.MapPath(Config.AccPath + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span><span style="color: #000000;">);

        </span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">Directory.Exists(strPhysicsPath))
        {
            Directory.CreateDirectory(strPhysicsPath);
        }
        </span><span style="color: #008000;">//</span><span style="color: #008000;">string resultUrl = Config.LogPath.Replace("~", HttpContext.Current.Request.Url.Host + ":" + HttpContext.Current.Request.Url.Port) + "Temp/" + DateTime.Today.Year;</span>
        <span style="color: #0000ff;">string</span> tempUrl = <span style="color: #800000;">"</span><span style="color: #800000;">/</span><span style="color: #800000;">"</span> + DateTime.Today.ToString(<span style="color: #800000;">"</span><span style="color: #800000;">MMdd-</span><span style="color: #800000;">"</span>) + Guid.NewGuid().ToString() + <span style="color: #800000;">"</span><span style="color: #800000;">.xls</span><span style="color: #800000;">"</span><span style="color: #000000;">;
        strPhysicsPath </span>= strPhysicsPath.TrimEnd(<span style="color: #800000;">'</span><span style="color: #800000;">\\</span><span style="color: #800000;">'</span>) +<span style="color: #000000;"> tempUrl;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">-------</span>
        <span style="color: #0000ff;">using</span> (MemoryStream ms = <span style="color: #0000ff;">new</span><span style="color: #000000;"> MemoryStream())
        {
            ms.Flush();
            ms.Position </span>= <span style="color: #800080;">0</span><span style="color: #000000;">;
            workbook.Write(ms);
            </span><span style="color: #0000ff;">using</span> (FileStream fs = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strPhysicsPath, FileMode.Create, FileAccess.Write))
            {
                </span><span style="color: #0000ff;">byte</span>[] fData =<span style="color: #000000;"> ms.ToArray();
                fs.Write(fData, </span><span style="color: #800080;">0</span><span style="color: #000000;">, fData.Length);
                fs.Flush();
            }
            LogHelper.WriteExportLog(filename);
            </span><span style="color: #0000ff;">return</span> Config.AccPath.Replace(<span style="color: #800000;">"</span><span style="color: #800000;">~</span><span style="color: #800000;">"</span>, <span style="color: #800000;">""</span>) + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span> +<span style="color: #000000;"> tempUrl;
        }

        </span><span style="color: #0000ff;">#region</span>  浏览器下载

        <span style="color: #008000;">//</span><span style="color: #008000;">供浏览器下载Excel  
        </span><span style="color: #008000;">//</span><span style="color: #008000;">if (HttpContext.Current.Request.Browser.Browser == "IE")
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    FileName = HttpUtility.UrlEncode(FileName);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">using (MemoryStream ms = new MemoryStream())
        </span><span style="color: #008000;">//</span><span style="color: #008000;">{
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Position = 0;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    workbook.Write(ms);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Flush();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    HttpContext curContext = HttpContext.Current;

        </span><span style="color: #008000;">//</span>    <span style="color: #008000;">//</span><span style="color: #008000;"> 设置编码和附件格式  
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.ContentType = "application/vnd.ms-excel";
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.ContentEncoding = Encoding.UTF8;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.Charset = "";
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.AppendHeader("Content-Disposition",
        </span><span style="color: #008000;">//</span><span style="color: #008000;">         "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.BinaryWrite(ms.GetBuffer());
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Close();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Dispose();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.End();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">}</span>
        <span style="color: #0000ff;">#endregion</span><span style="color: #000000;">
    }</span></pre>

View Code

导出EXCEL,可以导出多个sheet

        /// <summary>
        /// 导出EXCEL,可以导出多个sheet
        /// </summary>
        /// <param name="dtSources">原始数据数组类型</param>
        /// <param name="strFileName">路径</param>
        public static string ExporMultiSheet(DataTable[] dtSources, List<List<string>> lstname, List<string> sheetname, string filename)
        {
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook();
        </span><span style="color: #0000ff;">string</span> FileName = filename + <span style="color: #800000;">"</span><span style="color: #800000;">.xls</span><span style="color: #800000;">"</span><span style="color: #000000;">;

        </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> k = <span style="color: #800080;">0</span>; k &lt; dtSources.Length; k++<span style="color: #000000;">)
        {
            ISheet sheet </span>=<span style="color: #000000;"> workbook.CreateSheet(sheetname[k]);
            </span><span style="color: #008000;">//</span><span style="color: #008000;">设置列宽</span>
            sheet.SetColumnWidth(<span style="color: #800080;">0</span>, <span style="color: #800080;">30</span> * <span style="color: #800080;">256</span><span style="color: #000000;">);
            sheet.SetColumnWidth(</span><span style="color: #800080;">1</span>, <span style="color: #800080;">30</span> * <span style="color: #800080;">256</span><span style="color: #000000;">);
            sheet.SetColumnWidth(</span><span style="color: #800080;">2</span>, <span style="color: #800080;">20</span> * <span style="color: #800080;">256</span><span style="color: #000000;">);
            sheet.SetColumnWidth(</span><span style="color: #800080;">3</span>, <span style="color: #800080;">20</span> * <span style="color: #800080;">256</span><span style="color: #000000;">);
            sheet.SetColumnWidth(</span><span style="color: #800080;">4</span>, <span style="color: #800080;">20</span> * <span style="color: #800080;">256</span><span style="color: #000000;">);
            sheet.SetColumnWidth(</span><span style="color: #800080;">5</span>, <span style="color: #800080;">25</span> * <span style="color: #800080;">256</span><span style="color: #000000;">);
            </span><span style="color: #008000;">//</span><span style="color: #008000;">填充表头
            </span><span style="color: #008000;">//</span><span style="color: #008000;">填充列标题以及样式  </span>
            <span style="color: #0000ff;">int</span> rowsNum = <span style="color: #800080;">0</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号  </span>
            IRow headerRow =<span style="color: #000000;"> sheet.CreateRow(rowsNum);
            ICellStyle headStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
            headStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.CENTER;
            headStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.CENTER;
            </span><span style="color: #008000;">//</span><span style="color: #008000;">换行</span>
            headStyle.WrapText = <span style="color: #0000ff;">true</span><span style="color: #000000;">;

            headerRow.HeightInPoints </span>= <span style="color: #800080;">20</span><span style="color: #000000;">;
            IFont font </span>=<span style="color: #000000;"> workbook.CreateFont();
            font.FontHeightInPoints </span>= <span style="color: #800080;">13</span><span style="color: #000000;">;
            font.Boldweight </span>= <span style="color: #800080;">700</span><span style="color: #000000;">;
            headStyle.SetFont(font);
            </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i &lt; lstname[k].Count; i++<span style="color: #000000;">)
            {
                headerRow.CreateCell(i, CellType.STRING).SetCellValue(lstname[k][i]);
                headerRow.GetCell(i).CellStyle </span>=<span style="color: #000000;"> headStyle;
                </span><span style="color: #008000;">//</span><span style="color: #008000;">headerRow.Cells[i].SetCellValue(new HSSFRichTextString("\r\n"));  </span><span style="color: #008000;">//</span><span style="color: #008000;">强制换行</span>

                }
            ICellStyle cellStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
            cellStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.CENTER;
            cellStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.CENTER;
            IFont cellfont </span>=<span style="color: #000000;"> workbook.CreateFont();
            cellfont.FontHeightInPoints </span>= <span style="color: #800080;">10</span><span style="color: #000000;">;
            cellStyle.SetFont(cellfont);
            cellStyle.WrapText </span>= <span style="color: #0000ff;">true</span><span style="color: #000000;">;

            </span><span style="color: #008000;">//</span><span style="color: #008000;">填充数据行  </span>
            IRow dataRow = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
            rowsNum </span>= <span style="color: #800080;">1</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号，从第2行开始  

            </span><span style="color: #008000;">//</span><span style="color: #008000;">填充内容</span>
            <span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i &lt; dtSources[k].Rows.Count; i++<span style="color: #000000;">)
            {
                dataRow </span>= sheet.CreateRow(i + <span style="color: #800080;">1</span><span style="color: #000000;">);

                </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j &lt; dtSources[k].Columns.Count; j++<span style="color: #000000;">)
                {
                    dataRow.CreateCell(j).SetCellValue(dtSources[k].Rows[i][j].ToString());
                    dataRow.GetCell(j).CellStyle </span>=<span style="color: #000000;"> cellStyle;
                }
            }
        }

        </span><span style="color: #008000;">//</span><span style="color: #008000;">保存</span>
        <span style="color: #0000ff;">string</span> strPhysicsPath = HttpContext.Current.Server.MapPath(Config.AccPath + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span><span style="color: #000000;">);

        </span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">Directory.Exists(strPhysicsPath))
        {
            Directory.CreateDirectory(strPhysicsPath);
        }
        </span><span style="color: #0000ff;">string</span> tempUrl = <span style="color: #800000;">"</span><span style="color: #800000;">/</span><span style="color: #800000;">"</span> + DateTime.Today.ToString(<span style="color: #800000;">"</span><span style="color: #800000;">MMdd-</span><span style="color: #800000;">"</span>) + Guid.NewGuid().ToString() + <span style="color: #800000;">"</span><span style="color: #800000;">.xls</span><span style="color: #800000;">"</span><span style="color: #000000;">;
        strPhysicsPath </span>= strPhysicsPath.TrimEnd(<span style="color: #800000;">'</span><span style="color: #800000;">\\</span><span style="color: #800000;">'</span>) +<span style="color: #000000;"> tempUrl;

        </span><span style="color: #0000ff;">using</span> (MemoryStream ms = <span style="color: #0000ff;">new</span><span style="color: #000000;"> MemoryStream())
        {
            ms.Flush();
            ms.Position </span>= <span style="color: #800080;">0</span><span style="color: #000000;">;
            workbook.Write(ms);
            </span><span style="color: #0000ff;">using</span> (FileStream fs = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strPhysicsPath, FileMode.Create, FileAccess.Write))
            {
                </span><span style="color: #0000ff;">byte</span>[] fData =<span style="color: #000000;"> ms.ToArray();
                fs.Write(fData, </span><span style="color: #800080;">0</span><span style="color: #000000;">, fData.Length);
                fs.Flush();
            }
            LogHelper.WriteExportLog(filename);
            </span><span style="color: #0000ff;">return</span> Config.AccPath.Replace(<span style="color: #800000;">"</span><span style="color: #800000;">~</span><span style="color: #800000;">"</span>, <span style="color: #800000;">""</span>) + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span> +<span style="color: #000000;"> tempUrl;
        }
    }</span></pre>

View Code

图片base64形式怎么转为图片

String u = "Base64";
            // Base64解码
            byte[] b = new BASE64Decoder().decodeBuffer(u);
            // 生成图片
            String imgName = "echartsPhoto";
            String filePath = ServletActionContext.getServletContext().getRealPath("/") + "echarts";
            String fileName = filePath +"/"+ imgName +".png";
        File file </span>= <span style="color: #0000ff;">new</span><span style="color: #000000;"> File(filePath);
        </span><span style="color: #0000ff;">if</span>(!<span style="color: #000000;">file.exists()){
            file.mkdir();
        }
        OutputStream </span><span style="color: #0000ff;">out</span> = <span style="color: #0000ff;">new</span> FileOutputStream(<span style="color: #0000ff;">new</span><span style="color: #000000;"> File(fileName));
        </span><span style="color: #0000ff;">out</span>.write(b);<span style="color: #800080;">18</span>             <span style="color: #0000ff;">out</span>.close();</pre>

View Code

API 导出execl

public static string ExportExecl(DataTable dataSource,string filename)
        {
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook();
        ISheet sheet </span>=<span style="color: #000000;"> workbook.CreateSheet(filename);
        sheet.PrintSetup.Landscape </span>= <span style="color: #0000ff;">true</span>;     <span style="color: #008000;">//</span><span style="color: #008000;">是否横向排版</span>
        sheet.FitToPage = <span style="color: #0000ff;">false</span>;               <span style="color: #008000;">//</span><span style="color: #008000;">是否自适应页面</span>
        sheet.PrintSetup.Scale = <span style="color: #800080;">50</span>;           <span style="color: #008000;">//</span><span style="color: #008000;">缩放比例

        </span><span style="color: #008000;">//</span><span style="color: #008000;">填充列标题以及样式  </span>
        <span style="color: #0000ff;">int</span> rowsNum = <span style="color: #800080;">0</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号  </span>
        IRow headerRow =<span style="color: #000000;"> sheet.CreateRow(rowsNum);
        ICellStyle headStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
        headStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.CENTER;
        headStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.CENTER;
        headerRow.HeightInPoints </span>= <span style="color: #800080;">20</span><span style="color: #000000;">;

        IFont font </span>=<span style="color: #000000;"> workbook.CreateFont();
        font.FontHeightInPoints </span>= <span style="color: #800080;">13</span><span style="color: #000000;">;
        font.Boldweight </span>= <span style="color: #800080;">700</span><span style="color: #000000;">;
        headStyle.SetFont(font);

        </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i &lt; dataSource.Columns.Count; i++<span style="color: #000000;">)
        {
            headerRow.CreateCell(i).SetCellValue(dataSource.Columns[i].ColumnName);
            headerRow.Cells[i].CellStyle </span>=<span style="color: #000000;"> headStyle;
            </span><span style="color: #008000;">//</span><span style="color: #008000;">sheet.SetColumnWidth(i, 5000);</span>

                sheet.AutoSizeColumn(i);

}
        </span><span style="color: #008000;">//</span><span style="color: #008000;">ICellStyle cellStyle = workbook.CreateCellStyle();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">cellStyle.Alignment = HorizontalAlignment.CENTER;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">IFont cellfont = workbook.CreateFont();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">cellfont.FontHeightInPoints = 10;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">cellStyle.SetFont(cellfont);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">cellStyle.WrapText = true;</span>

        <span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">1</span>; i &lt; dataSource.Rows.Count; i++<span style="color: #000000;">)
        {
            IRow datarow </span>=<span style="color: #000000;"> sheet.CreateRow(i);
            </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j &lt; dataSource.Columns.Count; j++<span style="color: #000000;">)
            {
                datarow.CreateCell(j).SetCellValue(dataSource.Rows[i][j].ToString());
                </span><span style="color: #008000;">//</span><span style="color: #008000;">datarow.Cells[j].CellStyle = cellStyle;</span>

                }

}

using (MemoryStream ms = new MemoryStream())

{

workbook.Write(ms);

ms.Flush();

ms.Position = 0;
            </span><span style="color: #008000;">//</span><span style="color: #008000;">保存</span>
            <span style="color: #0000ff;">string</span> strPhysicsPath = HttpContext.Current.Server.MapPath(Config.AccPath + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span><span style="color: #000000;">);

            </span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">Directory.Exists(strPhysicsPath))
            {
                Directory.CreateDirectory(strPhysicsPath);
            }
            </span><span style="color: #0000ff;">string</span> tempUrl = <span style="color: #800000;">"</span><span style="color: #800000;">/</span><span style="color: #800000;">"</span> + DateTime.Today.ToString(<span style="color: #800000;">"</span><span style="color: #800000;">MMdd-</span><span style="color: #800000;">"</span>) + Guid.NewGuid().ToString() + <span style="color: #800000;">"</span><span style="color: #800000;">.xls</span><span style="color: #800000;">"</span><span style="color: #000000;">;
            strPhysicsPath </span>= strPhysicsPath.TrimEnd(<span style="color: #800000;">'</span><span style="color: #800000;">\\</span><span style="color: #800000;">'</span>) +<span style="color: #000000;"> tempUrl;
            </span><span style="color: #0000ff;">using</span> (FileStream fs = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strPhysicsPath, FileMode.Create, FileAccess.Write))
            {
                </span><span style="color: #0000ff;">byte</span>[] fData =<span style="color: #000000;"> ms.ToArray();
                fs.Write(fData, </span><span style="color: #800080;">0</span><span style="color: #000000;">, fData.Length);
                fs.Flush();
            }
            LogHelper.WriteExportLog(filename);
            </span><span style="color: #0000ff;">return</span> Config.AccPath.Replace(<span style="color: #800000;">"</span><span style="color: #800000;">~</span><span style="color: #800000;">"</span>, <span style="color: #800000;">""</span>) + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span> +<span style="color: #000000;"> tempUrl;

            </span><span style="color: #008000;">//</span><span style="color: #008000;">return ms; </span>

            }
        </span><span style="color: #0000ff;">#region</span>  浏览器下载

        <span style="color: #008000;">//</span><span style="color: #008000;">供浏览器下载Excel  
        </span><span style="color: #008000;">//</span><span style="color: #008000;">if (HttpContext.Current.Request.Browser.Browser == "IE")
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    FileName = HttpUtility.UrlEncode(FileName);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">using (MemoryStream ms = new MemoryStream())
        </span><span style="color: #008000;">//</span><span style="color: #008000;">{
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Position = 0;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    workbook.Write(ms);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Flush();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    HttpContext curContext = HttpContext.Current;

        </span><span style="color: #008000;">//</span>    <span style="color: #008000;">//</span><span style="color: #008000;"> 设置编码和附件格式  
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.ContentType = "application/vnd.ms-excel";
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.ContentEncoding = Encoding.UTF8;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.Charset = "";
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.AppendHeader("Content-Disposition",
        </span><span style="color: #008000;">//</span><span style="color: #008000;">         "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.BinaryWrite(ms.GetBuffer());
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Close();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    ms.Dispose();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    curContext.Response.End();
        </span><span style="color: #008000;">//</span><span style="color: #008000;">}</span>
        <span style="color: #0000ff;">#endregion</span><span style="color: #000000;">
    }</span></pre>

View Code

EXECL第一行加标题，合并单元格

/// <summary>
        /// 导出EXECL
        /// </summary>
        /// <param name="data">数据源</param>
        /// <param name="colsname">列名</param>
        /// <param name="filename">文件名</param>
        /// <param name="filePath">文件路径</param>
        /// <param name="isTitle">是否包含标题</param>
        /// <returns>返回文件路径</returns>
        public static string ExportExcel(DataTable data, List<string> colsname, string filename,string filePath = null ,bool isTitle = false)
        {
            //创建工作薄  
            IWorkbook workbook = new HSSFWorkbook();    //else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); }
            ISheet sheet = workbook.CreateSheet(filename);
            string FileName = filename + ".xls";
        </span><span style="color: #008000;">//</span><span style="color: #008000;">填充列标题以及样式  </span>
        <span style="color: #0000ff;">int</span> headRowsNum = <span style="color: #800080;">0</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号  
        </span><span style="color: #008000;">//</span><span style="color: #008000;">是否有标题</span>
        <span style="color: #0000ff;">if</span><span style="color: #000000;"> (isTitle)
        {
            IRow headerTitleRow </span>=<span style="color: #000000;"> sheet.CreateRow(headRowsNum);
            ICellStyle headTitleStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
            headTitleStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.Center;
            headTitleStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.Center;
            headerTitleRow.HeightInPoints </span>= <span style="color: #800080;">20</span><span style="color: #000000;">;

            IFont titlefont </span>=<span style="color: #000000;"> workbook.CreateFont();
            titlefont.FontHeightInPoints </span>= <span style="color: #800080;">13</span><span style="color: #000000;">;
            titlefont.Boldweight </span>= <span style="color: #0000ff;">short</span><span style="color: #000000;">.MaxValue;
            headTitleStyle.SetFont(titlefont);

            headerTitleRow.CreateCell(</span><span style="color: #800080;">0</span><span style="color: #000000;">, CellType.String).SetCellValue(filename);
            headerTitleRow.GetCell(</span><span style="color: #800080;">0</span>).CellStyle =<span style="color: #000000;"> headTitleStyle;
            </span><span style="color: #008000;">//</span><span style="color: #008000;">合并  CellRangeAddress四个参数为：起始行，结束行，起始列，结束列，合并后的内容与样式以该区域最左上角的单元格为准</span>
            CellRangeAddress region = <span style="color: #0000ff;">new</span> CellRangeAddress(<span style="color: #800080;">0</span>, <span style="color: #800080;">0</span>, <span style="color: #800080;">0</span><span style="color: #000000;">, colsname.Count());
            sheet.AddMergedRegion(region);

            headRowsNum</span>++<span style="color: #000000;">;
        }

        IRow headerRow </span>=<span style="color: #000000;"> sheet.CreateRow(headRowsNum);
        ICellStyle headStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
        headStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.Center;
        headStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.Center;
        headerRow.HeightInPoints </span>= <span style="color: #800080;">20</span><span style="color: #000000;">;

        IFont font </span>=<span style="color: #000000;"> workbook.CreateFont();
        font.FontHeightInPoints </span>= <span style="color: #800080;">13</span><span style="color: #000000;">;
        font.Boldweight </span>= <span style="color: #800080;">700</span><span style="color: #000000;">;
        headStyle.SetFont(font);

        </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i &lt; colsname.Count; i++<span style="color: #000000;">)
        {
            headerRow.CreateCell(i, CellType.String).SetCellValue(colsname[i]);
            headerRow.GetCell(i).CellStyle </span>=<span style="color: #000000;"> headStyle;
            </span><span style="color: #008000;">//</span><span style="color: #008000;">sheet.SetColumnWidth(i, 5000);  </span><span style="color: #008000;">//</span><span style="color: #008000;">列宽</span>

                sheet.AutoSizeColumn(i);

}
        ICellStyle cellStyle </span>=<span style="color: #000000;"> workbook.CreateCellStyle();
        cellStyle.Alignment </span>=<span style="color: #000000;"> HorizontalAlignment.Center;
        cellStyle.VerticalAlignment </span>=<span style="color: #000000;"> VerticalAlignment.Center;
        IFont cellfont </span>=<span style="color: #000000;"> workbook.CreateFont();
        cellfont.FontHeightInPoints </span>= <span style="color: #800080;">10</span><span style="color: #000000;">;
        cellStyle.SetFont(cellfont);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">cellStyle.WrapText = true;

        </span><span style="color: #008000;">//</span><span style="color: #008000;">填充数据行  </span>
        IRow row = <span style="color: #0000ff;">null</span><span style="color: #000000;">;
        </span><span style="color: #0000ff;">int</span> rowsNum = headRowsNum + <span style="color: #800080;">1</span>;  <span style="color: #008000;">//</span><span style="color: #008000;">行号，从第2行开始 </span>
        <span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> i = <span style="color: #800080;">0</span>; i &lt; data.Rows.Count; i++<span style="color: #000000;">)
        {
            </span><span style="color: #008000;">//</span><span style="color: #008000;">写入字段值  </span>
            row =<span style="color: #000000;"> sheet.CreateRow(rowsNum);
            row.HeightInPoints </span>= <span style="color: #800080;">18</span><span style="color: #000000;">;
            </span><span style="color: #0000ff;">for</span> (<span style="color: #0000ff;">int</span> j = <span style="color: #800080;">0</span>; j &lt; colsname.Count; j++<span style="color: #000000;">)
            {
                row.CreateCell(j, CellType.String).SetCellValue(data.Rows[i][j].ToString());
                row.GetCell(j).CellStyle </span>=<span style="color: #000000;"> cellStyle;
                </span><span style="color: #008000;">//</span><span style="color: #008000;">sheet.AutoSizeColumn(j);</span>

                }
            rowsNum</span>++<span style="color: #000000;">;
        }

        </span><span style="color: #008000;">//</span><span style="color: #008000;">for (int i = 0; i &lt; colsname.Count; i++)
        </span><span style="color: #008000;">//</span><span style="color: #008000;">{
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    int columnWidth = sheet.GetColumnWidth(i) / 256;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    for (int j = 0; j &lt; data.Rows.Count; j++)
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    {
        </span><span style="color: #008000;">//</span><span style="color: #008000;">        ICell currentCell = sheet.GetRow(j).GetCell(i);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">        int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">        if (columnWidth &lt; length)
        </span><span style="color: #008000;">//</span><span style="color: #008000;">        {
        </span><span style="color: #008000;">//</span><span style="color: #008000;">            columnWidth = length;
        </span><span style="color: #008000;">//</span><span style="color: #008000;">        }
        </span><span style="color: #008000;">//</span><span style="color: #008000;">    }

        </span><span style="color: #008000;">//</span><span style="color: #008000;">    sheet.SetColumnWidth(i, (columnWidth + 1) * 256);
        </span><span style="color: #008000;">//</span><span style="color: #008000;">}</span>

        <span style="color: #0000ff;">string</span> strPhysicsPath = <span style="color: #0000ff;">string</span><span style="color: #000000;">.Empty;
        </span><span style="color: #0000ff;">if</span> (!<span style="color: #0000ff;">string</span><span style="color: #000000;">.IsNullOrWhiteSpace(filePath))
        {
            strPhysicsPath </span>=<span style="color: #000000;"> filePath;
        }
        </span><span style="color: #0000ff;">else</span><span style="color: #000000;">
        {
            strPhysicsPath </span>= HttpContext.Current.Server.MapPath(<span style="color: #800000;">"</span><span style="color: #800000;">~/</span><span style="color: #800000;">"</span> + <span style="color: #800000;">"</span><span style="color: #800000;">Temp/Export</span><span style="color: #800000;">"</span><span style="color: #000000;">);
        }

        </span><span style="color: #0000ff;">if</span> (!<span style="color: #000000;">Directory.Exists(strPhysicsPath))
        {
            Directory.CreateDirectory(strPhysicsPath);
        }
        </span><span style="color: #0000ff;">string</span> tempUrl = <span style="color: #800000;">"</span><span style="color: #800000;">/</span><span style="color: #800000;">"</span> + DateTime.Today.ToString(<span style="color: #800000;">"</span><span style="color: #800000;">MMdd-</span><span style="color: #800000;">"</span>) + filename + <span style="color: #800000;">"</span><span style="color: #800000;">.xls</span><span style="color: #800000;">"</span><span style="color: #000000;">;
        strPhysicsPath </span>= strPhysicsPath.TrimEnd(<span style="color: #800000;">'</span><span style="color: #800000;">\\</span><span style="color: #800000;">'</span>) +<span style="color: #000000;"> tempUrl;

        </span><span style="color: #0000ff;">using</span> (MemoryStream ms = <span style="color: #0000ff;">new</span><span style="color: #000000;"> MemoryStream())
        {
            ms.Flush();
            ms.Position </span>= <span style="color: #800080;">0</span><span style="color: #000000;">;
            workbook.Write(ms);
            </span><span style="color: #0000ff;">using</span> (FileStream fs = <span style="color: #0000ff;">new</span><span style="color: #000000;"> FileStream(strPhysicsPath, FileMode.Create, FileAccess.Write))
            {
                </span><span style="color: #0000ff;">byte</span>[] fData =<span style="color: #000000;"> ms.ToArray();
                fs.Write(fData, </span><span style="color: #800080;">0</span><span style="color: #000000;">, fData.Length);
                fs.Flush();
            }
            </span><span style="color: #008000;">//</span><span style="color: #008000;">LogHelper.WriteExportLog(filename);
            </span><span style="color: #008000;">//</span><span style="color: #008000;">return Config.AccPath.Replace("~", "") + "Temp/Export" + tempUrl;</span>


                LogHelper.Info(filename);
                return strPhysicsPath;
            }
        }

View Code

 //从第二行开始循环，和上一行进行判断，如果相同，则合并
        for (int i = 1; i < dt.Rows.Count; i++)
        {
            row = sheet.CreateRow(i + 1);
            for (int j = 0; j < dt.Columns.Count; j++)
            {
                string cellText = dt.Rows[i][j].ToString();
                row.CreateCell(j).SetCellValue(cellText);
                string temp = dt.Rows[i - 1][j].ToString();
                //temp上一行数据，一定要判断是否为空！！！j< dt.Columns.Count-7，不需要合并的列，也可以改成 && (j!=5 || j!=7)
                if (!string.IsNullOrEmpty(temp) && cellText== temp && j< dt.Columns.Count-7)
                {
                    //本行和上一行合并,为什么是 i和i+1，这里是从第几行开始合并，到第几行结束，i是在dt里的行数，而在表里，因为有表头，所以必须要加1
                    CellRangeAddress region = new CellRangeAddress(i, i+1, j, j);
                    sheet.AddMergedRegion(region);
                }
            }
        }
        style.Alignment = HorizontalAlignment.Center;
        style.VerticalAlignment = VerticalAlignment.Center;
        MemoryStream ms = new MemoryStream();
        book.Write(ms);
        System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename={0}.xls", HttpUtility.UrlEncode(title + "_" + DateTime.Now.ToString("yyyy-MM-dd"), System.Text.Encoding.UTF8)));
        System.Web.HttpContext.Current.Response.BinaryWrite(ms.ToArray());
        System.Web.HttpContext.Current.Response.End();
        book = null;
        ms.Close();
        ms.Dispose();

View Code

合并单元格设置样式输入公式

NPOI的下载地址：http://npoi.codeplex.com/

NPOI的使用教程（中文）：http://tonyqus.sinaapp.com/

首先建立一个空白的工作簿用作测试，并在其中建立空白工作表，在表中建立空白行，在行中建立单元格，并填入内容：
　
//建立空白工作簿
IWorkbook workbook = new HSSFWorkbook();
//在工作簿中：建立空白工作表
ISheet sheet = workbook.CreateSheet();
//在工作表中：建立行，参数为行号，从0计
IRow row = sheet.CreateRow(0);
//在行中：建立单元格，参数为列号，从0计
ICell cell = row.CreateCell(0);
//设置单元格内容
cell.SetCellValue("实习鉴定表");
设置单元格样式：设置单元格样式时需要注意，务必创建一个新的样式对象进行设置，否则会将工作表所有单元格的样式一同设置，它们应该共享的是一个样式对象：

　

ICellStyle style 
= workbook.CreateCellStyle();

//设置单元格的样式：水平对齐居中

style.Alignment = HorizontalAlignment.CENTER;

//新建一个字体样式对象

IFont font = workbook.CreateFont();

//设置字体加粗样式

font.Boldweight = short.MaxValue;

//使用SetFont方法将字体样式添加到单元格样式中 

style.SetFont(font);

//将新的样式赋给单元格

cell.CellStyle = style;

码农公寓

相关文章