create excel sheet with dataset

using

//

 

{

 

Excel = Microsoft.Office.Interop.Excel; public string ExportDataSet2( DataSet dataSet, DataTable myTable, string userType) try

{

 

 

object missing = Type .Missing; //Create Excel

excel =

excel.DisplayAlerts =

workbooks = excel.Workbooks;

workbook = workbooks.Add(missing);

 

Excel.Range cel;

excel.Visible =

 

{

Excel.Sheets worksheets = (Excel.Sheets)excel.Worksheets;

Excel.Worksheet worksheet = (Excel.Worksheet)worksheets.Add(

 

worksheet.Name = dataTable.TableName;

 

 

 

new Excel.ApplicationClass(); false ; this .dataSet = dataSet; false ; foreach ( DataTable dataTable in dataSet.Tables) Type .Missing, Type .Missing, Type .Missing, Type .Missing); int columnsCount = dataTable.Columns.Count; int rowsCount = dataTable.Rows.Count; //Add Data Header

 

{

 

{

worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnsCount]);

cel.Font.Bold =

}

 

 

if (userType == "inter" ) for ( int i = 0; i < 8; i++) true ; DataRow dr; //Add Data

 

{

dr = dataTable.Rows[j];

 

{

worksheet.Cells[j + 2, k + 1] = dr[k].ToString().Trim();

}

}

}

 

{

 

{

worksheet.Cells[1, i + 1] = dataTable.Columns[i].ColumnName;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnsCount]);

cel.Font.Bold =

}

 

 

for ( int j = 0; j < rowsCount; j++) for ( int k = 0; k < 8; k++) else if (userType == "exter" ) for ( int i = 0; i < 5; i++) true ; DataRow dr; //Add Data

 

{

dr = dataTable.Rows[j];

 

{

worksheet.Cells[j + 2, k + 1] = dr[k].ToString().Trim();

}

}

}

 

{

 

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[rowNum+1, 1], worksheet.Cells[rowNum+1, 3]);

cel.Interior.ColorIndex = 3;

cel.ColumnWidth = 25;

}

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[rowNum + 1, 4], worksheet.Cells[rowNum + 1, 4]);

cel.Interior.ColorIndex = 3;

cel.ColumnWidth = 12;

}

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[rowNum + 1, 5], worksheet.Cells[rowNum + 1, 5]);

cel.Interior.ColorIndex = 3;

cel.ColumnWidth = 12;

}

 

{

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[rowNum + 1, 6], worksheet.Cells[rowNum + 1, 6]);

cel.Interior.ColorIndex = 3;

cel.ColumnWidth = 12;

}

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[rowNum + 1, 8], worksheet.Cells[rowNum + 1, 8]);

cel.ColumnWidth = 14;

}

}

}

for ( int j = 0; j < rowsCount; j++) for ( int k = 0; k < 5; k++) foreach ( DataRow drError in myTable.Rows) int rowNum = Convert .ToInt32(drError[ "ExcelRowIndex" ].ToString()); if ( Convert .ToInt32(drError[ "AML" ].ToString()) == 1) if ( Convert .ToInt32(drError[ "DocURL" ].ToString()) == 1) if ( Convert .ToInt32(drError[ "DocType" ].ToString()) == 1) if (userType == "inter" ) if ( Convert .ToInt32(drError[ "DocStatus" ].ToString()) == 1)

#region

 

Delete unwanted sheet (win 2003) try

{

((Excel.Worksheet)workbook.Worksheets[

}

 

"Sheet1" ]).Delete(); catch

{

}

 

try

{

((Excel.Worksheet)workbook.Worksheets[

}

 

"Sheet2" ]).Delete(); catch

{

}

 

try

{

((Excel.Worksheet)workbook.Worksheets[

}

 

"Sheet3" ]).Delete(); catch

{

}

#endregion

 

 

 

 

workbook.SaveAs(_path, Excel.XlFileFormat.xlWorkbookNormal, missing, missing,

 

 

}

 

{

 

}

 

string bufferFolder = @"BulkUploadFiles/" ; string _path = string .Format( "{0}{1}.xls" , Server.MapPath(bufferFolder), "ImportError" ); false , false , Excel.XlSaveAsAccessMode.xlNoChange, missing, missing, missing, missing, missing); return _path; catch ( Exception ee) throw ee; finally

{

excel.Workbooks.Close();

excel.Quit();

workbook =

workbooks =

excel =

 

 

 

 

}

}

 

set excel style****

private

{

Excel.Range cel;

 

 

void SetExcelStyle(Excel.ApplicationClass excel)int i = 1;int j = 1; // the index of [PBT Gap?] column

 

{

 

 

{

 

i = 1;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 27;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 27;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 12;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 20;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 60;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 12;

i++;

 

foreach (Excel.Worksheet worksheet in excel.Worksheets)switch (worksheet.Name)case "PartNumber gaps":// [PBT Gap?] column

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 11;

j = i;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 13;

 

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[

cel.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

cel.Borders.ColorIndex =

cel.AutoFilter(1,

if (this.dataSet.Tables["PartNumber gaps"].Rows.Count > 0)this.dataSet.Tables["PartNumber gaps"].Rows.Count+1, i]); ExcelColor.BorderColor;Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

 

try

{

cel.AutoFilter(j,

}

 

}

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, i]);

cel.Font.Bold =

cel.Font.ColorIndex =

cel.Interior.ColorIndex =

 

 

i = 1;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 27;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 27;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 43;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 25;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 28;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 12;

i++;

 

"Yes", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);catch { }true;ExcelColor.White;ExcelColor.HeaderColor;break;case "AML gaps":// [PBT Gap?] column

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 11;

j = i;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 13;

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[

cel.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

cel.Borders.ColorIndex =

cel.AutoFilter(1,

if (this.dataSet.Tables["AML gaps"].Rows.Count > 0)this.dataSet.Tables["AML gaps"].Rows.Count + 1, i]);ExcelColor.BorderColor;Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);

 

try

{

cel.AutoFilter(j,

}

 

}

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, i]);

cel.Font.Bold =

cel.Font.ColorIndex =

cel.Interior.ColorIndex =

 

 

i = 1;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 27;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 27;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 25;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 28;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 43;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 11;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 12;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 12;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 17;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 45;

i++;

 

"Yes", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);catch { }true;ExcelColor.White;ExcelColor.HeaderColor;break;case "Compliance gaps":// [PBT Gap?] column

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 11;

j = i;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 20;

i++;

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, i], worksheet.Cells[1, i]);

cel.ColumnWidth = 12;

 

{

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[

cel.Borders.LineStyle = Excel.XlLineStyle.xlContinuous;

cel.Borders.ColorIndex =

cel.AutoFilter(1,

 

if (this.dataSet.Tables["Compliance gaps"].Rows.Count > 0)this.dataSet.Tables["Compliance gaps"].Rows.Count + 1, i]);ExcelColor.BorderColor;Type.Missing, Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);try

{

cel.AutoFilter(j,

}

 

}

cel = (Excel.Range)worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, i]);

cel.Font.Bold =

cel.Font.ColorIndex =

cel.Interior.ColorIndex =

 

 

 

}

}

}

 

"Yes", Excel.XlAutoFilterOperator.xlAnd, Type.Missing, true);catch { }true;ExcelColor.White;ExcelColor.HeaderColor;break;default:break;  null ; null ; null ; if (workbook != null ) Marshal .ReleaseComObject(workbook); if (workbooks != null ) Marshal .ReleaseComObject(workbooks); if (excel != null ) Marshal .ReleaseComObject(excel); GC .Collect();//