jonathana jonathana - 4 months ago 56
Vb.net Question

Pasting HTML table from clipboard to Excel worksheet with VB.NET Application

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.

Answer

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