Load the data into a single
DataSet
:
private static DataTable LoadTable(string connectionString, string query, string tableName)
{
using (var connection = new OdbcConnection(connectionString))
using (var command = new OdbcCommand(query, connection))
using (var adapter = new OdbcDataAdapter(command))
{
command.CommandTimeout = 180;
var table = new DataTable(tableName);
adapter.Fill(table);
return table;
}
}
private static DataSet LoadData()
{
var result = new DataSet();
result.Tables.Add(LoadTable(odbcConnectString1, sQury1, "A"));
result.Tables.Add(LoadTable(odbcConnectString2, sQury2, "B"));
result.Tables.Add(LoadTable(odbcConnectString3, sQury3, "C"));
return result;
}
public static void CreateDataTable()
{
DataSet ds = LoadData();
CreateExcelSheet(ds, excelPath);
}
Then change your
CreateExcelSheet
method to create a sheet for each table in the
DataSet
:
private static void AddDataToWorksheet(Excel._Worksheet sheet, DataTable table)
{
sheet.Name = table.TableName;
for (int i = 0; i < table.Columns.Count; i++)
{
sheet.Cells[1, i + 1] = table.Columns[i].ColumnName;
}
for (int i = 0; i < table.Rows.Count; i++)
{
int rowNumber = i + 2;
DataRow row = table.Rows[i];
for (int j = 0; j < table.Columns.Count; j++)
{
sheet.Cells[rowNumber, j + 1] = Convert.ToString(row[j]);
}
}
}
public static void CreateExcelSheet(DataSet ds, string excelPath)
{
object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();
Excel.Workbook xlWorkBook = xlApp.Workbooks.Add(misValue);
for (int i = 0; i < ds.Tables.Count; i++)
{
DataTable table = ds.Tables[i];
Excel._Worksheet xlWorkSheet = xlWorkBook.Sheets.Count <= i
? (Excel._Worksheet)xlWorkBook.Sheets.Add(After: xlWorkBook.Sheets[xlWorkBook.Sheets.Count])
: (Excel._Worksheet)xlWorkBook.Sheets[i + 1];
AddDataToWorksheet(xlWorkSheet, table);
Marshal.ReleaseComObject(xlWorkSheet);
}
xlWorkBook.SaveAs(excelPath);
xlWorkBook.Close();
xlApp.Quit();
Marshal.ReleaseComObject(xlWorkbook);
Marshal.ReleaseComObject(xlApp);
}