I made a method about Export excel file from DataSet.
I have a 5 Datables in DataSet and each name is below
tables[0] - Web Application, tables[1] - Content Database, tables[2] - Site Collection,
tables[3] - Webs, tables[4] - Lists
And after the export, the result is like this
[
Click]
It works well, but I want to do like this.- reverse worksheet
Web Application, Content Database, Site Collection, Webs, Lists
My code is below, Please tell me how can I change the code.
What I have tried:
private bool ExportToExcel(DataSet ds, string fileName)
{
string saveFileName = "";
string worksheetName = string.Empty;
SaveFileDialog saveDialog1 = new SaveFileDialog();
this.Invoke(new MethodInvoker(delegate()
{
saveDialog1.DefaultExt = "xlsx";
saveDialog1.Filter = "Excel file|*.xlsx";
saveDialog1.FileName = fileName;
saveDialog1.ShowDialog();
}));
saveFileName = saveDialog1.FileName;
if (saveFileName.IndexOf(":") < 0)
return false;
Excel.Application xlApp = new Excel.Application();
if (xlApp == null)
{
MessageBox.Show("Can`t create Excel");
return false;
}
Excel.Workbooks workBooks = xlApp.Workbooks;
Excel.Workbook workBook = xlApp.Workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet workSheet = (Excel.Worksheet)workBook.Worksheets[1];
try
{
if (ds.Tables.Count > 0)
{
for (int index = 0; index < ds.Tables.Count; index++)
{
for (int i = 0; i < ds.Tables[index].Columns.Count; i++)
{
workSheet.Cells[1, i + 1] = ds.Tables[index].Columns[i].ColumnName;
}
for (int r = 0; r < ds.Tables[index].Rows.Count; r++)
{
for (int i = 0; i < ds.Tables[index].Columns.Count; i++)
{
workSheet.Cells[r + 2, i + 1] = ds.Tables[index].Rows[r][i].ToString();
}
Application.DoEvents();
}
((Excel.Range)workSheet.Rows[1, Type.Missing]).Font.Bold = true;
workSheet.Columns.EntireColumn.AutoFit();
if (index == 0)
{
workSheet.Name = "Web Application";
}
else if (index == 1)
{
workSheet.Name = "Content Database";
}
else if (index == 2)
{
workSheet.Name = "Site Collection";
}
else if (index == 3)
{
workSheet.Name = "Webs";
}
else if (index == 4)
{
workSheet.Name = "Lists";
}
else
{
workSheet.Name = ds.Tables[index].TableName;
}
if (index < ds.Tables.Count - 1)
{
workSheet = (Excel.Worksheet)workBook.Worksheets.Add();
}
}
}
else
{
MessageBox.Show("No tables returned.");
}
}
catch (Exception ex)
{
logManager.WriteLog(ex.ToString());
}
if (saveFileName != null)
{
try
{
workBook.Saved = true;
workBook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("Error, File is already Opened!\n" + ex.Message);
}
}
xlApp.Quit();
GC.Collect();
this.Invoke(new MethodInvoker(delegate()
{
pgstatusbar.MarqueeAnimationSpeed = 0;
pgstatusbar.Visible = false;
}));
MessageBox.Show("Excel file saved!", "Message", MessageBoxButtons.OK, MessageBoxIcon.Information);
return true;
}