问题描述

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

解决方案

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

解决方案二：

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

解决方案三：

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