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;
}
