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