expoprt or Generate the multiple sheets in a single excel sheet from data table or data set using c# .net code
one can generate the multiple excels from the data table using following :
step:1
public System.Data.DataTable ExportToExcel(int ID)
{
System.Data.DataTable table = new System.Data.DataTable();
table.Columns.Add("Id", typeof(string));
table.Columns.Add("Name", typeof(string));
table.Columns.Add("Relation", typeof(string));
using (var dbd = new Entities())
{
var newIds= dbd.card.Where(d => d.id == ID).Select(x => x.src_no).ToList();
foreach (var ID in newIds)
{
table.Rows.Add(ID);
}
}
// table.Rows.Add(1);
return table;
}
step:2
public void MainExport(string path, string name)
{
int FPSID = Int32.Parse(name.Split('_')[0]);
Microsoft.Office.Interop.Excel.Application excel;
Microsoft.Office.Interop.Excel.Workbook worKbooK;
Microsoft.Office.Interop.Excel.Worksheet worKsheeT;
Microsoft.Office.Interop.Excel.Range celLrangE;
Microsoft.Office.Interop.Excel.Worksheet worKsheeT1;
Microsoft.Office.Interop.Excel.Worksheet worKsheeT2;
try
{
excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
excel.DisplayAlerts = false;
worKbooK = excel.Workbooks.Add(Type.Missing);
worKsheeT = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.ActiveSheet;
worKsheeT.Name = ID + " Spelling Change".ToString();
worKsheeT1 = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.Sheets["Sheet2"];
worKsheeT1.Name = "Addition";
worKsheeT2 = (Microsoft.Office.Interop.Excel.Worksheet)worKbooK.Sheets["Sheet3"];
worKsheeT2.Name = "Deletion";
// original code
worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[1, 5]].Merge();
worKsheeT.Cells[1, 1] = "Spelling Changes";
worKsheeT1.Range[worKsheeT1.Cells[1, 1], worKsheeT1.Cells[1, 5]].Merge();
worKsheeT1.Cells[1, 1] = "Addition";
worKsheeT2.Range[worKsheeT2.Cells[1, 1], worKsheeT2.Cells[1, 5]].Merge();
worKsheeT2.Cells[1, 1] = "Deletion";
// worKsheeT.Cells.Font.Size = 15;
int rowcount = 2;
System.Data.DataTable table = new System.Data.DataTable();
table = ExportToExcel(ID);
foreach (DataRow datarow in table.Rows)
{
rowcount += 1;
for (int i = 1; i <= table.Columns.Count; i++)
{
if (rowcount == 3)
{
worKsheeT.Cells[2, i] = table.Columns[i - 1].ColumnName;
worKsheeT.Cells.Font.Color = System.Drawing.Color.Black;
worKsheeT1.Cells[2, i] = table.Columns[i - 1].ColumnName;
worKsheeT1.Cells.Font.Color = System.Drawing.Color.Black;
worKsheeT2.Cells[2, 1] = table.Columns["Id"].ColumnName;
worKsheeT2.Cells[2, 2] = table.Columns["Name"].ColumnName;
worKsheeT2.Cells.Font.Color = System.Drawing.Color.Black;
}
worKsheeT.Cells[rowcount, i] = datarow[i - 1].ToString();
worKsheeT1.Cells[rowcount, i] = datarow[i - 1].ToString();
worKsheeT2.Cells[rowcount, i] = datarow[i - 1].ToString();
if (rowcount > 3)
{
if (i == table.Columns.Count)
{
if (rowcount % 2 == 0)
{
celLrangE = worKsheeT.Range[worKsheeT.Cells[rowcount, 1], worKsheeT.Cells[rowcount, table.Columns.Count]];
celLrangE = worKsheeT1.Range[worKsheeT1.Cells[rowcount, 1], worKsheeT1.Cells[rowcount, table.Columns.Count]];
celLrangE = worKsheeT2.Range[worKsheeT2.Cells[rowcount, 1], worKsheeT2.Cells[rowcount, table.Columns.Count]];
}
}
}
}
}
// original code
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[rowcount, table.Columns.Count]];
celLrangE.EntireColumn.AutoFit();
Microsoft.Office.Interop.Excel.Borders border = celLrangE.Borders;
border.LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
border.Weight = 2d;
celLrangE = worKsheeT.Range[worKsheeT.Cells[1, 1], worKsheeT.Cells[2, table.Columns.Count]];
worKbooK.SaveAs(path + "\\" + name + ".xlsx");
worKbooK.Close();
worKbooK.Close(true);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(worKbooK);
}
catch (Exception ex)
{
var error = ex.Message;
}
finally
{
GC.Collect();
worKsheeT = null;
celLrangE = null;
worKbooK = null;
}
}
0comments
Post a Comment