public static void SaveExcelTemplate(SpreadsheetGear.IWorkbook workbook, string fileName, string fileType, out string outPath) { string path; if (fileType == "xlsx") { path = Path.Combine(Path.GetTempPath(), fileName + DateTime.Now.ToString("yyMMdd_hhmmss") + ".xlsx"); workbook.SaveAs(path, SpreadsheetGear.FileFormat.OpenXMLWorkbook); } else { path = Path.Combine(Path.GetTempPath(), fileName + DateTime.Now.ToString("yyMMdd_hhmmss") + ".xls"); workbook.SaveAs(path, SpreadsheetGear.FileFormat.Excel8); } outPath = path; }
static void Main(string[] args) { SpreadsheetGear.IWorkbook gear = SpreadsheetGear.Factory.GetWorkbook(); SpreadsheetGear.IWorksheet worksheet = gear.Worksheets.Add(); SpreadsheetGear.IWorksheetWindowInfo windowInfo = worksheet.WindowInfo; // Load some sample data. SpreadsheetGear.IRange dataRange = worksheet.Cells["A1:B6"]; dataRange.Value = new string[, ] { { "A", "$7,923" }, { "B", "$5,954" }, { "C", "$5,522" }, { "D", "$3,701" }, { "E", "$5,522" }, { "F", "$3,701" } }; SpreadsheetGear.Shapes.IShape shape = worksheet.Shapes.AddChart(0, 0, 100, 100); SpreadsheetGear.Charts.IChart chart = shape.Chart; chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns); chart.ChartType = SpreadsheetGear.Charts.ChartType.ColumnStacked; chart.ChartGroups[0].GapWidth = 50; chart.HasTitle = false; chart.HasLegend = false; chart.PlotVisibleOnly = true; chart.ChartArea.Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178); chart.SeriesCollection[0].HasDataLabels = false; chart.SeriesCollection[0].HasLeaderLines = false; chart.SeriesCollection[0].MarkerStyle = SpreadsheetGear.Charts.MarkerStyle.Automatic; shape = worksheet.Shapes.AddChart(500, 500, 600, 600); chart = shape.Chart; chart.SetSourceData(dataRange, SpreadsheetGear.Charts.RowCol.Columns); chart.ChartType = SpreadsheetGear.Charts.ChartType.Pie; SpreadsheetGear.Charts.ISeries series = chart.SeriesCollection[0]; series.XValues = dataRange; // Add series data labels and change to show percentage only. series.HasDataLabels = true; series.DataLabels.ShowPercentage = true; series.DataLabels.ShowValue = false; series.DataLabels.ShowCategoryName = false; worksheet.Cells["F3"].NumberFormat = @"_-* #,##0.00_-;-* #,##0.00_-;_-@_-"; worksheet.Cells["F3"].Value = 3553654566.641; worksheet.Cells["F6"].Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178); worksheet.Cells["F6"].Font.Name = "Webdings"; worksheet.Cells["F6"].Value = "a"; worksheet.Cells["F9"].Font.Color = SpreadsheetGear.Color.FromArgb(178, 178, 178); worksheet.Cells["F9"].Font.Name = "Webdings"; worksheet.Cells["F9"].Value = "r"; gear.SaveAs(@"D:\Excels.xls", SpreadsheetGear.FileFormat.OpenXMLWorkbook); }
// 导出excel模板 private void efBtn_export_model_Click(object sender, EventArgs e) { SaveFileDialog saveFileDialog = new SaveFileDialog(); saveFileDialog.Filter = "Excel files (*.xls)|*.xls"; saveFileDialog.FilterIndex = 2; saveFileDialog.RestoreDirectory = true; saveFileDialog.FileName = "Template"; try { if (saveFileDialog.ShowDialog(this) == DialogResult.OK) { #if (Devxpress) if (m_ctrlGrid is GridControl) { GridControl currentGridControl = m_ctrlGrid as GridControl; GridView currentGridView = currentGridControl.FocusedView as GridView; DataSet dsSource = currentGridControl.DataSource as DataSet; if (dsSource == null) { return; } DataTable dtExport = dsSource.Tables[currentGridControl.DataMember].Clone(); int index = 0; for (index = 1; index < currentGridView.VisibleColumns.Count; ++index) { string strColEname = currentGridView.VisibleColumns[index].FieldName; if (!dtExport.Columns.Contains(strColEname)) { dtExport.Columns.Add(strColEname); //dtExport.Columns[strColEname].SetOrdinal(index - 1); } else if (string.IsNullOrEmpty(strColEname.Trim())) { dtExport.Columns.Add(currentGridView.VisibleColumns[index].Name); } } index--; //while (index < dtExport.Columns.Count) //{ // dtExport.Columns.RemoveAt(index); //} SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(); SpreadsheetGear.IWorksheet workSheet = workbook.Worksheets[0]; workSheet.Name = string.IsNullOrEmpty(dtExport.TableName) ? "tmp" : dtExport.TableName; for (index = 0; index < dtExport.Columns.Count; ++index) { if (efRB_col_cname.Checked || efRB_col_seq.Checked) { string strCaption = ""; if (null != currentGridView.Columns.ColumnByFieldName(dtExport.Columns[index].ColumnName)) { strCaption = currentGridView.Columns.ColumnByFieldName(dtExport.Columns[index].ColumnName).Caption; } else { continue; } strCaption = strCaption.Replace("<br>", ""); workSheet.Cells[0, index].Formula = strCaption.Replace(" ", ""); } else { workSheet.Cells[0, index].Formula = dtExport.Columns[index].ColumnName; } workSheet.Cells[0, index].Columns.AutoFit(); workSheet.Cells[0, index].Interior.Color = Color.Gray; workSheet.Cells[0, index].Borders.LineStyle = SpreadsheetGear.LineStyle.Continuous; SpreadsheetGear.IRange iColumnRange = workSheet.Cells[0, index].EntireColumn; if (dtExport.Columns[index].DataType == typeof(DateTime)) { GridColumn gridColumn = currentGridView.Columns.ColumnByFieldName(dtExport.Columns[index].ColumnName); iColumnRange.NumberFormat = gridColumn.DisplayFormat.FormatString; } else if (dtExport.Columns[index].DataType == typeof(string)) { iColumnRange.NumberFormat = "@"; } } dtExport.Merge(dsSource.Tables[currentGridControl.DataMember], true, MissingSchemaAction.Ignore); dtExport.AcceptChanges(); if (dtExport.Rows.Count > 0) { SpreadsheetGear.IRange range = workSheet.Cells["A2"]; range.CopyFromDataTable(dtExport, SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders); } workbook.SaveAs(saveFileDialog.FileName, SpreadsheetGear.FileFormat.XLS97); return; } #endif if (m_ctrlGrid is DataGridView) { DataGridView currentGridControl = m_ctrlGrid as DataGridView; DataTable dsSource = currentGridControl.DataSource as DataTable; if (dsSource == null) { return; } DataTable dtExport = dsSource.Clone(); int index = 0; for (index = 1; index < currentGridControl.Columns.Count; ++index) { string strColEname = currentGridControl.Columns[index].Name; if (!dtExport.Columns.Contains(strColEname)) { dtExport.Columns.Add(strColEname); //dtExport.Columns[strColEname].SetOrdinal(index - 1); } } //index--; //while (index < dtExport.Columns.Count) //{ // dtExport.Columns.RemoveAt(index); //} SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(); SpreadsheetGear.IWorksheet workSheet = workbook.Worksheets[0]; workSheet.Name = string.IsNullOrEmpty(dtExport.TableName) ? "tmp" : dtExport.TableName; for (index = 0; index < dtExport.Columns.Count; ++index) { if (efRB_col_cname.Checked || efRB_col_seq.Checked) { string strCaption = currentGridControl.Columns[dtExport.Columns[index].ColumnName].HeaderText; strCaption = strCaption.Replace("<br>", ""); workSheet.Cells[0, index].Formula = strCaption.Replace(" ", ""); } else { workSheet.Cells[0, index].Formula = dtExport.Columns[index].ColumnName; } workSheet.Cells[0, index].Columns.AutoFit(); workSheet.Cells[0, index].Interior.Color = Color.Gray; workSheet.Cells[0, index].Borders.LineStyle = SpreadsheetGear.LineStyle.Continuous; SpreadsheetGear.IRange iColumnRange = workSheet.Cells[0, index].EntireColumn; if (dtExport.Columns[index].DataType == typeof(DateTime)) { //GridColumn gridColumn = currentGridControl.Columns[dtExport.Columns[index].ColumnName].di //iColumnRange.NumberFormat = gridColumn.DisplayFormat.FormatString; } else if (dtExport.Columns[index].DataType == typeof(string)) { iColumnRange.NumberFormat = "@"; } } dtExport.Merge(dsSource, true, MissingSchemaAction.Ignore); dtExport.AcceptChanges(); if (dtExport.Rows.Count > 0) { SpreadsheetGear.IRange range = workSheet.Cells["A2"]; range.CopyFromDataTable(dtExport, SpreadsheetGear.Data.SetDataFlags.NoColumnHeaders); } workbook.SaveAs(saveFileDialog.FileName, SpreadsheetGear.FileFormat.XLS97); return; } } } catch (Exception ex) { MessageBox.Show(ex.StackTrace); } }
public void Generate() { string ruc = string.Empty; string name = string.Empty; string email = string.Empty; int beforeMonth = Month; int currentYear = Year; int valueInt = 0; decimal valueDecimal = 0; int counterWorked = 0; SpreadsheetGear.IRange range = null; SpreadsheetGear.IWorksheet wsSource = null; SpreadsheetGear.IWorksheet wsTarget = null; //Retrieving Template and Source SpreadsheetGear.IWorkbook wbSource = SpreadsheetGear.Factory.GetWorkbook($@"{FileSource}"); List <string> codesSelected = new List <string>(); wsSource = wbSource.Worksheets[1]; if (Method == MethodReport.Random) { for (int i = 1; i <= RandomNumber; i++) { codesSelected.Add(wsSource.Cells[i, 0].Value.ToString()); } //while (codesSelected.Count < RandomNumber) //{ // int randonRowIndex = new Random().Next(1, 617); // if (!codesSelected.Contains(wsSource.Cells[randonRowIndex, 0].Value.ToString())) //} } else { codesSelected.Add(CodeStore); } wsSource = wbSource.Worksheets[0]; wsSource.Cells["G3"].Formula = wsSource.Cells["D3"].Formula.Replace("4", "3"); wsSource.Cells["H3"].Formula = wsSource.Cells["D3"].Formula.Replace("4", "36"); SpreadsheetGear.Drawing.Color basicColor = SpreadsheetGear.Drawing.Color.FromArgb(89, 89, 89); SpreadsheetGear.Drawing.Color blueColor = SpreadsheetGear.Drawing.Color.FromArgb(0, 112, 192); SpreadsheetGear.Drawing.Color orangeColor = SpreadsheetGear.Drawing.Color.FromArgb(255, 153, 51); this.TotalWork = codesSelected.Count; this.ProgressFinished = counterWorked; foreach (string code in codesSelected) { SpreadsheetGear.IWorkbook wbTarget = SpreadsheetGear.Factory.GetWorkbook($@"{System.AppDomain.CurrentDomain.BaseDirectory}\Resources\{FileTemplate}"); wbSource.WorkbookSet.Calculation = SpreadsheetGear.Calculation.Manual; wsSource = wbSource.Worksheets[0]; //Update Data from Excel range = wsSource.Cells["C3"]; range.Value = code; wbSource.WorkbookSet.Calculate(); wbSource.Save(); ruc = wsSource.Cells["G3"].Value?.ToString(); name = wsSource.Cells["D3"].Value?.ToString().Replace(".", string.Empty); email = wsSource.Cells["H3"].Value?.ToString(); NameActual = name; wsTarget = wbTarget.Worksheets[0]; wsTarget.Shapes["MAIN_WARNING1"].TextFrame.Characters.Font.Color = basicColor; wsTarget.Shapes["MAIN_WARNING2"].TextFrame.Characters.Font.Color = basicColor; // C12, C13, C14 beforeMonth = Month; currentYear = Year; valueInt = 0; valueDecimal = 0; for (int i = 14; i > 1; i--, beforeMonth--) // begin at Pos 14 { if (beforeMonth == 0) { currentYear -= 1; beforeMonth = 12; } range = wsSource.Cells[11, i]; // Row 11 range.Value = $"{beforeMonth}/{currentYear}"; } #region Setting Info wsTarget.Cells["AT7"].Value = FormatMonthYear(Month, Year); wsTarget.Cells["J9"].Value = wsSource.Cells["D3"].Value.ToString(); wsTarget.Cells["J10"].Value = wsSource.Cells["E3"].Value.ToString() + ", " + wsSource.Cells["F3"].Value.ToString(); wsTarget.Cells["J11"].Value = $"Comercio: {code}"; #endregion #region MainData // Main int index = 2; valueDecimal = 0; Decimal.TryParse(wsSource.Cells[5, 3].Value.ToString(), out valueDecimal); wsTarget.Cells["P24"].Value = (valueDecimal).ToString("N0"); Decimal.TryParse(wsSource.Cells[8, 3].Value.ToString(), out valueDecimal); wsTarget.Cells["P28"].Value = (valueDecimal).ToString("N0"); valueDecimal = 0; Decimal.TryParse(wsSource.Cells[5, 4].Value.ToString(), out valueDecimal); wsTarget.Cells["Y24"].Value = (valueDecimal).ToString("N0"); Decimal.TryParse(wsSource.Cells[8, 4].Value.ToString(), out valueDecimal); wsTarget.Cells["Y28"].Value = (valueDecimal).ToString("N0"); valueDecimal = 0; Decimal.TryParse(wsSource.Cells[5, 5].Value.ToString(), out valueDecimal); wsTarget.Cells["AH24"].Value = (valueDecimal).ToString("N0"); Decimal.TryParse(wsSource.Cells[8, 5].Value.ToString(), out valueDecimal); wsTarget.Cells["AH28"].Value = (valueDecimal).ToString("N0"); valueDecimal = 0; Decimal.TryParse(wsSource.Cells[5, 6].Value.ToString(), out valueDecimal); wsTarget.Cells["AQ24"].Value = (valueDecimal).ToString("N0"); Decimal.TryParse(wsSource.Cells[8, 6].Value.ToString(), out valueDecimal); wsTarget.Cells["AQ28"].Value = (valueDecimal).ToString("N0"); wsTarget = wbTarget.Worksheets[2]; wbSource.WorkbookSet.Calculate(); wbSource.Save(); #endregion #region Graphic 2 wsTarget = wbTarget.Worksheets[0]; wsTarget = wbTarget.Worksheets[2]; decimal lastYearmonth = 0; decimal actualMonth = 0; decimal sum3PreviousMonths = 0; DateTime?dateValue = null; for (var i = 2; i < 15; i++) { dateValue = ParseDateXlsToDateTime(int.Parse(wsSource.Cells[11, i].Value.ToString())); wsTarget.Cells[4, i].Value = FormatMonthYear(dateValue.Value.Month, dateValue.Value.Year, true); // headerDates valueDecimal = 0; range = wsSource.Cells[12, i]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells[5, i].Value = valueDecimal; if (i == 2) { lastYearmonth = valueDecimal; } else if (i == 14) { actualMonth = valueDecimal; } else if (i >= 11 && i < 14) { sum3PreviousMonths += valueDecimal; // sum of 3 previous months } valueDecimal = 0; range = wsSource.Cells[13, i]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells[6, i].Value = valueDecimal; } wsTarget = wbTarget.Worksheets[0]; var advices2 = EvalueAdviceG2(wsTarget.Cells["AT7"].Value.ToString() , sum3PreviousMonths, actualMonth, lastYearmonth, wsTarget.Cells["E48"].Value.ToString()); wsTarget.Cells["E48"].Value = advices2.Item1; wsTarget.Cells["E51"].Value = advices2.Item2; #endregion #region Graphic 3 wsTarget = wbTarget.Worksheets[2]; lastYearmonth = 0; actualMonth = 0; sum3PreviousMonths = 0; for (var i = 2; i < 15; i++) { dateValue = ParseDateXlsToDateTime(int.Parse(wsSource.Cells[11, i].Value.ToString())); wsTarget.Cells[9, i].Value = FormatMonthYear(dateValue.Value.Month, dateValue.Value.Year, true); // headerDates valueDecimal = 0; range = wsSource.Cells[14, i]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells[10, i].Value = valueDecimal; if (i == 2) { lastYearmonth = valueDecimal; } else if (i == 14) { actualMonth = valueDecimal; } else if (i >= 11 && i < 14) { sum3PreviousMonths += valueDecimal; // sum of 3 previous months } valueDecimal = 0; range = wsSource.Cells[15, i]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells[11, i].Value = valueDecimal; } wsTarget = wbTarget.Worksheets[0]; var advices3 = EvalueAdviceG3(wsTarget.Cells["AT7"].Value.ToString() , sum3PreviousMonths, actualMonth, lastYearmonth, wsTarget.Cells["AD48"].Value.ToString()); wsTarget.Cells["AD48"].Value = advices3.Item1; wsTarget.Cells["AD51"].Value = advices3.Item2; #endregion #region Graphic 4 index = 1; for (var i = 19; i < 24; i++, index++) { range = wsSource.Cells[19, 2]; int.TryParse(range.Value.ToString(), out valueInt); wsTarget.Cells["E59"].Value = valueInt.ToString(); range = wsSource.Cells[19, 3]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells["H59"].Value = $"({(int)(valueDecimal * 100)}%)"; range = wsSource.Cells[20, 2]; int.TryParse(range.Value.ToString(), out valueInt); wsTarget.Cells["E61"].Value = valueInt.ToString(); range = wsSource.Cells[20, 3]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells["H61"].Value = $"({(int)(valueDecimal * 100)}%)"; range = wsSource.Cells[21, 2]; int.TryParse(range.Value.ToString(), out valueInt); wsTarget.Cells["E63"].Value = valueInt.ToString(); range = wsSource.Cells[21, 3]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells["H63"].Value = $"({(int)(valueDecimal * 100)}%)"; range = wsSource.Cells[22, 2]; int.TryParse(range.Value.ToString(), out valueInt); wsTarget.Cells["E65"].Value = valueInt.ToString(); range = wsSource.Cells[22, 3]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells["H65"].Value = $"({(int)(valueDecimal * 100)}%)"; range = wsSource.Cells[23, 2]; int.TryParse(range.Value.ToString(), out valueInt); wsTarget.Cells["E67"].Value = valueInt.ToString(); range = wsSource.Cells[23, 3]; decimal.TryParse(range.Value.ToString(), out valueDecimal); wsTarget.Cells["H67"].Value = $"({(int)(valueDecimal * 100)}%)"; } wsTarget = wbTarget.Worksheets[0]; #endregion #region Graphic 5 wsTarget = wbTarget.Worksheets[2]; wbSource.WorkbookSet.Calculate(); List <(int, int)> mayor_days = new List <(int, int)>(); index = 7; // Begins on Sunday int lessValueIndex = 0; for (var i = 2; i < 9; i++, index--) { lessValueIndex = -1; valueInt = 0; int.TryParse(wsSource.Cells[33, i].Value.ToString(), out valueInt); wsTarget.Cells[15, i].Value = valueInt; if (valueInt > 0) { if (mayor_days.Count == 0) { mayor_days.Add((index, valueInt)); } else { if (mayor_days.Count < 3) { mayor_days.Add(ValueTuple.Create(index, valueInt)); } else { for (var pos = 0; pos < mayor_days.Count; pos++) { if (valueInt > mayor_days[pos].Item2) { lessValueIndex = pos; } } if (lessValueIndex != -1) { mayor_days[lessValueIndex] = (index, valueInt); } } } } } wsTarget = wbTarget.Worksheets[0]; var advices5 = EvalueAdviceG5(mayor_days, wsTarget.Cells["AD70"].Value.ToString()); wsTarget.Cells["AD70"].Value = advices5.Item1; #endregion string nameTarget = $"{name}~{ruc}~{email}.xlsx"; using (MemoryStream file = new MemoryStream()) { wbTarget.SaveToStream(file, SpreadsheetGear.FileFormat.OpenXMLWorkbook); wbTarget.SaveAs($@"{FolderPath}\{nameTarget}", SpreadsheetGear.FileFormat.OpenXMLWorkbook); GeneratePDF(file, $@"{FolderPath}\{nameTarget}", ruc.Trim()); } counterWorked++; this.ProgressFinished = counterWorked; } this.ProgressFinished = counterWorked; this.WorkFinished = true; }