Pasting HTML table from clipboard to Excel workshe

Question

I have a DataTable with 7000 rows and 60 columns, and I need to save it in an Excel file.

Looping through the DataTable takes to much time, and the ClosedXml libary is raising an exception that I could not solve (for many weeks). So I have decided to create an HTML table from the DataTable, add it to the clipboard and paste it to the generic Excel file on the network.

the problem is that all the strings are written in Hebrew, and when the app is pasting the data all the strings don't encode properly, and Excel presents them as question marks.

Note: When I paste the data that the application set on the clipboard using my mouse the data is pasted like it should, without question marks. Also I have tried to use all the Enum parameters of the PasteSpecial() method without success. Please see the relevant code parts I have added.

 Public Function LoadDataTableToDivisinReport(ByVal d As DataTable, ByVal pathAndFileNameToSave As String, ByVal DbsheetNameToReplaceData As String, Optional ByVal pathToSaveImages As String = "") As Boolean
........

                Dim t As Threading.Thread
                t = New System.Threading.Thread(AddressOf createHtmlTableToClipBoard)
                t.SetApartmentState(Threading.ApartmentState.STA)
                t.Start()
                ws.Range("a1").PasteSpecial(Excel.XlPasteType.xlPasteFormats)

.........
End Function


    Private Sub createHtmlTableToClipBoard()

        Dim b As New StringBuilder
        b.Append("<table>")
        b.Append("<tr>")
        For i = 0 To dd.Columns.Count - 1
            b.Append("<th>" & dd.Columns(i).ColumnName & "</th>")

        Next
        b.Append("</tr>")



        For j = 0 To dd.Rows.Count - 1
            b.Append("<tr>")
            For i = 0 To dd.Columns.Count - 1
                b.Append("<td>")
                b.Append(dd.Rows(j).Item(i).ToString)
                b.Append("</td>")
            Next
            b.Append("</tr>")
        Next

        b.Append("</table>")

        Clipboard.SetText(b.ToString())

    End Sub

    Private dd As DataTable

UPDATE: thanks to @ClearLogic comment the issue was solved, macros in the generic workbook was the cause for that behavior. Please see the discussion below @ClearLogic marked answer.

Answer 1

I would recommend EPPlus. Its available on nuget.Very easy to use.

Here is a working Code snippet in c#. Translation to vb.net would not be difficult

using (ExcelPackage excelFile = new ExcelPackage(new FileInfo(fileName)))
{
    ExcelWorksheet ws = excelFile.Workbook.Worksheets.Add("Sheet1");
    ws.Cells["A1"].LoadFromDataTable(datatable, true);
    for (int i = 1; i <= datatable.Columns.Count; i++)
    {
        ws.Column(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left;
        ws.Column(i).AutoFit();
    }

    ws.Row(1).Style.Font.Bold = true;
    excelFile.Save();
}

Edit Vb.net Code

Using excelFile As New ExcelPackage(New FileInfo(fileName))
        Dim ws As ExcelWorksheet = excelFile.Workbook.Worksheets.Add("Sheet1")
        ws.Cells("A1").LoadFromDataTable(datatable, True)
        For i As Integer = 1 To datatable.Columns.Count
            ws.Column(i).Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.Left
            ws.Column(i).AutoFit()
        Next

        ws.Row(1).Style.Font.Bold = True
        excelFile.Save()
    End Using