NameSpace--
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Word;
Source Code--
private void printExcelReport(DataTable dtFarmDetail)
{
//Declare Excel variable
object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();
Excel.Worksheet xlWorkSheet = new Microsoft.Office.Interop.Excel.Worksheet();
Excel.Workbook xlWorkBook;
Excel.Range chartRange;
//Declare value
xlWorkBook = xlApp.Workbooks.Add(misValue);
int Rowcount = dtFarmDetail.Rows.Count;
int Colcount = dtFarmDetail.Columns.Count;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Write Header
for (int p = 1; p <= dtFarmDetail.Columns.Count; p++)
{
xlWorkSheet.Cells[1, p] = dtFarmDetail.Columns[p - 1].ColumnName;
}
//Assing data in array
object[,] arrFarm = new object[dtFarmDetail.Rows.Count, dtFarmDetail.Columns.Count];
for (int i = 0; i < dtFarmDetail.Rows.Count; i++)
{
for (int j = 0; j < dtFarmDetail.Columns.Count; j++)
{
string cellvalues = dtFarmDetail.Rows[i][j].ToString();
string tickcross = "";
if (j > 10)
{
if (cellvalues == "Yes")
{
tickcross = "ü";
}
if (cellvalues == "No" || cellvalues == "")
{
tickcross = "û";
}
}
else
{ tickcross = cellvalues; }
arrFarm[i, j] = tickcross;
}
}
//Write data in sheet
if (dtFarmDetail.Rows.Count > 0)
{
xlWorkSheet.Range[xlWorkSheet.Cells[2,1], xlWorkSheet.Cells[dtFarmDetail.Rows.Count, dtFarmDetail.Columns.Count]] = arrFarm;
dtFarmDetail.Rows.Clear();
}
//Color header
chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]);
chartRange.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]).Font.Bold = true;
chartRange.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]).Font.Size = 12;
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
//Set Alingment
chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[2 + Rowcount, Colcount]);
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//Assign font and size
chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]);
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Name = "Wingdings";
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Bold = true;
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Size = 12;
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//write sheet
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin;
xlWorkSheet.Cells.EntireColumn.AutoFit();
xlApp.Visible = true;
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
System.GC.Collect();
}
using Excel = Microsoft.Office.Interop.Excel;
using System.Runtime.InteropServices;
using Microsoft.Office.Interop.Word;
Source Code--
private void printExcelReport(DataTable dtFarmDetail)
{
//Declare Excel variable
object misValue = System.Reflection.Missing.Value;
Excel.Application xlApp = new Excel.Application();
Excel.Worksheet xlWorkSheet = new Microsoft.Office.Interop.Excel.Worksheet();
Excel.Workbook xlWorkBook;
Excel.Range chartRange;
//Declare value
xlWorkBook = xlApp.Workbooks.Add(misValue);
int Rowcount = dtFarmDetail.Rows.Count;
int Colcount = dtFarmDetail.Columns.Count;
xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.Add(Type.Missing, Type.Missing, Type.Missing, Type.Missing);
//Write Header
for (int p = 1; p <= dtFarmDetail.Columns.Count; p++)
{
xlWorkSheet.Cells[1, p] = dtFarmDetail.Columns[p - 1].ColumnName;
}
//Assing data in array
object[,] arrFarm = new object[dtFarmDetail.Rows.Count, dtFarmDetail.Columns.Count];
for (int i = 0; i < dtFarmDetail.Rows.Count; i++)
{
for (int j = 0; j < dtFarmDetail.Columns.Count; j++)
{
string cellvalues = dtFarmDetail.Rows[i][j].ToString();
string tickcross = "";
if (j > 10)
{
if (cellvalues == "Yes")
{
tickcross = "ü";
}
if (cellvalues == "No" || cellvalues == "")
{
tickcross = "û";
}
}
else
{ tickcross = cellvalues; }
arrFarm[i, j] = tickcross;
}
}
//Write data in sheet
if (dtFarmDetail.Rows.Count > 0)
{
xlWorkSheet.Range[xlWorkSheet.Cells[2,1], xlWorkSheet.Cells[dtFarmDetail.Rows.Count, dtFarmDetail.Columns.Count]] = arrFarm;
dtFarmDetail.Rows.Clear();
}
//Color header
chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]);
chartRange.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]).Font.Bold = true;
chartRange.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[1, Colcount]).Font.Size = 12;
chartRange.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Silver);
//Set Alingment
chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[2 + Rowcount, Colcount]);
chartRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft;
//Assign font and size
chartRange = xlWorkSheet.get_Range((object)xlWorkSheet.Cells[1, 1], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]);
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Name = "Wingdings";
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Bold = true;
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).Font.Size = 12;
chartRange.get_Range((object)xlWorkSheet.Cells[2, 12], (object)xlWorkSheet.Cells[11 + Rowcount, Colcount]).HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter;
//write sheet
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeBottom).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideHorizontal).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlInsideVertical).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeTop).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeLeft).Weight = Excel.XlBorderWeight.xlThin;
chartRange.Borders.get_Item(Excel.XlBordersIndex.xlEdgeRight).Weight = Excel.XlBorderWeight.xlThin;
xlWorkSheet.Cells.EntireColumn.AutoFit();
xlApp.Visible = true;
Marshal.ReleaseComObject(xlWorkSheet);
Marshal.ReleaseComObject(xlWorkBook);
Marshal.ReleaseComObject(xlApp);
xlWorkSheet = null;
xlWorkBook = null;
xlApp = null;
System.GC.Collect();
}
No comments:
Post a Comment