class ReportGeneration
{
Excel.Application oXL = new Excel.Application();
Excel.Workbook oBook;
Excel.Worksheet oSheet, oSheet1, oSheet2;
string fileNameMerchant = null, fileNameRecycle = null;
public ReportGeneration(string fileNameMerchant, string
fileNameRecycle)
{
this.fileNameMerchant = fileNameMerchant ;
this.fileNameRecycle = fileNameRecycle ;
}
public int MerchantReportGeneration(SqlConnection con)
{
int status=0;
oBook = oXL.Workbooks.Add(Type.Missing);
oXL.Visible = false;
try
{
if (oBook.Worksheets.Count > 0)
{
oSheet = (Excel.Worksheet)oBook.Sheets[1];
oSheet.Activate();
SqlDataAdapter da = new SqlDataAdapter("select * from
table", con);
DataSet ds = new DataSet();
da.Fill(ds);
Range rg = oSheet.get_Range("A1", "B1");
rg.Select();
rg.Font.Bold = true;
rg.Font.Name = "Arial";
rg.Font.Size = 16;
rg.WrapText = true;
rg.MergeCells = true;
rg.HorizontalAlignment = Excel.Constants.xlCenter;
rg = oSheet.get_Range("B1", Type.Missing);
rg.Cells.ColumnWidth = 30;
rg.Font.Bold = true;
rg.Font.Name = "Arial";
rg.Font.Size = 10;
rg.Value2 = "columnname";
int x = 9;
int i = 0;
while (i < count)
{
string s = "A" + x.ToString();
rg = oSheet.get_Range(s.ToString(), Type.Missing);
rg.Cells.ColumnWidth = 30;
rg.Font.Bold = true;
rg.Font.Name = "Arial";
rg.Font.Size = 10;
rg.Value2 = ds.Tables[0].Rows[1].ToString();
string ss = "B" + x.ToString();
rg = oSheet.get_Range(ss.ToString(), Type.Missing);
rg.Cells.ColumnWidth = 40;
rg.HorizontalAlignment = Excel.Constants.xlRight;
rg.Value2 = "'" + ds.Tables[0].Rows[0].ToString();
x++;
}
}
fileNameMerchant = fileNameMerchant +
DateTime.Now.ToString("yyMMdd") + DateTime.Now.ToString("HHmmss");
oXL.ActiveWorkbook.SaveAs(fileNameMerchant,Excel.XlFileFormat.xlWorkbookNormal, Type.Missing, Type.Missing,
Type.Missing, Type.Missing,
Excel.XlSaveAsAccessMode.xlNoChange,
Type.Missing, Type.Missing, Type.Missing, Type.Missing,
Type.Missing);
System.Console.WriteLine("Merchant Report is archived at :
{0}", fileNameMerchant);
oXL.ActiveWorkbook.Close(true, fileNameMerchant, false);
status = 1;
}
catch (Exception ex)
{
string excString = ex.Message + " " + ex.Source + " " +
ex.TargetSite;
//ExceptionLogger.LogException(excString);
}
}
this is code for generating excel report throgh .net .steps are given below
1. add microsoft excell as reference from com .
2. using Excel add name space.
3. after selecting worksheet u can add as much valu as u want
here i am taking data from table and throu a for loop assingning data
u can take data from where u want
for opening excel
repeate step 1 and 2 , after defing variale. use worksheet.ope() method.
at ur sytem u must have excel installed.
