ClosedXML export datatable with image-CodePudding

I am trying to export data from database to Excel using ClosedXML. There is an id picture in the datatable, I have only tried exporting data texts and I want to know how to include the of picture of the student to 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();
            }
        }
}

Does anyone know what and where to add the code for exporting image?

CodePudding user response：

Actually Image SQL datatype is a little bit old fashioned, but will translate to byte[] in c#, so this o r a slightly modified code will do the trick

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

Actually, you can Add a datatable simply adding it as worksheet

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

but this didn't work with Images, so I used a for loop to do all work (and teach how to access Cell's Values)