Multiple Sheets from One Data Table


#1

I’m having problems getting data to populate on more than one sheet. I’m pulling a data table that contains rows grouped by a location. When it opens in excel it only puts the information on one worksheet.

What I would like for it to do is break up the worksheets based on location.

Exp: DataTable

Row 1. Bobs House
Row 2. Mikes House
Row 3. Freds House

Aspose (Excel sheet)

File name : Homes.xls
Sheet1 (Bobs House)
Sheet2 (Mikes House)
Sheet3 (Freds House)

Any help would be appreciated.

Thanks


#2

Hi, Steven.

Maybe you can try the following code:

//dt is your DataTable

int i = 0;
while(dt.Rows.Count > 0)
{
Cells cells = excel.Worksheets[i].Cells;
cells.ImportDataTable(dt, false, firstRow, firstColumn, 1, (byte)dt.Columns.Count);

dt.Rows.RemoveAt(0);
i ++;
}


#3

Laurence -

Hope you are doing well! Is there anyway you could convert this over to VB code? I would greatly appreciate it.

Thanks in Advance -

Steven Tidwell


#4

Dear Steven,

You can convert C# code to VB code using ConvertCSharp2VB.

Dear Laurence,

Would you please provide the VB equivalent of a C# code block in any occurence? You know, C# and VB guys are almost 50/50.


#5

Hi Steven,

The equivalent VB code is:

Dim i As Integer = 0
While dt.Rows.Count > 0
Dim cells As Cells = excel.Worksheets(i).Cells
cells.ImportDataTable(dt, False, firstRow, firstColumn, 1, CType(dt.Columns.Count, Byte))

dt.Rows.RemoveAt(0)
i = i + 1
End While