首页 > 代码库 > NOPI导出多个sheet,一列图片

NOPI导出多个sheet,一列图片

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

        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

 

技术分享
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;

                for (int iSheetIndex = 0; iSheetIndex < iSheetCount; iSheetIndex++)
                {
                    #region 图最大列

                    int mImageColMerage = 0;    //  图最大列
                    Dictionary<int, List<string>> dicImagePath = new Dictionary<int, List<string>>();

                    if (mColImage > -1)
                    {
                        List<int> lstImageColLen = new List<int>();

                        for (int i = iSheetIndex * 10000; i < dtSources[k].Rows.Count; i++)
                        {
                            List<string> lstImagePath = dtSources[k].Rows[i][mColImage].ToString().Trim(,).Split(new char[] { , }, StringSplitOptions.RemoveEmptyEntries).ToList();
                            dicImagePath[i] = lstImagePath;
                            lstImageColLen.Add(lstImagePath.Count());
                        }
                        mImageColMerage = lstImageColLen.Max();
                    }

                    #endregion

                    //create sheet 
                    string sheetName = string.Empty; 
                    if (iSheetCount>1)
                    {
                        sheetName = sheetname[k] + iSheetIndex;
                    }
                    else
                    {
                        sheetName = sheetname[k];
                    }
                    ISheet sheet = workbook.CreateSheet(sheetName);
                    sheet.PrintSetup.Landscape = true;     //是否横向排版
                    sheet.FitToPage = false;               //是否自适应页面
                    sheet.PrintSetup.Scale = 50;           //缩放比例

                    //填充列标题以及样式  
                    int rowsNum = 0;  //行号  
                    IRow headerRow = sheet.CreateRow(rowsNum);
                    ICellStyle headStyle = workbook.CreateCellStyle();
                    headStyle.Alignment = HorizontalAlignment.CENTER;
                    headerRow.HeightInPoints = 20;
                    headStyle.VerticalAlignment = VerticalAlignment.CENTER;
                    //换行
                    //headStyle.WrapText = true;

                    IFont font = workbook.CreateFont();
                    font.FontHeightInPoints = 13;
                    font.Boldweight = 700;
                    headStyle.SetFont(font);

                    for (int i = 0; i < lstColname[k].Count; i++)
                    {
                        if (i < mColImage)
                        {
                            //合并列前
                            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 = workbook.CreateCellStyle();
                    cellStyle.Alignment = HorizontalAlignment.CENTER;
                    cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                    IFont cellfont = workbook.CreateFont();
                    cellfont.FontHeightInPoints = 10;
                    cellStyle.SetFont(cellfont);
                    cellStyle.WrapText = true;

                    #region MyRegion

                    //填充数据行  
                    IRow dataRow = null;
                    rowsNum = 1;  //行号,从第2行开始  
                    /// patriarch 画图的顶级管理器,一个sheet只能获取一个(一定要注意这点)
                    HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
                    //填充内容
                    for (int i = iSheetIndex * 10000; i < dtSources[k].Rows.Count; i++)
                    {
                        dataRow = sheet.CreateRow(i + 1);

                        for (int j = 0; j < dtSources[k].Columns.Count; j++)
                        {
                            //sheet.SetColumnWidth(j, 5000);
                            //sheet.AutoSizeColumn(j);
                            if (j < mColImage)
                            {
                                //合并列前
                                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;
                            }

                        }
                    }

                    #endregion
                }
            }

            string strPhysicsPath = HttpContext.Current.Server.MapPath(Config.AccPath + "Temp/Export");

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

            #region  浏览器下载

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

            //    // 设置编码和附件格式  
            //    curContext.Response.ContentType = "application/vnd.ms-excel";
            //    curContext.Response.ContentEncoding = Encoding.UTF8;
            //    curContext.Response.Charset = "";
            //    curContext.Response.AppendHeader("Content-Disposition",
            //         "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8));
            //    curContext.Response.BinaryWrite(ms.GetBuffer());
            //    ms.Close();
            //    ms.Dispose();
            //    curContext.Response.End();
            //}
            #endregion
        }
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();

            string FileName = filename + ".xls";

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

                headerRow.HeightInPoints = 20;
                IFont font = workbook.CreateFont();
                font.FontHeightInPoints = 13;
                font.Boldweight = 700;
                headStyle.SetFont(font);
                for (int i = 0; i < lstname[k].Count; i++)
                {
                    headerRow.CreateCell(i, CellType.STRING).SetCellValue(lstname[k][i]);
                    headerRow.GetCell(i).CellStyle = headStyle;
                    //headerRow.Cells[i].SetCellValue(new HSSFRichTextString("\r\n"));  //强制换行
                }

                ICellStyle cellStyle = workbook.CreateCellStyle();
                cellStyle.Alignment = HorizontalAlignment.CENTER;
                cellStyle.VerticalAlignment = VerticalAlignment.CENTER;
                IFont cellfont = workbook.CreateFont();
                cellfont.FontHeightInPoints = 10;
                cellStyle.SetFont(cellfont);
                cellStyle.WrapText = true;

                //填充数据行  
                IRow dataRow = null;
                rowsNum = 1;  //行号,从第2行开始  

                //填充内容
                for (int i = 0; i < dtSources[k].Rows.Count; i++)
                {
                    dataRow = sheet.CreateRow(i + 1);

                    for (int j = 0; j < dtSources[k].Columns.Count; j++)
                    {
                        dataRow.CreateCell(j).SetCellValue(dtSources[k].Rows[i][j].ToString());
                        dataRow.GetCell(j).CellStyle = cellStyle;
                    }
                }
            }

            //保存
            string strPhysicsPath = HttpContext.Current.Server.MapPath(Config.AccPath + "Temp/Export");

            if (!Directory.Exists(strPhysicsPath))
            {
                Directory.CreateDirectory(strPhysicsPath);
            }
            string tempUrl = "/" + DateTime.Today.ToString("MMdd-") + Guid.NewGuid().ToString() + ".xls";
            strPhysicsPath = strPhysicsPath.TrimEnd(\\) + tempUrl;

            using (MemoryStream ms = new MemoryStream())
            {
                ms.Flush();
                ms.Position = 0;
                workbook.Write(ms);
                using (FileStream fs = new FileStream(strPhysicsPath, FileMode.Create, FileAccess.Write))
                {
                    byte[] fData =http://www.mamicode.com/ ms.ToArray();
                    fs.Write(fData, 0, fData.Length);
                    fs.Flush();
                }
                LogHelper.WriteExportLog(filename);
                return Config.AccPath.Replace("~", "") + "Temp/Export" + tempUrl;
            }
        }
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 = new File(filePath);
            if(!file.exists()){
                file.mkdir();
            }
            OutputStream out = new FileOutputStream(new File(fileName));
            out.write(b);18             out.close();
View Code

 

NOPI导出多个sheet,一列图片