In this example we explain that how to create excel
file using Microsoft office interop in asp.net using C#. or how to create Excel
file dynamically using C# in asp.net.so here we demonstrate that how to create
excel file with formatting the header and colour of the cell in asp.net using C#.
or how to create Excel dynamically from DataTable with colour and also set the
Number Decimal format for excel cell and Date Time Formatting in Cell when
export excel in asp.net.
So here we demonstrate that how to Formatting excel
cells (currency) in asp.net using C#. here we generally passed the Data Table to
a Button click event and generate the Excel file with formatting from Data
table using C#.
protected void btnexport_Click(object sender, EventArgs
e)
{
//Step 1 :
Add reference of Microsoft.Office.Interop.l_objExcel dll into project .
Microsoft.Office.Interop.Excel.Application l_objExcel;
Microsoft.Office.Interop.Excel.Workbook l_objExcelworkBook;
Microsoft.Office.Interop.Excel.Worksheet l_objExcelSheet;
try
{
DataSet
dss = HttpContext.Current.Session["ProjectData"] as
DataSet;
System.Data.DataTable p_dtData = dss.Tables[0];
// Create
the object of l_objExcel application .
l_objExcel = new Microsoft.Office.Interop.Excel.Application();
// Create
workbook .
l_objExcelworkBook =
l_objExcel.Workbooks.Add(Type.Missing);
// Get
active sheet from workbook
l_objExcelSheet =
(Microsoft.Office.Interop.Excel.Worksheet)l_objExcelworkBook.ActiveSheet;
l_objExcelSheet.Name = "Project Report";
// For
showing alert message of overwritting of existing file .
l_objExcel.DisplayAlerts = false;
for
(int rowIndex = 0; rowIndex <
p_dtData.Rows.Count; rowIndex++)
{
for
(int colIndex = 0; colIndex <
p_dtData.Columns.Count; colIndex++)
{
if
(rowIndex == 0)
{
// Write column name into Excel cell .
Microsoft.Office.Interop.Excel.Range oRange;
oRange =
(Microsoft.Office.Interop.Excel.Range)l_objExcelSheet.Cells[rowIndex
+ 1, colIndex + 1];
if (p_dtData.Columns[colIndex].ColumnName == "Name")
l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = "Project";
else if
(p_dtData.Columns[colIndex].ColumnName == "Customer")
l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = "Company";
else if
(p_dtData.Columns[colIndex].ColumnName == "EstProjectFinishDate")
l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = "Est. Finish Date";
else if
(p_dtData.Columns[colIndex].ColumnName == "OwnerIdName")
l_objExcelSheet.Cells[rowIndex + 1, colIndex + 1] = "Sales";
else
l_objExcelSheet.Cells[rowIndex
+ 1, colIndex + 1] = p_dtData.Columns[colIndex].ColumnName;
//oRange.Font.Color =
System.Drawing.ColorTranslator.FromHtml("#ffffff");
oRange.Interior.Color =
System.Drawing.ColorTranslator.FromHtml("#333333");
oRange.Font.Color =
System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.White);
// oRange.Interior.Color = System.Drawing.Color.Red;
oRange.HorizontalAlignment =
Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;
}
Microsoft.Office.Interop.Excel.Range
oRange1;
if
(rowIndex != 0)
if (rowIndex % 2 == 0)
{
oRange1 =
(Microsoft.Office.Interop.Excel.Range)l_objExcelSheet.Cells[rowIndex
+ 1, colIndex + 1];
oRange1.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#E6E3D8"); //
GridView1.AlternatingRowStyle.BackColor;
// l_objExcelSheet.Cells.Font.Color =
System.Drawing.ColorTranslator.FromHtml("#333333");
}
else
{
oRange1 =
(Microsoft.Office.Interop.Excel.Range)l_objExcelSheet.Cells[rowIndex
+ 1, colIndex + 1];
oRange1.Interior.Color = System.Drawing.ColorTranslator.FromHtml("#EAEAEA");
// l_objExcelSheet.Cells.Font.Color =
System.Drawing.ColorTranslator.FromHtml("#333333");
}
Microsoft.Office.Interop.Excel.Range
oRange22;
oRange22 = (Microsoft.Office.Interop.Excel.Range)l_objExcelSheet.Cells[rowIndex + 1, colIndex
+ 1];
Microsoft.Office.Interop.Excel.Range
oRange2;
oRange2 =
(Microsoft.Office.Interop.Excel.Range)l_objExcelSheet.Cells[rowIndex
+ 2, colIndex + 1];
oRange2.Font.Color =
System.Drawing.ColorTranslator.FromHtml("#333333");
oRange2.BorderAround(
Type.Missing,
Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,
(Microsoft.Office.Interop.Excel.XlColorIndex)1,
Type.Missing);
if
(colIndex == 0)
{
oRange2.ColumnWidth =
80;
l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] =
p_dtData.Rows[rowIndex][colIndex];
}
else
if (colIndex == 1)
{
oRange2.ColumnWidth =
40;
l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] =
p_dtData.Rows[rowIndex][colIndex];
}
else
if (colIndex == 2)
{
oRange2.ColumnWidth =
10;
l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] =
p_dtData.Rows[rowIndex][colIndex];
}
else
if (colIndex == 3)
{
oRange2.ColumnWidth =
10;
l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] =
p_dtData.Rows[rowIndex][colIndex];
}
else
if (colIndex == 4)
{
oRange2.ColumnWidth =
20;
NumberFormatInfo formatInfo = new
CultureInfo("nl-NL",
false).NumberFormat;
// string s =
p_dtData.Columns[4].DataType.ToString();
//l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1],
l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat = "#,###.00
€";
l_objExcelSheet.Cells[rowIndex
+ 2, colIndex + 1] = Convert.ToDecimal(p_dtData.Rows[rowIndex][colIndex]).ToString("C", formatInfo);
}
else
if (colIndex == 5)
{
oRange2.ColumnWidth =
15;
l_objExcel.Range[l_objExcel.Cells[rowIndex + 2, colIndex + 1],
l_objExcel.Cells[rowIndex + 2, colIndex + 1]].NumberFormat = "yyyy-mm-d";
// = "mm-d-yy h:mm:ss AM/PM";
l_objExcelSheet.Cells[rowIndex
+ 2, colIndex + 1] = p_dtData.Rows[rowIndex][colIndex];
}
else
if (colIndex == 6)
{
oRange2.ColumnWidth =
30;
l_objExcelSheet.Cells[rowIndex + 2, colIndex + 1] =
p_dtData.Rows[rowIndex][colIndex];
}
oRange2.HorizontalAlignment
= Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignLeft;
oRange2.VerticalAlignment =
Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignTop;
oRange2.WrapText = true;
}
}
l_objExcelSheet.Name =
"ActivityReport.xlxs";
l_objExcel.Visible = true;
l_objExcelworkBook.SaveAs(Server.MapPath("~/Admin/ProjectReport.xlxs"));
l_objExcelworkBook.Close();
l_objExcel.Quit();
}
catch (Exception ex)
{
cf.LogError(ex);
}
finally
{
l_objExcelSheet = null;
l_objExcelworkBook = null;
}
}
0 comments:
Post a Comment