wtblao
wtblao
2016-05-26 08:47
采纳率: 100%
浏览 1.4k
已采纳

.NET 导出图片到Excel中,打开不显示(或提示受保护)问题。

public static MemoryStream TablesToExcelForXLSX(DataTable dt, DataTable reportTable, string file, int timeCount, string exportAll, string exportDatasource, string exportChart, out byte[] buf)
{
//HSSFWorkbook
//POIDocument, IWorkbook, IList, ICollection, IEnumerable, IEnumerable

            //POIXMLDocument, IWorkbook, IList<ISheet>, ICollection<ISheet>, IEnumerable<ISheet>, IEnumerable
            HSSFWorkbook xssfworkbook = new HSSFWorkbook();
            string columnName = "";
            int tmpI = 0;
            if (exportAll == "1" || exportDatasource == "1")
            {
                #region chartsource table
                if (dt != null && dt.Rows.Count != 0)
                {
                    //sheet1
                    ISheet sheet = xssfworkbook.CreateSheet("DataSource");
                    //表头
                    IRow row = sheet.CreateRow(0);
                    for (int i = 0; i < dt.Columns.Count; i++)
                    {
                        columnName = dt.Columns[i].ColumnName;
                        if (columnName.ToLower() == "linecount" || columnName.ToLower() == "sort")
                        {
                            continue;
                        }
                        if (columnName.ToLower() == "enlinename")
                        {
                            columnName = "ProductLine";
                        }
                        if (columnName.ToLower() == "thetype")
                        {
                            columnName = "Type";
                        }
                        if (i > timeCount + 3) //只显示时间跨度内的字段,加3 是除时间字段外有3字段
                        {
                            continue;
                        }
                        ICell cell = row.CreateCell(tmpI);
                        cell.SetCellValue(columnName);
                        tmpI++;
                    }

                    //数据
                    for (int i = 0; i < dt.Rows.Count; i++)
                    {
                        tmpI = 0;
                        IRow row1 = sheet.CreateRow(i + 1);
                        for (int j = 0; j < dt.Columns.Count; j++)
                        {
                            if (j == 0 || j == 3)
                            {
                                continue;//过滤每行的lineCount和sort字段
                            }
                            if (j > timeCount + 3) //只显示时间跨度内的字段,加1 是除时间字段外有1字段
                            {
                                continue;
                            }
                            ICell cell = row1.CreateCell(tmpI);
                            var cellValue = dt.Rows[i][j].ToString();
                            if (tmpI == 0)//第一列为产品线名称
                            {
                                cellValue = (cellValue.IndexOf("cn:") == -1) ? cellValue : cellValue.Substring(cellValue.IndexOf("cn:") + 3);
                            }
                            cell.SetCellValue(cellValue);
                            tmpI++;
                        }
                    }
                }
                #endregion

                #region datasource table
                if (reportTable != null && reportTable.Rows.Count != 0)
                {
                    //sheet2
                    ISheet sheet2 = xssfworkbook.CreateSheet("ReportSource");
                    //表头
                    IRow row2 = sheet2.CreateRow(0);
                    tmpI = 0;
                    for (int i = 0; i < reportTable.Columns.Count; i++)
                    {
                        columnName = reportTable.Columns[i].ColumnName;
                        if (columnName.ToLower() == "sort")
                        {
                            continue;
                        }
                        if (columnName.ToLower() == "thetype")
                        {
                            columnName = "Type";
                        }
                        ICell cell = row2.CreateCell(tmpI);
                        cell.SetCellValue(columnName);
                        tmpI++;
                    }

                    //数据
                    for (int k = 0; k < reportTable.Rows.Count; k++)
                    {
                        tmpI = 0;
                        IRow row1 = sheet2.CreateRow(k + 1);
                        for (int j = 0; j < reportTable.Columns.Count; j++)
                        {
                            if (j == 1)
                            {
                                continue;//过滤每行sort字段
                            }
                            if (j > timeCount + 1) //只显示时间跨度内的字段,加1 是除时间字段之前有一个1字段
                            {
                                continue;
                            }
                            ICell cell = row1.CreateCell(tmpI);
                            cell.SetCellValue(reportTable.Rows[k][j].ToString());
                            tmpI++;
                        }
                    }
                }
                #endregion
            }
            if (exportAll == "1" || exportChart == "1")
            {
                #region 插入图片
                string imgpath = AppDomain.CurrentDomain.BaseDirectory + "/export/sellthrough_chart.png";
                if (File.Exists(imgpath))
                {
                    //create sheet
                    //HSSFSheet hssfSheet = (HSSFSheet)xssfworkbook.CreateSheet("Chart");
                    ISheet hssfSheet = xssfworkbook.CreateSheet("Chart");
                    //hssfSheet.Protect = false;
                    // Create the drawing patriarch.  This is the top level container for all shapes. 
                    //HSSFPatriarch patriarch = (HSSFPatriarch)hssfSheet.CreateDrawingPatriarch();
                    IDrawing patriarch = hssfSheet.CreateDrawingPatriarch();

                    //add picture data to this workbook.
                    byte[] bytes = System.IO.File.ReadAllBytes(imgpath);
                    int pictureIdx = xssfworkbook.AddPicture(bytes, PictureType.PNG);

                    //add a picture
                    HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, 1023, 0, 0, 0, 15, 30);
                    //HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
                    IPicture pict = patriarch.CreatePicture(anchor, pictureIdx);
                }
                #endregion
            }
            //转为字节数组
            MemoryStream stream = new MemoryStream();

            xssfworkbook.Write(stream);
            buf = stream.ToArray();
            //var buf = stream.ToArray();
            /*
            //保存为Excel文件
            using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
            {
                fs.Write(buf, 0, buf.Length);
                fs.Flush();
            }*/
            return stream;
        }
  • 点赞
  • 写回答
  • 关注问题
  • 收藏
  • 邀请回答

4条回答 默认 最新

  • wtblao
    wtblao 2016-05-26 09:38
    已采纳

    根据别人意见,换个图片格式测试。

    点赞 评论
  • wtblao
    wtblao 2016-05-26 08:48

    string name = "sell_through_datasource.xls";
    string filename = AppDomain.CurrentDomain.BaseDirectory + name;
    byte[] buf = null;
    MemoryStream ms = CommonStruct.ExcelHelper.x2007.TablesToExcelForXLSX(dt, reportTable, filename, timeCount, exportAll, exportDatasource, exportChart,out buf);

            rs.AppendHeader("Content-Disposition", "attachment;filename=" + name);
            rs.ContentType = "application/ms-excel";
            rs.BinaryWrite(buf);
            //rs.WriteFile(filename);
            ms.Close();
            ms.Dispose();
            rs.Flush();
            rs.End();
    
    点赞 评论
  • wtblao
    wtblao 2016-05-26 08:53

    在.NET项目中做导出图片(已存在的图片)到excel中时,遇到个感觉跟奇怪的问题,为什么我本地机器测试可以正常导出和显示,使用IIS部署测试也是正常的。
    别人的服务器上测试时,前2个sheet数据(文字格式)可以看到,而第三个sheet显示不出来。
    有的情况打开excel后是不显示第三个sheet,有的打开后excel提示'受保护的视图'的信息。

    点赞 评论
  • wtblao
    wtblao 2016-05-26 08:55

    有大虾们遇到过这种情况吗 或者指点一二。 感激不尽~

    点赞 评论

相关推荐