Here’s my code:
private void CreateAddedItemsReportWithoutWkgp(Excel excel)
{
try
{
string designerFile = System.Configuration.ConfigurationSettings.AppSettings.Get(“AsposeFile”);
excel.Open(designerFile);
this.dataTbl.Reset();
this.oleDbComm = this.oleDbConn.CreateCommand();
this.oleDbComm.CommandType = CommandType.StoredProcedure;
this.oleDbComm.CommandText = “p_sel_CASH_LOG_RPT_ADDED”;
this.oleDbComm.Parameters.Add(new OleDbParameter("@BEGIN_DATE",OleDbType.VarChar,30));
this.oleDbComm.Parameters.Add(new OleDbParameter("@END_DATE", OleDbType.VarChar,30));
this.oleDbComm.Parameters.Add(new OleDbParameter("@LOG_TYPE",OleDbType.Integer));
Worksheet sheet = excel.Worksheets[“Added Items Report”];
sheet.Name = “Added Items Report1”;
Cells cells = sheet.Cells;
sheet.PageSetup.SetHeader(1, “&“Tahoma,Bold” &12 " + “Added Items Report\rAdded Items from " + txtBeginDt.Text + " to " + txtEndDt.Text + “\r” + slogTypeName);
///If the begin date equals the end date, add the hour and minutes
///so we can get the whole day.
this.txtBeginDt.Text = this.txtBeginDt.Text + " 00:00”;
this.txtEndDt.Text = this.txtEndDt.Text + " 23:59”;
oleDbComm.Parameters["@BEGIN_DATE"].Value = txtBeginDt.Text;
oleDbComm.Parameters["@END_Date"].Value = txtEndDt.Text;
oleDbComm.Parameters["@LOG_TYPE"].Value = GetLogType();
this.oleDbAdapter = new OleDbDataAdapter(this.oleDbComm);
this.oleDbAdapter.Fill(this.dataTbl);
sheet.Cells.ImportDataTable(this.dataTbl, false, 1,0);
//Remove lines for comments
for(int c = dataTbl.Rows.Count; c >= 1; --c)
{
string val = cells[c,0].StringValue.ToString();
string prevVal = cells[c -1,0].StringValue.ToString();
int z = val.CompareTo(prevVal);
switch (z)
{
case 0:
//They’re equal so remove the data
cells.ClearContents(c,0,c,14);
break;
default:
Range r = cells.CreateRange(c,0,c,20);
r.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
break;
}
}
//Create style
int styleIndex;
Aspose.Excel.Style style;
styleIndex = excel.Styles.Add();
style = excel.Styles[styleIndex];
Border borderStyle = style.Borders[BorderType.TopBorder];
borderStyle.LineStyle = CellBorderType.Thin;
borderStyle.Color = Color.Black;
Border borderStyle2 = style.Borders[BorderType.BottomBorder];
borderStyle2.LineStyle = CellBorderType.Thin;
borderStyle2.Color = Color.Black;
Border borderStyle3 = style.Borders[BorderType.LeftBorder];
borderStyle3.LineStyle = CellBorderType.Thin;
borderStyle3.Color = Color.Black;
Border borderStyle4 = style.Borders[BorderType.RightBorder];
borderStyle4.LineStyle = CellBorderType.Thin;
borderStyle4.Color = Color.Black;
style.IsTextWrapped = true;
int f = 2;
for(int i = 1; i <= this.dataTbl.Rows.Count; i ++)
{
cells[i,19].Formula = “=Q” + f + “&” " & P" + f + “& " " & R” + f + " & S" + f;
cells[i,19].Style = style;
f ++;
}
//Add line for bottom row
Range range = cells.CreateRange(dataTbl.Rows.Count + 1,0,dataTbl.Rows.Count + 1,19);
range.SetOutlineBorder(BorderType.TopBorder, CellBorderType.Thin, Color.Black);
//Auto fit rows
for(int i = 0; i < dataTbl.Rows.Count+1; i++)
{
sheet.AutoFitRow(i);
}
sheet.PageSetup.PrintTitleRows = “$11”;
///Remove the rest of the sheets
for(int i = 0; i < excel.Worksheets.Count ; i ++)
{
sheet = excel.Worksheets;
if(sheet.Name != “Added Items Report1”)
{
excel.Worksheets.RemoveAt(i);
i --;
}
}
excel.Save(“AddedItems1.xls”, SaveType.OpenInExcel, FileFormatType.Excel2000, Response);
}
catch(Exception ex)
{
throw new Exception(“Create Added Items Report”,ex);
}
}