将图像导出到Excel-Java 学习之路

我使用以下代码将带有图像的数据导出到 Excel .

Code

protected void ExportToExcel(object sender, EventArgs e)
{
    //Get the data from database into datatable
    string strQuery = "select CustomerID, ContactName, City, PostalCode, display_picture" +
        " from customers";
    SqlCommand cmd = new SqlCommand(strQuery);
    DataTable dt = GetData(cmd);

    //Create a dummy GridView
    GridView GridView1 = new GridView();
    GridView1.AllowPaging = false;
    GridView1.DataSource = dt;
    GridView1.DataBind();

    Response.Clear();
    Response.Buffer = true;
    Response.AddHeader("content-disposition",
     "attachment;filename=DataTable.xls");
    Response.Charset = "";
    Response.ContentType = "application/vnd.ms-excel";
    StringWriter sw = new StringWriter();
    HtmlTextWriter hw = new HtmlTextWriter(sw);

    for (int i = 0; i  .textmode { mso-number-format:\@; } ";
    Response.Write(style);
    Response.Output.Write(sw.ToString());
    Response.Flush();
    Response.End();
}

Excel 正在正确下载 . 但问题是当我在 Excel 中过滤数据时 . Excel 中的图像位于 Move but don't size with cells 属性中 . 如何使用属性制作图像 Move and size with cells ？

2 回答

您的代码根本不会创建Excel文件，它会创建一个HTML表格，并使用旧的二进制Excel格式（xls）的伪内容类型发送它 . Excel不会被欺骗，它会检测到这是一个HTML表并尝试使用默认设置导入它 . 这可能由于多种原因而中断 .

使用像EPPlus这样的库创建一个真正的Excel文件要容易得多，也更便宜 . 对于初学者，您可以直接从DataTable填充工作表：

protected void ExportToExcel(object sender, EventArgs e)
{
    ///...
    DataTable dt = GetData(cmd);

    using (ExcelPackage pck = new ExcelPackage())
    {
        //Create the worksheet
        var ws = pck.Workbook.Worksheets.Add("Demo");
        //Load the datatable into the sheet, starting from cell A1. 
        //Print the column names on row 1
        ws.Cells["A1"].LoadFromDataTable(dt, true);
        //That's it!

        //Write it back to the client
        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
        Response.AddHeader("content-disposition", "attachment;  filename=ExcelDemo.xlsx");
        Response.BinaryWrite(pck.GetAsByteArray());
    }
}

您可以使用 Drawings.AddPicture 方法添加图片：

ExcelPicture pic = ws.Drawings.AddPicture("pic1", new FileInfo("PathToMyImage.png"));

结果是一个 xlsx 文件，它是一个压缩的XML文件包 . 这意味着它实际上小于经常生成的HTML表或CSV文件，而不是实际的Excel文件 .

回复于 2024-04-20T17:36:30+08:00

EasyXLS是一个库，它还可以使用图像导出xlsx和xls文件 .

//Create a workbook
ExcelDocument workbook = new ExcelDocument();

//Add a worksheet
ExcelWorksheet worksheet = new ExcelWorksheet("Gridview");
workbook.easy_addWorksheet(worksheet);

//Add the gridview to the worksheet
DataSet dataSet = new DataSet();
dataSet.Tables.Add((DataTable)GridView1.DataSource);
worksheet.easy_insertDataSet(dataSet);

//Add an image
worksheet.easy_addImage("image.jpg", "A10");

//Exporting gridview with image
workbook.easy_WriteXLSXFile("DataTable.xlsx");

如果从数据库加载图像字节，则需要在计算机上本地临时保存图像 .

您还可以查看how to export gridview to excel以查看有关格式化数据的更多信息 .

回复于 2024-04-20T17:36:30+08:00

将图像导出到Excel

2 回答

相关问题