public SpreadsheetGear.IWorkbook generarReporte(string nombreHoja, List <string> titulos, List <List <string> > contenido)
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
SpreadsheetGear.IRange cells = worksheet.Cells;
// Set the worksheet name.
if (nombreHoja.Length > 31)
{
worksheet.Name = nombreHoja.Replace('/', '-').Substring(0, 31);
}
else
{
worksheet.Name = nombreHoja.Replace('/', '-');
}
string ultimaColumna = "";
int tituloIndex = 1;
// Load column titles.
for (char c = 'A'; tituloIndex <= titulos.Count(); c++)
{
cells[c.ToString() + "1"].Formula = titulos[tituloIndex - 1];
if (tituloIndex == titulos.Count())
{
ultimaColumna = c.ToString();
}
tituloIndex++;
}
//centra los titulos del reporte
cells["A1:" + ultimaColumna + "1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
//carga el contenido del reporte
for (int i = 0; i < contenido.Count; i++)
{
for (int j = 0; j < contenido[i].Count; j++)
{
// 65 = 'A', 66 = 'B', etc. Empieza en la A2, B2, C2 ... y luego cambia de fila
string celda = (char)(j + 65) + (i + 2).ToString();
cells[celda].Formula = contenido[i][j];
}
}
cells["A1:" + ultimaColumna + "100"].Columns.AutoFit();
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010.
return(workbook);
}
public IActionResult DownloadReport()
{
// Create a new workbook.
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook();
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["Sheet1"];
SpreadsheetGear.IRange cells = worksheet.Cells;
// Set the worksheet name.
worksheet.Name = "2005 Sales";
// Load column titles and center.
cells["B1"].Formula = "North";
cells["C1"].Formula = "South";
cells["D1"].Formula = "East";
cells["E1"].Formula = "West";
cells["B1:E1"].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
// Load row titles using multiple cell text reference and iteration.
int quarter = 1;
foreach (SpreadsheetGear.IRange cell in cells["A2:A5"])
{
cell.Formula = "Q" + quarter++;
}
// Load random data and format as $ using a multiple cell range.
SpreadsheetGear.IRange body = cells[1, 1, 4, 4];
body.Formula = "=RAND() * 10000";
body.NumberFormat = "$#,##0_);($#,##0)";
// Save workbook to an Open XML (XLSX) workbook stream.
System.IO.Stream stream = workbook.SaveToStream(
SpreadsheetGear.FileFormat.OpenXMLWorkbook);
// Reset stream's current position back to the beginning.
stream.Seek(0, System.IO.SeekOrigin.Begin);
// Stream the Excel spreadsheet to the client in a format
// compatible with Excel 97/2000/XP/2003/2007/2010/2013/2016.
return(new FileStreamResult(stream,
"application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"));
}
private void SetParameters()
{
string xlsPath;
SpreadsheetGear.IWorkbook workbook = null;
SpreadsheetGear.IWorksheet sheet = null;
SpreadsheetGear.IRange range = null;
SetStatus("Setting Parameter Data");
try
{
xlsPath = EnsureXLSFile();
workbook = SpreadsheetGear.Factory.GetWorkbook(xlsPath);
sheet = workbook.Worksheets["Data"];
foreach (string key in m_Inputs.Parameters.Keys)
{
range = null;
try
{
range = sheet.Cells[key];
}
catch (Exception)
{
}
if (range != null)
{
range.Value = m_Inputs.Parameters[key];
}
}
workbook.WorkbookSet.CalculateFull();
workbook.Save();
}
finally
{
workbook?.Close();
}
}
public static void Main(string[] Pages)
{
XmlReader reader = XmlReader.Create(Pages[0]);
DataContractSerializer serializer = new DataContractSerializer(typeof(Dictionary <string, string>));
Dictionary <string, string> list = (Dictionary <string, string>)serializer.ReadObject(reader);
reader.Close();
SpreadsheetGear.Windows.Forms.WorkbookView WV = new SpreadsheetGear.Windows.Forms.WorkbookView();
FeuerwehrCloud.Helper.Logger.WriteLine("| > [ExcelPrinter] *** Opening " + Pages[1]);
WV.ActiveWorkbook = SpreadsheetGear.Factory.GetWorkbookSet().Workbooks.Open(Pages[1]);
WV.GetLock();
SpreadsheetGear.IWorksheet WB = WV.ActiveWorkbook.Worksheets [0];
for (int x = 0; x < 25; x++)
{
for (int y = 0; y < 25; y++)
{
SpreadsheetGear.IRange IR = WB.Cells [x, y];
string CValue = IR.Text;
try {
if (CValue == "#EINSATZORT#")
{
IR.Value = list["EinsatzOrt"];
}
} catch (Exception e1) {
}
try {
if (CValue == "#EINSATZNR#")
{
IR.Value = list["EinsatzNr"];
}
} catch (Exception e1) {
}
try {
if (CValue == "#EINSATZSTRASSE#")
{
IR.Value = list["EinsatzStrasse"];
}
} catch (Exception e1) {
}
try {
if (CValue == "#EINSATZABSCHNITT#")
{
IR.Value = list["EinsatzAbschnitt"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#EINSATZKREUZUNG#")
{
IR.Value = list["EinsatzKreuzung"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#EINSATZOBJEKT#")
{
IR.Value = list["EinsatzObjekt"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#EINSATZBMERKUNG#")
{
IR.Value = list["EinsatzBemerkung"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#EINSATZPRIORITAET#")
{
IR.Value = list["EinsatzPrioritaet"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#EINSATZSTICHWORT#")
{
IR.Value = list["EinsatzStichwort"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#EINSATZSCHLAGWORT#")
{
IR.Value = list["EinsatzSchlagwort"];
}
} catch (Exception e1) {
}
try {
if (IR.Value == "#DATUM#")
{
IR.Value = System.DateTime.Now.ToString("d");
}
} catch (Exception e1) {
}
}
}
WB.PageSetup.LeftMargin = 0;
WB.PageSetup.RightMargin = 0;
WB.PageSetup.TopMargin = 0;
WB.PageSetup.BottomMargin = 0;
WB.PageSetup.PaperSize = SpreadsheetGear.PaperSize.A4;
WB.PageSetup.FitToPagesWide = 1;
WV.ReleaseLock();
WV.Print(false);
WV.GetLock();
WV.ActiveWorkbook.Close();
}
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);
}
}
//end getfromfile
/// <summary>
/// return datatable of origin names when there is a start range but no end range
/// iterate through from start cell and end when an empty cell is reached
/// </summary>
/// <param name="dir">location of excel worknook</param>
/// <param name="comapnygroup">identifies worksheet to use depending on company</param>
/// <param name="xlsheet">worksheet to use within workbbok</param>
/// <param name="startrange">start cell on worksheet colunm/row value e.g. A1 or A1:A50</param>
/// <returns>indexed datatable of items</returns>
public static DataTable getfromfile(string dir, string companygroup, string xlsheet, string xlstart)
{
//*****
//100212 check against company id, if no file just use "officepricer" prefix
//namecustomcontroller _name = new namecustomcontroller();
//string _cg = _name.get_company_group(companyid);
string _source = companygroup != "0" ? get_latest_pricer(dir, companygroup) : get_latest_pricer(dir); //find latest upload of pricer
//****
string _copy = companygroup.ToString() + "_" + DateTime.Now.ToString("ddMMyyHHmmss") + ".xls";
fso_copy_file(dir + _source, dir + _copy);
SpreadsheetGear.IWorkbook _wb = SpreadsheetGear.Factory.GetWorkbook(HttpContext.Current.Server.MapPath(dir + _copy));
//disable password protection for now
_wb.Unprotect("Trueblue");
//is this necessary in web apps - seems primarily for threading in winforms
//_wb.WorkbookSet.GetLock(); //acquire lock
//get range
SpreadsheetGear.IRange _range = _wb.Worksheets[xlsheet].Range[xlstart];
//_range.Replace("", "*", SpreadsheetGear.LookAt.Whole, SpreadsheetGear.SearchOrder.ByColumns, false);
DataTable _dt = new DataTable();
//_dt =_range.GetDataTable(SpreadsheetGear.Data.GetDataFlags.NoColumnHeaders);
//add a datatabe name and a column name so e.g. can data bind to combo
_dt.Columns.Add("item_index", typeof(int));
_dt.Columns.Add("item", typeof(string));
_dt.TableName = "item_table";
_dt.PrimaryKey = new DataColumn[] { _dt.Columns["Item_index"] };
//_dt.Columns[0].ColumnName = "item";
Boolean _end = false;
int _ix = 0;
while (_end == false)
{
DataRow _dr = _dt.NewRow();
string _s = _range.Rows[_ix, 0].Value != null ? _range.Rows[_ix, 0].Value.ToString() : null;
if (!string.IsNullOrEmpty(_s))
{
_ix += 1;
_dr["item"] = _s;
_dr["item_index"] = _ix;
_dt.Rows.Add(_dr);
}
else
{
_end = true;
}
}
_wb.Protect("Trueblue", true, true);
_wb.Close();
//_wb.WorkbookSet.ReleaseLock();
//delete teporary copy
fso_kill_file(dir + _copy);
return(_dt);
}
override protected void SetReportView()
{
if (this.InvokeRequired)
{
this.BeginInvoke(new SetReportViewHandler(SetReportView));
return;
}
ReportView.GetLock();
ReportView.ActiveWorksheet.Cells.Clear();
ReportView.ReleaseLock();
SampleStatReportSettings srs = SampleStatReportSettings.Instance;
if (_gc == null)
{
return;
}
//清空
if (_gc.Count == 0)
{
return;
}
//按样品排序
EncodeCollection <CheckOrder> gc = _gc.Copy();
Dictionary <string, System.Reflection.PropertyInfo> dicFieldPI = new Dictionary <string, System.Reflection.PropertyInfo>();
//DataFieldAttribute fBrand = new DataFieldAttribute { ColumnName = "brand", Description = "牌号" };
//head
List <string> listHead = new List <string>();
foreach (DataFieldAttribute field in srs.HeadFields)
{
if (!dicFieldPI.ContainsKey(field.Description))
{
dicFieldPI.Add(field.Description, FieldManager.FieldToProperty(typeof(CheckOrder), field.Description));
listHead.Add(field.Description);
}
}
//关键字分类
Dictionary <string, BrandStat> diccon2 = new Dictionary <string, BrandStat>();
Dictionary <string, BrandStat> diccon = new Dictionary <string, BrandStat>();
StringBuilder sbHead = new StringBuilder();
List <string> lsHead = new List <string>();
foreach (CheckOrder cggroup in gc)
{
if (sbHead.Length > 0)
{
sbHead.Remove(0, sbHead.Length);
}
foreach (DataFieldAttribute field in srs.HeadFields)
{
object value = null;
if (dicFieldPI[field.Description] != null)
{
value = dicFieldPI[field.Description].GetValue(cggroup, null);
}
string valuestr = "";
if (value != null)
{
valuestr = value.ToString().Trim();
}
sbHead.Append(valuestr + " ");
}
if (!diccon2.ContainsKey(sbHead.ToString()))
{
diccon2.Add(sbHead.ToString(), new BrandStat());
diccon2[sbHead.ToString()].Groups.Add(cggroup);
lsHead.Add(sbHead.ToString());
}
else
{
diccon2[sbHead.ToString()].Groups.Add(cggroup);
}
}
lsHead.Sort();
for (int i = 0; i < lsHead.Count; i++)
{
diccon.Add(lsHead[i], diccon2[lsHead[i]]);
}
if (diccon.Count == 0)
{
return;
}
foreach (BrandStat bs in diccon.Values)
{
bs.Init();
}
ReportView.GetLock();
ReportView.ActiveWorksheet.Cells.Clear();
ReportView.ActiveWorksheet.Name = "质检工单统计表";
SpreadsheetGear.IRange irange = ReportView.ActiveWorksheet.Cells;
irange.HorizontalAlignment = SpreadsheetGear.HAlign.Left;
irange.VerticalAlignment = SpreadsheetGear.VAlign.Center;
irange[0, 0].Value = "质检工单统计表";
irange[0, 0, 0, listHead.Count + srs.StatFields.Count].MergeCells = true;
irange[0, 0, 0, listHead.Count + srs.StatFields.Count].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
for (int i = 0; i < listHead.Count; i++)
{
irange[1, i].Value = listHead[i];//string.Join(" ", listHead.ToArray());
}
//irange[1, listHead.Count].Value = "项目";
irange[0, 0].Font.Bold = false;
irange[0, 0].Font.Size = 18;
SpreadsheetGear.IBorders border = irange[0, 0, 0, srs.StatFields.Count + listHead.Count].Borders;
border[SpreadsheetGear.BordersIndex.EdgeBottom].Weight = SpreadsheetGear.BorderWeight.Thin;
border = irange[1, 0, 1, srs.StatFields.Count + listHead.Count].Borders;
border[SpreadsheetGear.BordersIndex.EdgeBottom].Weight = SpreadsheetGear.BorderWeight.Thin;
irange[1, 0, 1, srs.StatFields.Count + listHead.Count].Font.Bold = false;
irange[1, 0, 1, srs.StatFields.Count + listHead.Count].Font.Size = 14;
int HeadCount = listHead.Count - 1;
if (HeadCount < 0)
{
HeadCount = 0;
}
for (int l = 0; l < srs.StatFields.Count; l++)
{
irange[1, HeadCount + l + 1].Value = srs.StatFields[l].Description;
irange[1, HeadCount + l + 1].Font.Bold = false;
}
ReportView.ReleaseLock();
int newrow = 2;
// List<BrandStat> listBS = new List<BrandStat>(diccon.Values);
List <string> listBS = new List <string>(diccon.Keys);
listBS.Sort();
foreach (string key in listBS)
{
BrandStat bs = diccon[key];
if (bAbort)
{
break;
}
if (bs.Stats.Count == 0)
{
continue;
}
Application.DoEvents();
ReportView.GetLock();
for (int l = 0; l < listHead.Count; l++)
{
FieldValue fv = FieldManager.GetFieldValue(bs.Groups[0], listHead[l]);
if (fv != null)
{
irange[newrow, l].Value = fv.GetSafeValue();
irange[newrow, l].Font.Bold = true;
}
}
// irange[newrow, 0].Value = key;// bs.GetBrand();
border = irange[newrow, 0, newrow, srs.StatFields.Count + HeadCount].Borders;
border[SpreadsheetGear.BordersIndex.EdgeTop].Weight = SpreadsheetGear.BorderWeight.Thin;
for (int l = 0; l < bs.Stats.Count; l++)
{
int row = 0;
for (int j = 0; j < srs.StatFields.Count; j++)
{
object data = bs.Stats[l].GetFieldValue(srs.StatFields[j]);
irange[newrow + row, j + HeadCount + 1].NumberFormat = SetNumberFormat(data.ToString());
irange[newrow + row, j + HeadCount + 1].Value = data;// bs.Stats[l].GetFieldValue(srs.StatFields[j]);
}
}
newrow += 1;
ReportView.ReleaseLock();
}
ReportView.GetLock();
ReportView.ActiveWorksheetWindowInfo.SplitRows = 2;
ReportView.ActiveWorksheetWindowInfo.FreezePanes = true;
ReportView.ActiveWorksheet.Cells.Columns.AutoFit();
ReportView.ReleaseLock();
}
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;
}
private static void ReadXlsxFileIntoList()
{
Console.WriteLine("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- started");
StaticVariable.ConsoleOutput.Add("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- started");
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Any line containing 'DefaultXX' will be ignored, as will all headers");
string[] worksheetsTypes = { Constants.Duration, Constants.Capped, Constants.Pulse };
List <string> workSheetsNotUsed = new List <string>();
List <string> discardedLines = new List <string>();
List <string> workSheetsUsed = new List <string>();
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(StaticVariable.InputFile);
foreach (string wksheet in worksheetsTypes)
{
try
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[wksheet];
SpreadsheetGear.IRange cells = worksheet.Cells;
workSheetsUsed.Add(wksheet);
}
catch (Exception)
{
workSheetsNotUsed.Add(wksheet);
}
}
foreach (string wksheet in workSheetsUsed)
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets[wksheet];
SpreadsheetGear.IRange cells = worksheet.Cells;
var currentColumn = 0;
for (currentColumn = 0; currentColumn < cells.ColumnCount; currentColumn++)
{
if (cells[0, currentColumn].Text.ToUpper().Equals(Constants.FinalColumnName))
{
currentColumn++;
break;
}
}
var maximumNumberOfColumns = currentColumn;
try
{
foreach (SpreadsheetGear.IRange row in worksheet.UsedRange.Rows)
{
StringBuilder sb = new StringBuilder();
for (int i = 0; i < maximumNumberOfColumns; i++)
{
sb.Append(row[0, i].Value + "\t"); //0.0400 being chopped to 0.04.
}
string sAdjustSb = sb.ToString().TrimEnd('\t');
if (sAdjustSb.Contains(";") && !DiscardHeaderLine(sAdjustSb))
{
discardedLines.Add("- " + sAdjustSb.Substring(0, sAdjustSb.IndexOf('\t')));
}
else if (!string.IsNullOrEmpty(sAdjustSb) && !DiscardHeaderLine(sAdjustSb))
{
ValidateData.CheckForCommasInLine(sAdjustSb);
StaticVariable.InputXlsxFileDetails.Add(ValidateData.CapitaliseWord(sAdjustSb));
}
}
}
catch (Exception e)
{
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Error in reading in XLSX line into list. Is there any data? ");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + e.Message);
}
}
workbook.Close();
if (workSheetsNotUsed.Any())
{
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
foreach (var entry in workSheetsNotUsed)
{
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry + " rates are not being used. Delete this worksheet");
}
}
foreach (var entry in workSheetsUsed)
{
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry + " rates are being used. ");
}
if (discardedLines.Any())
{
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile::ReadXLSXFileIntoList()");
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + "Customer destinations discarded.");
discardedLines.Sort();
foreach (var entry in discardedLines)
{
StaticVariable.ProgressDetails.Add(Constants.FiveSpacesPadding + entry);
}
}
StaticVariable.ProgressDetails.Add(Environment.NewLine + "ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList()-- completed");
Console.WriteLine("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- finished");
StaticVariable.ConsoleOutput.Add("ProcessInputXlsxFile".PadRight(30, '.') + "ReadXLSXFileIntoList() -- finished");
}
private void OutPutExcel_N4Nguphap(DataTable adtData)
{
string templatePath = Common.GetTemplate("N4Nguphap_テンプレート.xls");
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(templatePath);
try
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["N4Nguphap"];
SpreadsheetGear.IRange range = null;
//tb.Columns.Add("id");
//tb.Columns.Add("maucau");
//tb.Columns.Add("cachchia");
//tb.Columns.Add("ynghia");
//tb.Columns.Add("vidu");
string dataA, dataB, dataC, dataD, dataE;
string[] numofLineA, numofLineB, numofLineC, numofLineD, numofLineE;
for (int i = 0, addressY = 2, plus = 0; i < adtData.Rows.Count; i++, addressY++, plus = 0)
{
dataA = adtData.Rows[i]["id"].ToString();
dataB = adtData.Rows[i]["maucau"].ToString();
dataC = adtData.Rows[i]["cachchia"].ToString();
dataD = adtData.Rows[i]["ynghia"].ToString();
dataE = adtData.Rows[i]["vidu"].ToString();
numofLineA = dataA.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineB = dataB.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineC = dataC.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineD = dataD.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
numofLineE = dataE.Split(new string[] { "\n" }, StringSplitOptions.RemoveEmptyEntries);
for (int plusP = 0; plusP < numofLineA.Length; plusP++)
{
range = worksheet.Cells["A" + (addressY + plusP)];
range.Value = numofLineA[plusP];
}
for (int plusP = 0; plusP < numofLineB.Length; plusP++)
{
range = worksheet.Cells["B" + (addressY + plusP)];
range.Value = numofLineB[plusP];
}
for (int plusP = 0; plusP < numofLineC.Length; plusP++)
{
range = worksheet.Cells["C" + (addressY + plusP)];
range.Value = numofLineC[plusP];
}
for (int plusP = 0; plusP < numofLineD.Length; plusP++)
{
range = worksheet.Cells["D" + (addressY + plusP)];
range.Value = numofLineD[plusP];
}
for (int plusP = 0; plusP < numofLineE.Length; plusP++)
{
range = worksheet.Cells["E" + (addressY + plusP)];
range.Value = numofLineE[plusP];
}
plus = Math.Max(numofLineA.Length, numofLineB.Length);
plus = Math.Max(numofLineC.Length, plus);
plus = Math.Max(numofLineD.Length, plus);
plus = Math.Max(numofLineE.Length, plus);
addressY = addressY + plus - 1;
}
string outPath = "";
Common.SaveExcelTemplate(workbook, "N4文法", "xls", out outPath);
if (File.Exists(outPath))
{
System.Diagnostics.Process.Start(outPath);
}
}
catch (Exception)
{
throw;
}
finally
{
workbook.Close();
}
}
private void OutPutExcel_2000共通単語(DataTable adtData)
{
string templatePath = Common.GetTemplate("2000共通単語_テンプレート.xls");
SpreadsheetGear.IWorkbook workbook = SpreadsheetGear.Factory.GetWorkbook(templatePath);
try
{
SpreadsheetGear.IWorksheet worksheet = workbook.Worksheets["2000共通単語"];
SpreadsheetGear.IRange range = null;
for (int i = 0, addressY = 2; i < adtData.Rows.Count; i++, addressY++)
{
range = worksheet.Cells["A" + addressY];
range.Value = adtData.Rows[i]["id"];
range = worksheet.Cells["B" + addressY];
range.Value = adtData.Rows[i]["jp"];
if (adtData.Rows[i]["read"].ToString().Length > 0 || adtData.Rows[i]["tooltipText"].ToString().Length > 0)
{
range.AddComment(adtData.Rows[i]["read"].ToString().Length > 0 ? (adtData.Rows[i]["read"].ToString() + Environment.NewLine + adtData.Rows[i]["tooltipText"].ToString()) : adtData.Rows[i]["tooltipText"].ToString());
SpreadsheetGear.IComment icomment = range.Comment;
using (Graphics g = this.CreateGraphics())
{
string item = icomment.ToString();
SizeF sizeF = g.MeasureString(item, Font);
icomment.Shape.Width = sizeF.Width;
icomment.Shape.Height = sizeF.Height;
}
}
range = worksheet.Cells["C" + addressY];
range.Value = adtData.Rows[i]["read"];
range.WrapText = false;
range = worksheet.Cells["D" + addressY];
range.Value = adtData.Rows[i]["vi"];
range.WrapText = false;
range = worksheet.Cells["E" + addressY];
range.Value = adtData.Rows[i]["innerText"];
range.WrapText = false;
range = worksheet.Cells["F" + addressY];
range.Value = adtData.Rows[i]["outerHtml"];
range.WrapText = false;
range = worksheet.Cells["G" + addressY];
range.Value = adtData.Rows[i]["tooltipText"];
range.WrapText = false;
}
string outPath = "";
Common.SaveExcelTemplate(workbook, "2000共通単語", "xls", out outPath);
if (File.Exists(outPath))
{
System.Diagnostics.Process.Start(outPath);
}
}
catch (Exception)
{
throw;
}
finally
{
workbook.Close();
}
}
override protected void SetReportView()
{
if (this.InvokeRequired)
{
this.BeginInvoke(new SetReportViewHandler(SetReportView));
return;
}
ReportView.GetLock();
try
{
ReportView.ActiveWorksheet.Cells.Clear();
}
finally
{
ReportView.ReleaseLock();
}
SampleStatReportSettings srs = SampleStatReportSettings.Instance;
if (_gc == null)
{
return;
}
//清空
if (_gc.Count == 0)
{
return;
}
//按样品排序
EncodeCollection <CheckOrder> gc = _gc.Copy();
Dictionary <string, System.Reflection.PropertyInfo> dicFieldPI = new Dictionary <string, System.Reflection.PropertyInfo>();
//DataFieldAttribute fBrand = new DataFieldAttribute { ColumnName = "brand", Description = "牌号" };
//head
List <string> listHead = new List <string>();
foreach (DataFieldAttribute field in srs.HeadFields)
{
if (!dicFieldPI.ContainsKey(field.Description))
{
dicFieldPI.Add(field.Description, FieldManager.FieldToProperty(typeof(CheckOrder), field.Description));
listHead.Add(field.Description);
}
}
//关键字分类
Dictionary <string, BrandStat> diccon2 = new Dictionary <string, BrandStat>();
Dictionary <string, BrandStat> diccon = new Dictionary <string, BrandStat>();
StringBuilder sbHead = new StringBuilder();
List <string> lsHead = new List <string>();
foreach (CheckOrder cggroup in gc)
{
if (sbHead.Length > 0)
{
sbHead.Remove(0, sbHead.Length);
}
foreach (DataFieldAttribute field in srs.HeadFields)
{
object value = null;
if (dicFieldPI[field.Description] != null)
{
value = dicFieldPI[field.Description].GetValue(cggroup, null);
}
string valuestr = "";
if (value != null)
{
valuestr = value.ToString().Trim();
}
sbHead.Append(valuestr + " ");
}
if (!diccon2.ContainsKey(sbHead.ToString()))
{
diccon2.Add(sbHead.ToString(), new BrandStat());
diccon2[sbHead.ToString()].Groups.Add(cggroup);
lsHead.Add(sbHead.ToString());
}
else
{
diccon2[sbHead.ToString()].Groups.Add(cggroup);
}
}
lsHead.Sort();
for (int i = 0; i < lsHead.Count; i++)
{
diccon.Add(lsHead[i], diccon2[lsHead[i]]);
}
if (diccon.Count == 0)
{
return;
}
foreach (BrandStat bs in diccon.Values)
{
bs.Init();
}
//decisions
List <int> listDecision = new List <int>();
foreach (DataFieldAttribute field in srs.DecisionsFields)
{
//if (!listDecision.Contains(field.))
//{
// listDecision.Add(field.ColumnName);
//}
}
int colCount = listHead.Count + srs.StatFields.Count + listDecision.Count;
int HeadCount = listHead.Count - 1;
int tmpCol = HeadCount + srs.StatFields.Count + 1;
ReportView.GetLock();
ReportView.ActiveWorksheet.Cells.Clear();
ReportView.ActiveWorksheet.Name = "质检工单统计表";
SpreadsheetGear.IRange irange = ReportView.ActiveWorksheet.Cells;
SpreadsheetGear.IBorders border = null;
try
{
irange.HorizontalAlignment = SpreadsheetGear.HAlign.Left;
irange.VerticalAlignment = SpreadsheetGear.VAlign.Center;
irange[0, 0].Value = "中粮长城葡萄酒（烟台）有限公司";
irange[1, 0].Value = "质量检验数据统计分析表";
irange[2, 0].Value = string.Format("统计期间：{0} 至 {1}", gc[0].PlanCheckDate, gc[gc.Count - 1].PlanCheckDate);
for (int i = 0; i < 2; i++)
{
irange[i, 0, i, colCount].MergeCells = true;
irange[i, 0, i, colCount].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
irange[i, 0].Font.Bold = false;
irange[i, 0].Font.Size = 20;
}
for (int i = 0; i < listHead.Count; i++)
{
irange[3, i].Value = listHead[i];//string.Join(" ", listHead.ToArray());
irange[3, i, 4, i].MergeCells = true;
}
border = irange[2, 0, 2, colCount].Borders;
border[SpreadsheetGear.BordersIndex.EdgeBottom].Weight = SpreadsheetGear.BorderWeight.Thin;
border = irange[3, 0, 3, colCount].Borders;
border[SpreadsheetGear.BordersIndex.EdgeBottom].Weight = SpreadsheetGear.BorderWeight.Thin;
irange[3, 0, 3, colCount].Font.Bold = false;
irange[3, 0, 3, colCount].Font.Size = 14;
if (HeadCount < 0)
{
HeadCount = 0;
}
for (int l = 0; l < srs.StatFields.Count; l++)
{
irange[3, HeadCount + l + 1].Value = srs.StatFields[l].Description;
irange[3, HeadCount + l + 1].Font.Bold = false;
irange[3, HeadCount + l + 1, 4, HeadCount + l + 1].MergeCells = true;
}
//decisions
if (listDecision.Count > 0)
{
irange[3, tmpCol].Value = "使用决策情况统计";
irange[3, tmpCol, 3, colCount].HorizontalAlignment = SpreadsheetGear.HAlign.Center;
irange[3, tmpCol, 3, tmpCol + listDecision.Count].MergeCells = true;
irange[4, tmpCol].Value = "合格率%";
for (int i = 1; i <= listDecision.Count; i++)
{
irange[4, tmpCol + i].Value = listDecision[i - 1];
}
}
}
catch (Exception err)
{
ReturnValue.ShowMessage(err.Message);
return;
}
finally
{
ReportView.ReleaseLock();
}
int newrow = 5;
// List<BrandStat> listBS = new List<BrandStat>(diccon.Values);
List <string> listBS = new List <string>(diccon.Keys);
listBS.Sort();
foreach (string key in listBS)
{
BrandStat bs = diccon[key];
if (bAbort)
{
break;
}
if (bs.Stats.Count == 0)
{
continue;
}
Application.DoEvents();
ReportView.GetLock();
try
{
for (int l = 0; l < listHead.Count; l++)
{
FieldValue fv = FieldManager.GetFieldValue(bs.Groups[0], listHead[l]);
if (fv != null)
{
irange[newrow, l].Value = fv.GetSafeValue();
irange[newrow, l].Font.Bold = true;
}
}
// irange[newrow, 0].Value = key;// bs.GetBrand();
border = irange[newrow, 0, newrow, colCount].Borders;
border[SpreadsheetGear.BordersIndex.EdgeTop].Weight = SpreadsheetGear.BorderWeight.Thin;
for (int l = 0; l < bs.Stats.Count; l++)
{
for (int j = 0; j < srs.StatFields.Count; j++)
{
object data = bs.Stats[l].GetFieldValue(srs.StatFields[j]);
if (data != null)
{
irange[newrow, j + HeadCount + 1].NumberFormat = SetNumberFormat(data.ToString());
irange[newrow, j + HeadCount + 1].Value = data;// bs.Stats[l].GetFieldValue(srs.StatFields[j]);
}
}
irange[newrow, tmpCol].Value = bs.Stats[l].UseGoodRate;
for (int i = 1; i <= listDecision.Count; i++)
{
irange[newrow, tmpCol + i].Value = bs.Stats[l].UsageDecisionStat(listDecision[i - 1]);
}
}
newrow += 1;
}
catch (Exception err)
{
ReturnValue.ShowMessage(err.Message);
break;
}
finally
{
ReportView.ReleaseLock();
}
}
ReportView.GetLock();
try
{
irange[4, 0, newrow, colCount].HorizontalAlignment = SpreadsheetGear.HAlign.Right;
ReportView.ActiveWorksheetWindowInfo.SplitRows = 5;
ReportView.ActiveWorksheetWindowInfo.FreezePanes = true;
irange[3, 0, 5, colCount].Columns.AutoFit();
ReportView.ActiveWorksheet.Cells.Rows.AutoFit();
}
finally
{
ReportView.ReleaseLock();
}
}
