帶影像的ClosedXML匯出資料表

我正在嘗試使用 ClosedXML 將資料從資料庫匯出到 Excel。資料表中有一張身份證圖片，我只嘗試過匯出資料文本，我想知道如何將學生的圖片包含到Excel中。

public void ExportToExcel()
{
        SaveFileDialog sfd = new SaveFileDialog();
        sfd.Filter = "Excel files|*.xlsx";
        sfd.Title = "Save an Excel File";
        sfd.FileName = "Student List";

        cn.Open();

        cm = new SqlCommand("SELECT s.Lname, s.Fname, s.Mname, s.Gender, s.MobileNum, c.Course, s.Scholarship, s.EmailAddress, s.Address, s.District, s.ZipCode, s.Birthdate, "  
            "s.Birthplace, s.Citizenship, s.MotherName, s.MotherContact, s.FatherName, s.FatherContact, s.ZoomAcc, s.FbAcc, s.EducAtt, s.EmploymentStat, s.AssessmentResult, s.ProfilePic"  
            "FROM Student s INNER JOIN Course c ON s.CourseID = c.CourseID WHERE s.StudentID = @id", cn);
        cm.Parameters.AddWithValue("@id", lblID);

        SqlDataAdapter da = new SqlDataAdapter(cm);

        DataTable dt = new DataTable();
        da.Fill(dt);

        cn.Close();

        using (XLWorkbook wb = new XLWorkbook())
        {
            var ws = wb.Worksheets.Add(dt, "Student List");
            ws.Columns().AdjustToContents();

            if (sfd.ShowDialog() == DialogResult.OK)
            {
                sfd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);

                wb.SaveAs(sfd.FileName);
                this.Cursor = Cursors.WaitCursor;
                MyMessageBox.ShowMessage("Data successfully backed up!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
                this.Cursor = Cursors.Default;
                wb.Dispose();
            }
        }
}

有誰知道在哪里添加匯出影像的代碼？

uj5u.com熱心網友回復：

實際上 Image SQL 資料型別有點過時，但會在 c# 中轉換為 byte[]，所以這個或稍加修改的代碼就可以了

    using (XLWorkbook wb = new XLWorkbook())
    {
        var ws = wb.Worksheets.Add(dt, "Student List");
        ws.Columns().AdjustToContents();

        if (sfd.ShowDialog() == DialogResult.OK)
        {
            sfd.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
            int numRow = 1;
            foreach (DataRow row in dt.Rows)
            {
                    ws.Cell(numRow, 1).Value = (string)row["Lname"];
                    ws.Cell(numRow, 2).Value = (string)row["Fname"];
                    ws.Cell(numRow, 3).Value = (string)row["Mname"];
                    ws.Cell(numRow, 4).Value = (string)row["Gender"];
                    ws.Cell(numRow, 5).Value = (double)row["MobileNum"];
                    //[...] do with all the needed cells
                    var image = ws.AddPicture(new MemoryStream((byte[])row["ProfilePic"])) //the cast is only to be sure
                                        .MoveTo(ws.Cell(numRow, 7)) //Or the cell you want to bind the picture
                                        .Scale(0.5);
                    numRow  ;
            }
            wb.SaveAs(sfd.FileName);
            this.Cursor = Cursors.WaitCursor;
            MyMessageBox.ShowMessage("Data successfully backed up!", "", MessageBoxButtons.OK, MessageBoxIcon.Information);
            this.Cursor = Cursors.Default;
            wb.Dispose();
        }
    }

實際上，您可以添加一個資料表，只需將其添加為作業表

// Add a DataTable as a worksheet
wb.Worksheets.Add(dataTable);

但這不適用于 Images，所以我使用了一個 for 回圈來完成所有作業（并教如何訪問 Cell 的值）

標籤：C＃ 表格 关闭的xml

上一篇：自定義linq-query填充的DataGridView

下一篇：折線圖Windows表單，