public void CreateWorkbook(string FileName)
{
//Create workbook
IWorkbook book = NativeExcel.Factory.CreateWorkbook();
//Add sheet
IWorksheet sheet = book.Worksheets.Add();
sheet.Name = "John Parks Insurance Report";
sheet.PageSetup.CenterHeader = "MONTH 2014";
//Add header
IRange range = sheet.Range["A1:X1"];
range.Font.Name = "Calibri";
range.Font.Size = 12;
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells.Font.Name = "Calibri";
sheet.Cells.Font.Size = 12;
sheet.Cells[1, 1].Value = "Name";
sheet.Cells[1, 2].Value = "Address";
sheet.Cells[1, 3].Value = "City";
sheet.Cells[1, 4].Value = "State";
sheet.Cells[1, 5].Value = "Zip";
sheet.Cells[1, 6].Value = "Email";
sheet.Cells[1, 7].Value = "Evidence Number";
sheet.Cells[1, 8].Value = "Enrollment date";
sheet.Cells[1, 9].Value = "Company Affiliation";
sheet.Cells[1, 10].Value = "Company ID";
sheet.Cells[1, 11].Value = "Payment ($)";
sheet.Cells[1, 12].Value = "JAPCO ($)";
sheet.Cells[1, 13].Value = "Gross Prem ($)";
sheet.Cells[1, 14].Value = "Comm ($)";
sheet.Cells[1, 15].Value = "Payable ($)";
sheet.Cells[1, 16].Value = "Tax ($)";
sheet.Cells[1, 17].Value = "Payable with Tax ($)";
sheet.Cells[1, 18].Value = "Slush ($)";
sheet.Cells[1, 19].Value = "Slush-Fee ($)";
sheet.Cells[1, 20].Value = "IDS Payable ($)";
//New Code
sheet.Cells[1, 21].Value = "AD&D ($)";
sheet.Cells[1, 22].Value = "ADD COMM ($)";
sheet.Cells[1, 23].Value = "AD&D NET PAYABLE ($)";
sheet.Cells[1, 24].Value = "NET Payable IDS ($)";
//sheet.Cells[10, 7].Value = "COMM ($)";
//sheet.Cells[10, 8].Value = "PAYABLE ($)";
//sheet.Cells[10, 9].Value = "SLUSH ($)";
//sheet.Cells[10, 10].Value = "PROPAY ($)";
//sheet.Cells[10, 11].Value = "PROPAY ($)";
sheet.Cells["A1:X1"].Borders.Color = System.Drawing.Color.Black;
sheet.Cells["A1:X1"].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells["A1:X1"].Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);
DataSet dsReportData = new DataSet();
ManageUserSVC.ManageUserClient Client = new ManageUserSVC.ManageUserClient();
DateTime dtFrom = DateTime.ParseExact(txtfrom.Text, "MM/dd/yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-US"));
DateTime dtTo = DateTime.ParseExact(txtto.Text, "MM/dd/yyyy", System.Globalization.CultureInfo.GetCultureInfo("en-US"));
dsReportData = Client.GetReport_Data(dtFrom, dtTo);
//John Parks Insurance Report
double JOHNPARKINSURANCE = Convert.ToDouble(System.Configuration.ConfigurationManager.AppSettings["JOHNPARKINSURANCE"].ToString());
double TAX = Convert.ToDouble(System.Configuration.ConfigurationManager.AppSettings["TAX"].ToString());
double ADND = Convert.ToDouble(System.Configuration.ConfigurationManager.AppSettings["ADND"].ToString());
double ADDCOMM = Convert.ToDouble(System.Configuration.ConfigurationManager.AppSettings["ADDCOMM"].ToString());
if (dsReportData != null && dsReportData.Tables[0].Rows.Count > 0)
{
sheet.Cells["H2:H" + dsReportData.Tables[0].Rows.Count + 2].NumberFormat = "#,##0.00";
for (int i = 0; i < dsReportData.Tables[0].Rows.Count; i++)
{
for (int j = 1; j <= dsReportData.Tables[0].Columns.Count - 1; j++)
{
sheet.Cells[i + 2, j].Value = dsReportData.Tables[0].Rows[i][j - 1].ToString();
sheet.Cells[i + 2, 11].Value = Convert.ToDouble(Convert.ToString(dsReportData.Tables[0].Rows[i]["Payment"]));
double calc;
calc = Convert.ToDouble(sheet.Cells[i + 2, 11].Value) / 100;
sheet.Cells[i + 2, 11].Value = calc;
sheet.Cells[i + 2, 11].Font.Bold = true;
sheet.Cells[i + 2, 12].Value = JOHNPARKINSURANCE;
sheet.Cells[i + 2, 13].Value = "=L2-6.00";
sheet.Cells[i + 2, 14].Value = "=(M2)*0.12";
sheet.Cells[i + 2, 15].Value = "=(M2)-N2";
sheet.Cells[i + 2, 16].Value = TAX;
sheet.Cells[i + 2, 17].Value = "=O2+P2";
sheet.Cells[i + 2, 18].Value = "=6-P2";
//change
sheet.Cells[i + 2, 19].Value = "=R2-2.34";
sheet.Cells[i + 2, 20].Value = "=K2-L2";
sheet.Cells[i + 2, 21].Value = ADND;
sheet.Cells[i + 2, 22].Value = ADDCOMM;
sheet.Cells[i + 2, 23].Value = "=U2-V2";
sheet.Cells[i + 2, 24].Value = "=T2-U2";
}
}
int row = dsReportData.Tables[0].Rows.Count + 2;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 1].Value = "Total";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 1].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 11].Formula = "=SUM(K2:K" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 11].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 12].Formula = "=SUM(L2:L" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 12].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 13].Formula = "=SUM(M2:M" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 13].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 14].Formula = "=SUM(N2:N" + row + ")";
double KTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 14].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 14].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 14].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 14].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 14].Interior.Color = System.Drawing.Color.FromArgb(255, 128, 128);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 15].Formula = "=SUM(O2:O" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 15].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 16].Formula = "=SUM(P2:P" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 16].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 17].Formula = "=SUM(Q2:Q" + row + ")";
double PayTaxTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 17].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 17].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 17].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 17].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 17].Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 18].Formula = "=SUM(R2:R" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 18].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 19].Formula = "=SUM(S2:S" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 19].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 19].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 19].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 19].Interior.Color = System.Drawing.Color.FromArgb(204, 204, 255);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 20].Formula = "=SUM(T2:T" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 20].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 21].Formula = "=SUM(U2:U" + row + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 21].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 22].Formula = "=SUM(V2:V" + row + ")";
double STotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 22].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 22].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 22].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 22].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 22].Interior.Color = System.Drawing.Color.FromArgb(255, 128, 128);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 23].Formula = "=SUM(W2:W" + row + ")";
double ADNetPayTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 23].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 23].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 23].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 23].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 23].Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 24].Formula = "=SUM(X2:X" + row + ")";
double NetPayIDSTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 24].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 24].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 24].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 24].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 24].Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 10].Formula = "COMM ($)";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 10].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 10].Formula = KTotal + STotal;
double CommTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 10].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 10].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 10].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 10].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 10].Interior.Color = System.Drawing.Color.FromArgb(255, 128, 128);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 11].Formula = "PAYABLE ($)";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 11].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 11].Formula = PayTaxTotal + ADNetPayTotal + NetPayIDSTotal;
double PayablesTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 11].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 11].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 11].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 11].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 11].Interior.Color = System.Drawing.Color.FromArgb(255, 255, 0);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 12].Formula = "SLUSH ($)";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 12].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 12].Formula = "=SUM(S2:S" + row + ")";
double SlushTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 12].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 12].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 12].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 12].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 12].Interior.Color = System.Drawing.Color.FromArgb(204, 204, 255);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 13].Formula = "PROPAY ($)";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 13].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 13].Formula = "=SUM(2.34*6)";
double PropayTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 13].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 13].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 13].Borders.Color = System.Drawing.Color.Black;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 13].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 13].Interior.Color = System.Drawing.Color.FromArgb(255, 204, 0);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 14].Formula = "B & W ($)";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 9, 14].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 14].Formula = PayTaxTotal + ADNetPayTotal;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 10, 14].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 12, 13].Formula = CommTotal + PayablesTotal + SlushTotal + PropayTotal;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 12, 13].Font.Bold = true;
sheet.Cells.Autofit();
book.SaveAs(FileName);
//Attain Report Sheet
string POLICYNUMBER = "CIP116614";
sheet = book.Worksheets.Add();
sheet.Name = "Attain Report";
sheet.PageSetup.CenterHeader = "APRIL 2014 BURNS & WILCOX PAYABLE";
//Add header
range = sheet.Range["A1:L1"];
range.Font.Name = "Calibri";
range.Font.Size = 12;
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells.Font.Name = "Calibri";
sheet.Cells.Font.Size = 12;
sheet.Cells[1, 1].Value = "Policy Number";
sheet.Cells[1, 2].Value = "Name";
sheet.Cells[1, 3].Value = "Address";
sheet.Cells[1, 4].Value = "City";
sheet.Cells[1, 5].Value = "State";
sheet.Cells[1, 6].Value = "Zip";
sheet.Cells[1, 7].Value = "Email";
sheet.Cells[1, 8].Value = "Evidence Number";
sheet.Cells[1, 9].Value = "Enrollment date";
sheet.Cells[1, 10].Value = "Company Affiliation";
sheet.Cells[1, 11].Value = "Payable with Tax ($)";
sheet.Cells[1, 12].Value = "AD&D NET PAYABLE ($)";
sheet.Cells["A1:L1"].Borders.Color = System.Drawing.Color.Black;
sheet.Cells["A1:L1"].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells["A1:L1"].Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);
for (int i = 1; i <= dsReportData.Tables[0].Rows.Count; i++)
{
sheet.Cells[i + 1, 1].Value = POLICYNUMBER;
sheet.Cells[i + 1, 2].Value = "='John Parks Insurance Report'!A" + (i + 1);
sheet.Cells[i + 1, 3].Value = "='John Parks Insurance Report'!B" + (i + 1);
sheet.Cells[i + 1, 4].Value = "='John Parks Insurance Report'!C" + (i + 1);
sheet.Cells[i + 1, 5].Value = "='John Parks Insurance Report'!D" + (i + 1);
sheet.Cells[i + 1, 6].Value = "='John Parks Insurance Report'!E" + (i + 1);
sheet.Cells[i + 1, 7].Value = "='John Parks Insurance Report'!F" + (i + 1);
sheet.Cells[i + 1, 8].Value = "='John Parks Insurance Report'!G" + (i + 1);
sheet.Cells[i + 1, 9].Value = "='John Parks Insurance Report'!H" + (i + 1);
sheet.Cells[i + 1, 10].Value = "='John Parks Insurance Report'!I" + (i + 1);
sheet.Cells[i + 1, 11].Value = "='John Parks Insurance Report'!Q" + (i + 1);
sheet.Cells[i + 1, 12].Value = "='John Parks Insurance Report'!W" + (i + 1);
}
}
int row2 = dsReportData.Tables[0].Rows.Count + 2;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 1].Value = "Total";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 1].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 11].Formula = "=SUM(K2:K" + row2 + ")";
double PayWithTaxTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 11].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 11].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 12].Formula = "=SUM(L2:L" + row2 + ")";
double ADDNetPayTotal = Convert.ToDouble(sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 12].Value);
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 12].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 7, 11].Value = "Total";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 7, 11].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 7, 12].Value = PayWithTaxTotal + ADDNetPayTotal;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 7, 12].Font.Bold = true;
sheet.Cells.Autofit();
book.SaveAs(FileName);
//IDS Payble Report
//string POLICYNUMBER = "CIP116614";
sheet = book.Worksheets.Add();
sheet.Name = "IDS Payable Report";
sheet.PageSetup.CenterHeader = "MONTH 2014 IDS PAYABLE";
//Add header
range = sheet.Range["A1:J1"];
range.Font.Name = "Calibri";
range.Font.Size = 12;
range.Font.Bold = true;
range.HorizontalAlignment = XlHAlign.xlHAlignCenter;
range.VerticalAlignment = XlVAlign.xlVAlignCenter;
sheet.Cells.Font.Name = "Calibri";
sheet.Cells.Font.Size = 12;
sheet.Cells[1, 1].Value = "Name";
sheet.Cells[1, 2].Value = "Address";
sheet.Cells[1, 3].Value = "City";
sheet.Cells[1, 4].Value = "State";
sheet.Cells[1, 5].Value = "Zip";
sheet.Cells[1, 6].Value = "Email";
sheet.Cells[1, 7].Value = "Evidence Number";
sheet.Cells[1, 8].Value = "Enrollment date";
sheet.Cells[1, 9].Value = "Company Affiliation";
sheet.Cells[1, 10].Value = "NET Payable IDS ($)";
sheet.Cells["A1:J1"].Borders.Color = System.Drawing.Color.Black;
sheet.Cells["A1:J1"].Borders.LineStyle = XlLineStyle.xlContinuous;
sheet.Cells["A1:J1"].Interior.Color = System.Drawing.Color.FromArgb(192, 192, 192);
for (int i = 1; i <= dsReportData.Tables[0].Rows.Count; i++)
{
sheet.Cells[i + 1, 1].Value = "='John Parks Insurance Report'!A" + (i + 1);
sheet.Cells[i + 1, 2].Value = "='John Parks Insurance Report'!B" + (i + 1);
sheet.Cells[i + 1, 3].Value = "='John Parks Insurance Report'!C" + (i + 1);
sheet.Cells[i + 1, 4].Value = "='John Parks Insurance Report'!D" + (i + 1);
sheet.Cells[i + 1, 5].Value = "='John Parks Insurance Report'!E" + (i + 1);
sheet.Cells[i + 1, 6].Value = "='John Parks Insurance Report'!F" + (i + 1);
sheet.Cells[i + 1, 7].Value = "='John Parks Insurance Report'!G" + (i + 1);
sheet.Cells[i + 1, 8].Value = "='John Parks Insurance Report'!H" + (i + 1);
sheet.Cells[i + 1, 9].Value = "='John Parks Insurance Report'!I" + (i + 1);
sheet.Cells[i + 1, 10].Value = "='John Parks Insurance Report'!X" + (i + 1);
}
int row1 = dsReportData.Tables[0].Rows.Count + 2;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 1].Value = "Total";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 1].Font.Bold = true;
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 10].Formula = "=SUM(J2:J" + row1 + ")";
sheet.Cells[dsReportData.Tables[0].Rows.Count + 5, 10].Font.Bold = true;
sheet.Cells.Autofit();
book.SaveAs(FileName);
}