public ActionResult DownloadExcel() { BusinessLayer BAL = new BusinessLayer(); var workbook = new Spire.Xls.Workbook(); workbook.LoadFromFile(Server.MapPath("~/DetailFormatInExcel/DetailsFormat.xlsx")); var worksheet1 = workbook.Worksheets[0]; var worksheet2 = workbook.Worksheets[1]; byte[] array = null; var dt1 = BAL.GetXlsTableBooks(); worksheet1.InsertDataTable(dt1, false, 3, 1); var dt2 = BAL.GetXlsTableCourse(); worksheet2.InsertDataTable(dt2, false, 3, 1); var dt3 = BAL.GetXlsTableTeacher(); worksheet2.InsertDataTable(dt3, false, 3, 3); using (var ms = new System.IO.MemoryStream()) { workbook.SaveToStream(ms, FileFormat.Version2010); ms.Seek(0, System.IO.SeekOrigin.Begin); array = ms.ToArray(); } return(File(array, "application / vnd.openxmlformats - officedocument.spreadsheetml.sheet", " Detail.xlsx")); }
/// <summary> /// Создает файл формата .doc /// </summary> /// <param name="path">Путь к Excel файлу, из которого берется таблица.</param> private void CreateWordFile(string path) { Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); workbook.LoadFromFile(path); Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; Document doc = new Document(); Table table = doc.AddSection().AddTable(true); table.ResetCells(sheet.LastRow, sheet.LastColumn); for (int r = 1; r <= sheet.LastRow; r++) { for (int c = 1; c <= sheet.LastColumn; c++) { CellRange xCell = sheet.Range[r, c]; TableCell wCell = table.Rows[r - 1].Cells[c - 1]; TextRange textRange = wCell.AddParagraph().AppendText(xCell.NumberText); } } for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Rows[i].Cells.Count; j++) { table.Rows[i].Cells[j].Width = 100f; } } doc.SaveToFile("result.doc", Spire.Doc.FileFormat.Docx); System.Diagnostics.Process.Start("result.doc"); }
public static void getstudentsfromexcel2(String updir) { studentList = null; studentList = new ObservableCollection <Student>(); string strFileName = updir; Spire.Xls.Workbook wb = new Spire.Xls.Workbook(); wb.LoadFromFile(strFileName); Spire.Xls.CellRange range; Worksheet sheet = wb.Worksheets[0]; for (int i = 1; i <= 150; i++) { range = sheet.Range["A" + i.ToString()]; String Strxvhao = range.DisplayedText.ToString().Trim(); range = sheet.Range["C" + i.ToString()]; String Strxuehao = range.DisplayedText.ToString().Trim(); range = sheet.Range["D" + i.ToString()]; String Strxm = range.DisplayedText.ToString().Trim(); { if (isxuehao(Strxuehao)) { studentList.Add(new Student(Strxuehao, Strxm, int.Parse(Strxvhao), "11111111", -1, -1)); } } } }
public void ChangeExcel2Image(string filename) { Spire.Xls.Workbook workbook = new Spire.Xls.Workbook(); workbook.LoadFromFile(filename); Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; sheet.SaveToImage("选型结果.jpg"); //图片后缀.bmp ,imagepath自己设置 }
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 static void ReplaceText(RepalcePageViewModel model, IList <FileNameCustom> fileNameCustoms) { if (!Utils.CheckListExists(fileNameCustoms)) { return; } IList <ReplaceViewModel> replaceModel = model.ReplaceViewModels; foreach (FileNameCustom fileNameCustom in fileNameCustoms) { string path = fileNameCustom.FilePath; if (Utils.CheckFileExists(path)) { Workbook workbook = new Spire.Xls.Workbook(); workbook.LoadFromFile(path); //存在文件就替换 if (model.AllSheet) { //全部页面替换 ReplaceWorkbook(workbook, model.ReplaceViewModels); } else { int sheetIndex = model.ReplaceSheetIndex; if (workbook.Worksheets.Count > sheetIndex) { //指定的sheet替换 ReplaceSheet(workbook.Worksheets[sheetIndex], model.ReplaceViewModels); } } workbook.Save(); } } }
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 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())); }
private void export_Click(object sender, EventArgs e) { var path = @"D:\test\WorkOrder.xls"; var outPath = @"D:\\test\\aspose"; //HSSFWorkbook wk = null; //using (FileStream fs = File.Open(path, FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite)) //{ // wk = new HSSFWorkbook(fs); // fs.Close(); //} //ISheet sheet = wk.GetSheet("Sheet1"); using (Spire.Xls.Workbook workbook = new Spire.Xls.Workbook()) { workbook.LoadFromFile(path); Spire.Xls.Worksheet sheet = workbook.Worksheets[0]; const string main_Dir = @"c:/WenTest"; if (!Directory.Exists(main_Dir)) { //Directory.CreateDirectory(main_Dir); } else { ExportImg.DeleteFolder(main_Dir); Directory.CreateDirectory(main_Dir); } //string mypath = Environment.CurrentDirectory+"\\test"; if (File.Exists(outPath)) { File.Delete(outPath); Directory.CreateDirectory(outPath); } else { Directory.CreateDirectory(outPath); } //sheet.SetRowHeightPixels() //sheet.SaveToImage(outPath+"\\msample.png"); sheet.SaveToImage(sheet.FirstRow, sheet.FirstColumn, sheet.LastRow, sheet.LastColumn).Save(outPath + "\\test.jpg"); //IWorkbook workbook = new IWorkbook(); } }
public void LoadReport(string fileName, bool autoFit) { Workbook book = new Workbook(); book.LoadFromFile(fileName); ReportBookTemplate tplbook = new ReportBookTemplate(); tplbook.LoadTemplate(db, book, paramMap); tplbook.FillTemplate(); List <ReportSheetTemplate> tplList = tplbook.sheetList; // tpl.Clear(); // Open with Excel /* xlapp.Visible = false;*/ try { ClearReport(autoFit, book, tplList); // xlapp.Workbooks.Close (); // Copy Image } finally { book.Save(); book.Dispose(); /* xlapp.DisplayAlerts = true ; * * xlapp.Visible = true; */ RemoveWarning(fileName); Process.Start(fileName); } // remove warnning sheet. }
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; } }
static void Main(string[] args) { try { Workbook workbook = new Workbook(); workbook.LoadFromFile(@"C:\temp\CFRI_Clinical_DB_Approved_Studies_v24.xlsx"); //Initailize worksheet Worksheet sheet = workbook.Worksheets[0]; DataTable dataTable = sheet.ExportDataTable(); DataTableToJsonObj(dataTable); Console.ReadKey(); } catch (Exception e) { Console.WriteLine(e.Message); Console.ReadKey(); } }
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")); } }
public FileResult GetPDF() { #region "GetSessionVariables" string logon_user = (string)Session["logon_user"]; long teamId, repId; int month, year; teamId = (long)Session["IS_Team"]; repId = (long)Session["IS_Rep"]; month = (int)Session["IS_Month"]; year = (int)Session["IS_Year"]; OSC_Team oSC_Team = db.Teams.Find(teamId); OSC_Representative oSC_Representative = db.Representatives.Find(repId); #endregion "GetSessionVariables" #region "WorkbookUsingClosedXML" string templateFileName = Server.MapPath("~/Templates/Template.xlsx"); XLWorkbook workbook = new XLWorkbook(templateFileName); string exportPath = Server.MapPath("~/Export"); string excelFileName = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".xlsx"; string pdfFileName = "IndividualScorecard_" + oSC_Representative.LastName + oSC_Representative.FirstName + "_" + month.ToString() + year.ToString() + "_" + logon_user + ".pdf"; string tempFileName = exportPath + "/" + excelFileName; string outputFileName = exportPath + "/" + pdfFileName; if (System.IO.File.Exists(tempFileName)) { System.IO.File.Delete(tempFileName); } workbook.SaveAs(tempFileName); IXLWorksheet worksheet = workbook.Worksheet("Scorecard"); IXLWorksheet worksheet2 = workbook.Worksheet("Workitems"); IXLWorksheet worksheet3 = workbook.Worksheet("NPT"); #region "Table" List <IndividualScorecard> list = new List <IndividualScorecard>(); list = af.GetIndividualScorecardFull(teamId, repId, month, year); int x = 2; int y = 29; IXLCell cell = worksheet.Cell(y, x); cell.SetValue("Attendance"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Overtime"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("NPT Hours"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Processing Time"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Total Transactions"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Rate of Production"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Processing Quality"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Total Utilization"); x += 1; cell = worksheet.Cell(29, x); cell.SetValue("Efficiency"); for (int i = 1; i <= list.Count; i++) { y += 1; x = 2; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualActivities.Attendance_Days.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualActivities.Overtime_Hours.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualNonProcessing.NPTHours.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualBIProd.ProcessingHours.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualBIProd.Count.ToString()); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].ProductivityRating.ToString() + "%"); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].individualBIQual.QualityRating.ToString() + "%"); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].TotalUtilization.ToString() + "%"); x += 1; cell = worksheet.Cell(y, x); cell.SetValue(list[i - 1].Efficiency.ToString() + "%"); } do { x += 1; worksheet.Column(x).Clear(); }while (x <= 23); #endregion "Table" #region "Highlights" IndividualScorecard ind = af.GetIndividualScorecard(teamId, repId, month, year); cell = worksheet.Cell("A47"); cell.SetValue(ind.Highlights); #endregion "Highlights" #region "Worktypes" List <IndividualWorkTypes> worktypes = af.GetIndividualWorkTypes(teamId, repId, month, year); y = 1; x = 1; IXLCell cell2 = worksheet2.Cell(y, x); for (int i = 1; i <= worktypes.Count; i++) { y += 1; x = 1; cell2 = worksheet2.Cell(y, x); cell2.SetValue(worktypes[i - 1].WorkType.ToString()); x += 1; cell2 = worksheet2.Cell(y, x); cell2.SetValue(((int)worktypes[i - 1].Count)); } do { y += 1; worksheet2.Row(y).Clear(); }while(y <= 200); #endregion "Worktypes" #region "NPT" List <PieList> npts = af.GetIndividualNPT(teamId, repId, month, year); y = 1; x = 1; IXLCell cell3 = worksheet3.Cell(y, x); for (int i = 1; i <= npts.Count; i++) { y += 1; x = 1; cell3 = worksheet3.Cell(y, x); cell3.SetValue(npts[i - 1].Category.ToString()); x += 1; cell3 = worksheet3.Cell(y, x); cell3.SetValue((double)npts[i - 1].TimeSpent); } do { y += 1; worksheet3.Row(y).Clear(); }while (y <= 200); #endregion "NPT" #region "SaveWorkBook"" workbook.SaveAs(tempFileName); #endregion "SaveWorkBook" #endregion "WorkbookUsingClosedXML" #region "UsingSpireXLS" sxl.Workbook swb = new sxl.Workbook(); swb.LoadFromFile(tempFileName); sxl.Worksheet sws1 = swb.Worksheets[0]; sxl.Worksheet sws2 = swb.Worksheets[1]; sxl.Worksheet sws3 = swb.Worksheets[2]; sws1.TextBoxes[0].Text = oSC_Team.TeamName + " Monthly Scorecard"; sws1.TextBoxes[1].Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName; sws1.TextBoxes[2].Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString(); sxl.Chart chart1 = sws1.Charts[0]; chart1.DataRange = sws2.Range[1, 1, sws2.Rows.Count(), 2]; sxl.Chart chart2 = sws1.Charts[1]; chart2.DataRange = sws3.Range[1, 1, sws3.Rows.Count(), 2]; swb.Save(); #endregion "UsingSpireXLS" #region "WorkbookUsingInterop" //remove this codes when 403 - 454 when closedXML can generate this to PDF xl.Application app = new xl.Application(); xl.Workbook wb = app.Workbooks.Open(tempFileName); #region "Cover" xl.Sheets xs = wb.Worksheets; xl.Worksheet ws = (xl.Worksheet)xs.get_Item("Scorecard"); xl.Worksheet ws2 = (xl.Worksheet)xs.get_Item("Workitems"); xl.Worksheet ws3 = (xl.Worksheet)xs.get_Item("NPT"); int txt_ctr = 1; foreach (xl.Shape shp in ws.Shapes) { if (shp.Type == Microsoft.Office.Core.MsoShapeType.msoTextBox) { if (txt_ctr == 1) { shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Team.TeamName + " Monthly Scorecard"; } else if (txt_ctr == 2) { shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = oSC_Representative.FirstName + " " + oSC_Representative.LastName; shp.ZOrder(MsoZOrderCmd.msoBringToFront); } else { shp.TextFrame.Characters(Type.Missing, Type.Missing).Text = db.months.Where(m => m.Value == Convert.ToString((int)month)).First().Text + "-" + year.ToString(); shp.ZOrder(MsoZOrderCmd.msoBringToFront); } } txt_ctr += 1; } int cht_ctr = 1; foreach (xl.ChartObject cht in ws.ChartObjects()) { if (cht_ctr == 1) { xl.Range last = ws2.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing); xl.Range chartRange = ws2.get_Range("A1", last); cht.Chart.SetSourceData(chartRange); } else { xl.Range last = ws3.Cells.SpecialCells(xl.XlCellType.xlCellTypeLastCell, Type.Missing); xl.Range chartRange = ws3.get_Range("A1", last); cht.Chart.SetSourceData(chartRange); } cht_ctr += 1; } #endregion "Cover" wb.Save(); wb.ExportAsFixedFormat(xl.XlFixedFormatType.xlTypePDF, outputFileName, xl.XlFixedFormatQuality.xlQualityStandard); wb.Close(); app.Quit(); #endregion "WorkbookUsingInterop" #region "Return" byte[] fileBytes = System.IO.File.ReadAllBytes(outputFileName); string fileName = pdfFileName; return(File(fileBytes, System.Net.Mime.MediaTypeNames.Application.Octet, fileName)); #endregion "Return" }
private void button7_Click(object sender, EventArgs e) { _PrintExcel.Workbook workbook = new _PrintExcel.Workbook(); workbook.LoadFromFile(filename); _PrintExcel.Worksheet sheet = workbook.Worksheets["printout"]; sheet.SaveToPdf(path + "Sizing Printout.pdf"); System.Diagnostics.Process.Start("explorer.exe", path + "Sizing Printout.pdf"); workbook.Dispose(); //PrintDialog dialog = new PrintDialog(); //dialog.AllowPrintToFile = true; //dialog.AllowCurrentPage = true; //dialog.AllowSomePages = true; //dialog.AllowSelection = true; //dialog.UseEXDialog = true; //dialog.PrinterSettings.Duplex = Duplex.Simplex; //dialog.PrinterSettings.PrintRange = PrintRange.SomePages; //workbook.PrintDialog = dialog; //PrintDocument pd = workbook.PrintDocument; //if (dialog.ShowDialog() == DialogResult.OK) //{ pd.Print(); } //Application excel = new Application(); //_Excel.Workbook workbook = excel.Workbooks.Open(filename, ReadOnly: false, Editable: true); //_Excel.Worksheet worksheet = workbook.Worksheets["printout"] as _Excel.Worksheet; //worksheet.PrintPreview(); //worksheet.PrintOut(From:1,To:2,Copies:1, Preview:true, Type.Missing, PrintToFile:true, Type.Missing, Type.Missing); //worksheet.PrintPreview(); //PrintDialog printDlg = new PrintDialog(); /*PrintDocument printDoc = new PrintDocument(); * printDoc.DocumentName = "Print Document"; * printDlg.Document = printDoc; * printDlg.AllowSelection = true; * printDlg.AllowSomePages = true; * if (printDlg.ShowDialog() == DialogResult.OK) printDoc.Print();*/ // worksheet.PrintOut( //1, 1, 1, Type.Missing, //Type.Missing, Type.Missing, Type.Missing, Type.Missing); // // Cleanup: // GC.Collect(); // GC.WaitForPendingFinalizers(); // Marshal.FinalReleaseComObject(worksheet); // workbook.Close(false, Type.Missing, Type.Missing); // Marshal.FinalReleaseComObject(workbook); // excel.Quit(); // Marshal.FinalReleaseComObject(excel); /*workbook.DisplayAlerts = false; * excel.ActiveWorkbook.Save(); * excel.Application.Quit(); * excel.Quit();*/ }