public static void InitiExcel(string[] name) { Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); Spire.Xls.Worksheet newSheet1 = workbook.CreateEmptySheet("Crashes"); workbook.Worksheets.Remove("Sheet1"); workbook.Worksheets.Remove("Sheet2"); workbook.Worksheets.Remove("Sheet3"); newSheet1.Range["A1:M1"].ColumnWidth = 17; newSheet1.Range.HorizontalAlignment = HorizontalAlignType.Center; newSheet1.Range["B6"].Value = "Date"; for (int i = 1; i <= name.Length; i++) { newSheet1.Range[Convert.ToChar('B' + i).ToString() + "6"].Value = name[i - 1]; } newSheet1.Range[Convert.ToChar('B' + (name.Length + 1)).ToString() + "6"].Value = "All Crashes"; newSheet1.Range[Convert.ToChar('B' + (name.Length + 2)).ToString() + "6"].Value = "Percent Impacted"; newSheet1.Range[Convert.ToChar('B' + (name.Length + 3)).ToString() + "6"].Value = "Crashes/OS"; newSheet1.Range[Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].Value = "OS"; newSheet1.Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].BorderInside(LineStyleType.Thin, Color.LightBlue); newSheet1.Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].BorderAround(LineStyleType.Medium, Color.LightBlue); workbook.SaveToFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx", ExcelVersion.Version2013); }
public void MergeAllSheet(GetTime gt) { Spire.Xls.Workbook wb = new Spire.Xls.Workbook(); wb.LoadFromFile(excelFilePath); Spire.Xls.Workbook destWorkbook = new Spire.Xls.Workbook(); Spire.Xls.Worksheet destSheet = destWorkbook.Worksheets[0]; wb.Worksheets["Sheet1"].AllocatedRange.Copy(destSheet.Range[1, 1]); destSheet.Range[1, 1].Text = string.Format("营盘壕煤矿{0}工作面矿压综合分析日报表", gt.getWorkFace()); int lastrow = destSheet.Range.RowCount; if (wb.Worksheets["Sheet2"] != null) { wb.Worksheets["Sheet2"].AllocatedRange.Copy(destSheet.Range[destSheet.Range.RowCount + 1, 1]); if (wb.Worksheets["Sheet3"] != null) { wb.Worksheets["Sheet3"].AllocatedRange.Copy(destSheet.Range[lastrow + 1, 11]); } for (int i = 4; i <= wb.Worksheets.Count + 6; i++) { if (wb.Worksheets["Sheet" + i.ToString()] != null) { wb.Worksheets["Sheet" + i.ToString()].AllocatedRange.Copy(destSheet.Range[destSheet.Range.RowCount + 1, 1]); } } } destWorkbook.SaveToFile(@"..\..\modelFile\" + string.Format("Excel_{0}.xls", gt.getDateToday())); }
public void MergeAllSheet(GetTime gt) { Spire.Xls.Workbook wb = new Spire.Xls.Workbook(); wb.LoadFromFile(excelFilePath); Spire.Xls.Workbook destWorkbook = new Spire.Xls.Workbook(); Spire.Xls.Worksheet destSheet = destWorkbook.Worksheets[0]; for (int i = 1; i <= wb.Worksheets.Count + 6; i++) { if (wb.Worksheets["Sheet" + i.ToString()] != null) { wb.Worksheets["Sheet" + i.ToString()].AllocatedRange.Copy(destSheet.Range[destSheet.LastRow + 3, 1]); } } destWorkbook.SaveToFile(@"..\..\modelFile\" + string.Format("Excel_{0}.xls", gt.getDateToday())); }
public static void GenerateExcel(Dictionary <string, string[]> condition) { Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); string[] name = condition.GetValueOrDefault("Name"); string arg = condition.GetValueOrDefault("Model")[0]; if (File.Exists(System.Environment.CurrentDirectory + "\\RecentData.xlsx")) { workbook.LoadFromFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx"); } else { InitiExcel(name); workbook.LoadFromFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx"); } LoadData(workbook, name, arg, condition); workbook.SaveToFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx", ExcelVersion.Version2013); GenerateChart(name); }
public static void GenerateChart(string[] name) { try { if (File.Exists(System.Environment.CurrentDirectory + "\\Trend Analysis.xlsx")) { File.Delete(System.Environment.CurrentDirectory + "\\Trend Analysis.xlsx"); } Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); Spire.Xls.Worksheet newSheet = workbook.CreateEmptySheet("Crashes"); workbook.Worksheets.Remove("Sheet1"); workbook.Worksheets.Remove("Sheet2"); workbook.Worksheets.Remove("Sheet3"); newSheet.Range["A1:M1"].ColumnWidth = 15; //newSheet.Range.HorizontalAlignment = HorizontalAlignType.Center; Spire.Xls.Workbook workbook1 = new Spire.Xls.Workbook(); workbook1.LoadFromFile(System.Environment.CurrentDirectory + "\\RecentData.xlsx"); //int lastRow = workbook1.Worksheets["Crashes"].LastRow; int lastRow = workbook1.Worksheets["Crashes"].LastRow; //workbook1.Worksheets["Crashes"].Range["B6:I6"].Copy(workbook.Worksheets["Crashes"].Range["A6:H6"]); //workbook1.Worksheets["Crashes"].Range["B" + (lastRow - 29) + ":I" + lastRow].Copy(workbook.Worksheets["Crashes"].Range["A7:H36"]); workbook1.Worksheets["Crashes"].Range["B6:" + Convert.ToChar('B' + (name.Length + 4)).ToString() + "6"].Copy(workbook.Worksheets["Crashes"].Range["A41:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + "41"]); if (lastRow < 37) { workbook1.Worksheets["Crashes"].Range["B7" + ":" + Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow].Copy(workbook.Worksheets["Crashes"].Range["A42:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + (lastRow + 25)]); } else { workbook1.Worksheets["Crashes"].Range["B" + (lastRow - 29) + ":" + Convert.ToChar('B' + (name.Length + 4)).ToString() + lastRow].Copy(workbook.Worksheets["Crashes"].Range["A42:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + "71"]); } workbook.Worksheets["Crashes"].Range["A41:" + Convert.ToChar('A' + (name.Length + 4)).ToString() + (lastRow + 25)].HorizontalAlignment = HorizontalAlignType.Center; workbook.SaveToFile(System.Environment.CurrentDirectory + "\\Trend Analysis.xlsx", ExcelVersion.Version2013); } catch (Exception) { throw; } }
////////////////////////// private void exportsum(classinfo cl1, View_class_exp vst1, V_tea_course vcourse, int poscol) { String dirsave = null; List <View_class_student> lstwei = new List <View_class_student>(); // List<View_class_student> lstall = null; saveFileDialog2.DefaultExt = ".xlsx"; saveFileDialog2.Filter = "EXCEL file|*.xlsx"; if (saveFileDialog2.ShowDialog() == DialogResult.OK) { string localFilePath = saveFileDialog2.FileName.ToString(); // dirsave = localFilePath.Substring(0, localFilePath.LastIndexOf("\\")); dirsave = localFilePath; } //////////////////////excel Spire.Xls.Workbook wb = new Spire.Xls.Workbook(); //清除默认的工作表 wb.Worksheets.Clear(); //添加一个工作表并指定表名 Worksheet sheet = wb.Worksheets.Add("score"); sheet.Range[2, 2].Text = "序号"; sheet.Range[3, 2].Text = "分值"; sheet.Range[4, 2].Text = "指标"; sheet.Range[5, 1].Text = "学号"; sheet.Range[5, 2].Text = "姓名"; int intobjective = vcourse.numobjective; String expobjective = vst1.objective; String[] strexpobj = expobjective.Split('|'); for (int i = 1; i <= intobjective + 1; i++) { if (i < intobjective + 1) { sheet.Range[4, poscol + i - 1].Text = i.ToString(); } else { sheet.Range[4, poscol + i - 1].Text = "已交"; } } int irow = 6; int icol = poscol; var questionQuery1 = from o in pp.context.View_class_student where o.classid == cl1.classid orderby o.classno select o; List <View_class_student> lstall = null; if (questionQuery1.Count <View_class_student>() > 0) { lstall = questionQuery1.ToList <View_class_student>(); } /// /// if (lstall != null) { foreach (View_class_student itvst in lstall) { sheet.Range[irow, 1].Text = itvst.studentid; sheet.Range[irow, 2].Text = itvst.name; var questionQuery2 = from o in pp.context.studreport where o.classid == cl1.classid && o.expid == vst1.expid && o.stid == itvst.studentid select o; studreport isturep = null; if (questionQuery2.Count <studreport>() > 0) { isturep = questionQuery2.First <studreport>(); String strmarks = isturep.score; String[] strmark = strmarks.Split('|'); for (int i = 1; i <= intobjective + 1; i++) { if (i < intobjective + 1) { sheet.Range[irow, poscol + i - 1].Value2 = 0; for (int j = 1; j < strexpobj.Length; j++) { if (i == int.Parse(strexpobj[j])) { sheet.Range[irow, poscol + i - 1].Value2 = int.Parse(strmark[j]); } } } // else // sheet.Range[irow, poscol + i - 1].Text = "NO"; } } else { for (int i = 1; i <= intobjective + 1; i++) { if (i < intobjective + 1) { sheet.Range[irow, poscol + i - 1].Value2 = 0; } else { sheet.Range[irow, poscol + i - 1].Text = "NO"; } } } irow = irow + 1; } //foreach (View_class_stu } /// end if( lstall !=null) /// sheet.AllocatedRange.AutoFitColumns(); wb.SaveToFile(dirsave, ExcelVersion.Version2013); MessageBox.Show("excel生成好了"); }
public void CreateExcel(string ref_code) { ////AdhyapanDB adhyapanDB = new AdhyapanDB(); StudentTestInfo studentTestScore = adhyapanDB.GetScores(ref_code); SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY"); SpreadsheetInfo.FreeLimitReached += (sender, e) => e.FreeLimitReachedAction = FreeLimitReachedAction.ContinueAsTrial; //var workbook = new ExcelFile(); //string fileName = "ich_will.mp3"; //string path = Path.Combine(Environment.CurrentDirectory, @"wwwroot\", "ReportTemplate.xlsx"); //var worksheet = workbook.Worksheets.Add("Chart"); var workbook = ExcelFile.Load(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/ReportTemplate.xlsx")); //var workbook = ExcelFile.Load(Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, @"wwwroot\", "ReportTemplate.xlsx")); var worksheet = workbook.Worksheets[0]; //Data for Emotional Regulation Score worksheet.Cells["C10"].Value = studentTestScore.Name; worksheet.Cells["C12"].Value = studentTestScore.Age; worksheet.Cells["C14"].Value = studentTestScore.Email_ID; worksheet.Cells["C16"].Value = studentTestScore.Gender; worksheet.Cells["C18"].Value = studentTestScore.School_Name; worksheet.Cells["C20"].Value = studentTestScore.TestDate; worksheet.Cells["B68"].Value = "Emotional Parameters"; worksheet.Cells["B69"].Value = "Self-Blame"; worksheet.Cells["B70"].Value = "Acceptance"; worksheet.Cells["B71"].Value = "Rumination"; worksheet.Cells["B72"].Value = "Positive Refocusing"; worksheet.Cells["B73"].Value = "Refocus on Planning"; worksheet.Cells["B74"].Value = "Positive Reappraisal"; worksheet.Cells["B75"].Value = "Putting into Perspective"; worksheet.Cells["B76"].Value = "Catastrophizing"; worksheet.Cells["B77"].Value = "Other_Blame"; worksheet.Cells["C68"].Value = "Your Score"; worksheet.Cells["C69"].Value = studentTestScore.ER_Self_Blame; worksheet.Cells["C70"].Value = studentTestScore.ER_Acceptance; worksheet.Cells["C71"].Value = studentTestScore.ER_Rumination; worksheet.Cells["C72"].Value = studentTestScore.ER_PositiveRefocusing; worksheet.Cells["C73"].Value = studentTestScore.ER_RefocusonPlanning; worksheet.Cells["C74"].Value = studentTestScore.ER_PositiveReappraisal; worksheet.Cells["C75"].Value = studentTestScore.ER_PuttingintoPerspective; worksheet.Cells["C76"].Value = studentTestScore.ER_Catastrophizing; worksheet.Cells["C77"].Value = studentTestScore.ER_Other_blame; worksheet.Cells["D68"].Value = "Average"; worksheet.Cells["D69"].Value = 7.0; worksheet.Cells["D70"].Value = 8.5; worksheet.Cells["D71"].Value = 7.5; worksheet.Cells["D72"].Value = 9.5; worksheet.Cells["D73"].Value = 9.5; worksheet.Cells["D74"].Value = 10.1; worksheet.Cells["D75"].Value = 9.5; worksheet.Cells["D76"].Value = 6.0; worksheet.Cells["D77"].Value = 6.5; //chart for Emotional Regulation var emotional_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B68", "H88"); emotional_chart.SelectData(worksheet.Cells.GetSubrange("B68", "D77"), true); emotional_chart.DataLabels.Show(); emotional_chart.Title.Text = "Emotional Regulation"; emotional_chart.Legend.IsVisible = true; emotional_chart.Legend.Position = ChartLegendPosition.Bottom; //Data for Verbal Score worksheet.Cells["B177"].Value = "Verbal Subtest Scores"; worksheet.Cells["B178"].Value = "Information"; worksheet.Cells["B179"].Value = "Comprehension"; worksheet.Cells["B180"].Value = "Arithmetic"; worksheet.Cells["B181"].Value = "Similarities"; worksheet.Cells["B182"].Value = "Vocabulary"; worksheet.Cells["C177"].Value = "Your Score"; worksheet.Cells["C178"].Value = studentTestScore.Verbal_Scaled_INF; worksheet.Cells["C179"].Value = studentTestScore.Verbal_Scaled_COM; worksheet.Cells["C180"].Value = studentTestScore.Verbal_Scaled_ARI; worksheet.Cells["C181"].Value = studentTestScore.Verbal_Scaled_SIM; worksheet.Cells["C182"].Value = studentTestScore.Verbal_Scaled_VOC; worksheet.Cells["D177"].Value = "Average"; worksheet.Cells["D178"].Value = 50; worksheet.Cells["D179"].Value = 50; worksheet.Cells["D180"].Value = 50; worksheet.Cells["D181"].Value = 50; worksheet.Cells["D182"].Value = 50; //chart for Verbal Score var verbal_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B177", "H189"); verbal_chart.SelectData(worksheet.Cells.GetSubrange("B177", "D182"), true); verbal_chart.DataLabels.Show(); verbal_chart.Title.Text = "Verbal Scores"; verbal_chart.Legend.IsVisible = true; verbal_chart.Legend.Position = ChartLegendPosition.Bottom; //Data for Performance Score worksheet.Cells["B191"].Value = "Performance Subtest Scores"; worksheet.Cells["B192"].Value = "Digital Sympbol"; worksheet.Cells["B193"].Value = "Picture Completion"; worksheet.Cells["B194"].Value = "Spatial"; worksheet.Cells["B195"].Value = "Picture Arrangement"; worksheet.Cells["B196"].Value = "Object Assembly"; worksheet.Cells["C191"].Value = "Your Score"; worksheet.Cells["C192"].Value = studentTestScore.Performance_Scaled_DS; worksheet.Cells["C193"].Value = studentTestScore.Performance_Scaled_PC; worksheet.Cells["C194"].Value = studentTestScore.Performance_Scaled_SPA; worksheet.Cells["C195"].Value = studentTestScore.Performance_Scaled_PA; worksheet.Cells["C196"].Value = studentTestScore.Performance_Scaled_OA; worksheet.Cells["D191"].Value = "Average"; worksheet.Cells["D192"].Value = 50; worksheet.Cells["D193"].Value = 50; worksheet.Cells["D194"].Value = 50; worksheet.Cells["D195"].Value = 50; worksheet.Cells["D196"].Value = 50; //chart for Performance Score var perf_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B191", "H203"); perf_chart.SelectData(worksheet.Cells.GetSubrange("B191", "D196"), true); perf_chart.DataLabels.Show(); perf_chart.Title.Text = "Performance Scores"; perf_chart.Legend.IsVisible = true; perf_chart.Legend.Position = ChartLegendPosition.Bottom; //Data for IQ Score worksheet.Cells["B207"].Value = "IQ Scores"; worksheet.Cells["B208"].Value = "Verbal Score"; worksheet.Cells["B209"].Value = "Performance"; worksheet.Cells["B210"].Value = "Full Scale Score"; worksheet.Cells["C207"].Value = "IQ Score"; worksheet.Cells["C208"].Value = studentTestScore.IQ_Verbal; worksheet.Cells["C209"].Value = studentTestScore.IQ_Perfromance; worksheet.Cells["C210"].Value = studentTestScore.Full_Scale_IQ; worksheet.Cells["D207"].Value = "Average IQ Score"; worksheet.Cells["D208"].Value = 100; worksheet.Cells["D209"].Value = 100; worksheet.Cells["D210"].Value = 100; worksheet.Cells["E207"].Value = "Percentile Score"; worksheet.Cells["E208"].Value = studentTestScore.Percentile_Verbal; worksheet.Cells["E209"].Value = studentTestScore.Percentile_Performance; worksheet.Cells["E210"].Value = studentTestScore.Full_Percentile; //chart for IQ Score var IQ_chart = worksheet.Charts.Add(GemBox.Spreadsheet.Charts.ChartType.Column, "B207", "H221"); IQ_chart.SelectData(worksheet.Cells.GetSubrange("B207", "E210"), true); IQ_chart.DataLabels.Show(); IQ_chart.Title.Text = "Your IQ Scores"; IQ_chart.Legend.IsVisible = true; IQ_chart.Legend.Position = ChartLegendPosition.Bottom; // Make entire sheet print on a single page. //worksheet.PrintOptions.FitWorksheetWidthToPages = 1; workbook.Save(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx")); //workbook.Save(Path.Combine(System.AppDomain.CurrentDomain.BaseDirectory, @"wwwroot\pdf\", "Chart" + ref_code.ToString() + ".xlsx")); //workbook.Save("./wwwroot/pdf/Chart" + ref_code.ToString() + ".xlsx"); Spire.Xls.Workbook pdfWorkbook = new Spire.Xls.Workbook(); pdfWorkbook.LoadFromFile(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx")); pdfWorkbook.SaveToFile(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Results" + studentTestScore.Student_TestID + ".pdf"), Spire.Xls.FileFormat.PDF); Execute(ref_code, studentTestScore).Wait(); if ((System.IO.File.Exists(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx")))) { System.IO.File.Delete(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Chart" + studentTestScore.Student_TestID + ".xlsx")); } if ((System.IO.File.Exists(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Results" + studentTestScore.Student_TestID + ".pdf")))) { System.IO.File.Delete(System.Web.Hosting.HostingEnvironment.MapPath("~/wwwroot/pdf/Results" + studentTestScore.Student_TestID + ".pdf")); } }
private void bt_baocao_Click(object sender, EventArgs e) { //Create a new workbook Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); //Initialize worksheet workbook.CreateEmptySheets(1); Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; string conString = @"Data Source=DESKTOP-RGHAECC;Initial Catalog=SucKhoeNhanVien_PNT;Integrated Security=True"; SqlConnection sqlCon = new SqlConnection(conString); sqlCon.Open(); SqlDataAdapter da = new SqlDataAdapter("SELECT LichSuBenh.Id_LichSuBenh,NhanVien.MaNV,NhanVien.TenNV,Khoa.TenKhoa,ChucVu.TenChucVu,LichSuBenh.NgayKham,LichSuBenh.PhanLoaiSK,LichSuBenh.BenhKhac from NhanVien, LichSuBenh, Khoa , ChucVu where NhanVien.IdNhanVien = LichSuBenh.IdNhanVien And NhanVien.Khoa = Khoa.IdKhoa And NhanVien.IdChucVu = ChucVu.IdChucVu", sqlCon); System.Data.DataTable dtMainSQLData = new System.Data.DataTable(); da.Fill(dtMainSQLData); DataColumnCollection dcCollection = dtMainSQLData.Columns; // Export Data into EXCEL Sheet Microsoft.Office.Interop.Excel.Application ExcelApp = new Microsoft.Office.Interop.Excel.Application(); ExcelApp.Application.Workbooks.Add(Type.Missing); ExcelApp.Columns.ColumnWidth = 20; //Create a new workbook // Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); //Initialize worksheet // workbook.CreateEmptySheets(1); //Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; //Set sheet name sheet.Name = "Chart data"; //Set the grid lines invisible sheet.GridLinesVisible = false; //Create a chart Spire.Xls.Chart chart = sheet.Charts.Add(ExcelChartType.Pie3D); //Set region of chart data chart.DataRange = sheet.Range["B2:B5"]; chart.SeriesDataFromRange = false; //Set position of chart chart.LeftColumn = 1; chart.TopRow = 6; chart.RightColumn = 9; chart.BottomRow = 25; //Chart title chart.ChartTitle = "Sales by year"; chart.ChartTitleArea.IsBold = true; chart.ChartTitleArea.Size = 12; //Initialize the chart series Spire.Xls.Charts.ChartSerie cs = chart.Series[0]; //Chart Labels resource cs.CategoryLabels = sheet.Range["A2:A5"]; //Chart value resource cs.Values = sheet.Range["B2:B5"]; //Set the value visible in the chart cs.DataPoints.DefaultDataPoint.DataLabels.HasValue = true; //Year sheet.Range["A1"].Value = "Year"; sheet.Range["A2"].Value = "2002"; sheet.Range["A3"].Value = "2003"; sheet.Range["A4"].Value = "2004"; sheet.Range["A5"].Value = "2005"; //Sales sheet.Range["B1"].Value = "Sales"; sheet.Range["B2"].NumberValue = 4000; sheet.Range["B3"].NumberValue = 6000; sheet.Range["B4"].NumberValue = 7000; sheet.Range["B5"].NumberValue = 8500; //Style sheet.Range["A1:B1"].Style.Font.IsBold = true; sheet.Range["A2:B2"].Style.KnownColor = ExcelColors.LightYellow; sheet.Range["A3:B3"].Style.KnownColor = ExcelColors.LightGreen1; sheet.Range["A4:B4"].Style.KnownColor = ExcelColors.LightOrange; sheet.Range["A5:B5"].Style.KnownColor = ExcelColors.LightTurquoise; //Border sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeTop].LineStyle = LineStyleType.Thin; sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thin; sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeLeft].LineStyle = LineStyleType.Thin; sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].Color = Color.FromArgb(0, 0, 128); sheet.Range["A1:B5"].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin; //Number format sheet.Range["B2:C5"].Style.NumberFormat = "\"$\"#,##0"; chart.PlotArea.Fill.Visible = false; //Save the file // workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003); //Launch the file //System.Diagnostics.Process.Start("Sample.xls"); // ExcelApp.Cells.CopyFromRecordset(objRS); for (int i = 1; i < dtMainSQLData.Rows.Count + 2; i++) { for (int j = 1; j < dtMainSQLData.Columns.Count + 1; j++) { if (i == 1) { ExcelApp.Cells[i, j] = dcCollection[j - 1].ToString(); ExcelApp.Cells[i, j].Font.Bold = true; ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; Microsoft.Office.Interop.Excel.Range formatRange; formatRange = ExcelApp.Cells[i, j]; formatRange.Interior.Color = System.Drawing. ColorTranslator.ToOle(System.Drawing.Color.CornflowerBlue); } else { ExcelApp.Cells[i, j] = dtMainSQLData.Rows[i - 2][j - 1].ToString(); } ExcelApp.Cells[i, j].HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; } } //Save the file workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003); //Launch the file System.Diagnostics.Process.Start("Sample.xls"); ExcelApp.ActiveWorkbook.SaveCopyAs("C:\\Users\\Oanh\\Desktop\\BaoCaoSK.xls"); ExcelApp.ActiveWorkbook.Saved = true; ExcelApp.Quit(); }