private void SetCellValue(object value, CellExportedEventArgs e, WorksheetCell cell, IMeasure measured) { if (e.Field.Name == "Item.ManageRequestStatus" || e.Field.Name == "Item.Request.ManageRequestStatus") { cell.Value = Expl_User_Journal_ManagePU_Request_List.StatusToString(Convert.ToInt16(value)); return; } ExportHelper.SetCellValue(cell, value, measured, e.Field.Tag, e.Field.Name); }
private static void GenerateFooterRow(Worksheet sheet) { WorksheetRow Row23 = sheet.Table.Rows.Add(); WorksheetCell cell = Row23.Cells.Add(); Row23.Height = 25; Row23.AutoFitHeight = false; cell = Row23.Cells.Add(); cell.StyleID = "s76"; cell.Data.Type = DataType.String; cell.Data.Text = "סה\"כ"; cell.Index = 5; cell.NamedCell.Add("Print_Area"); cell = Row23.Cells.Add(); cell.StyleID = "s77"; cell.Data.Type = DataType.Number; cell.Data.Text = "150.54000000000002"; cell.Formula = "=SUM(R[-21]C:R[-2]C)"; cell.NamedCell.Add("Print_Area"); }
public void setCell(int row, int col, object value, string fontName, int fontSize, bool isBold, bool isItalic) { try { while (EXsheet.Table.Rows.Count < row) { EXsheet.Table.Rows.Add(); } DataType tipo = getDataType(value); if (tipo == DataType.Number) { value = value.ToString().Replace(",", "."); } WorksheetCell _cell = new WorksheetCell(value.ToString(), DataType.String); _cell.Data.Type = tipo; EXsheet.Table.Rows[row - 1].Cells.Add(_cell); } catch (Exception e) { throw Carralero.ExceptionControler.getFullException(e); } }
private static object FlagToCell(enumPowerFlag flag, WorksheetCell cell) { //Color color; //if (flag.HasFlag(VALUES_FLAG_DB.DataNotComplete) || // (flag.HasFlag(VALUES_FLAG_DB.NotCorrect))) // color = Color.IndianRed; //else if (flag.HasFlag(VALUES_FLAG_DB.DataNotFull)) // color = Color.LightGray; //else // color = Color.LightGreen; cell.CellFormat.Alignment = HorizontalCellAlignment.Center; cell.CellFormat.VerticalAlignment = VerticalCellAlignment.Center; //cell.CellFormat.Fill = new CellFillPattern(new WorkbookColorInfo(color), new WorkbookColorInfo(color), FillPatternStyle.Solid); string result; if (GlobalTreeDictionary.PowerFlagDictionary.TryGetValue(flag, out result)) { return(result); } return(string.Empty); }
private void GenerateDataRows(Worksheet sheet) { String lastCategoryName = String.Empty; for (int i = 0; i < this.Items.Count; i++) { ShoplistItem currItem = this.Items[i]; bool addCategoryRow = lastCategoryName != currItem.Product.Category.Name; if (addCategoryRow) { WorksheetRow workSHeetCategoryRow = sheet.Table.Rows.Add(); workSHeetCategoryRow.Height = 15; workSHeetCategoryRow.AutoFitHeight = false; WorksheetCell cell = workSHeetCategoryRow.Cells.Add(); cell.StyleID = "s65"; cell.Data.Type = DataType.String; cell.Data.Text = currItem.Product.Category.Name; cell.NamedCell.Add("Print_Area"); cell = workSHeetCategoryRow.Cells.Add(); cell.StyleID = "s66"; cell.NamedCell.Add("Print_Area"); cell = workSHeetCategoryRow.Cells.Add(); cell.StyleID = "s66"; cell.NamedCell.Add("Print_Area"); cell = workSHeetCategoryRow.Cells.Add(); cell.StyleID = "s66"; cell.NamedCell.Add("Print_Area"); cell = workSHeetCategoryRow.Cells.Add(); cell.StyleID = "s66"; cell.NamedCell.Add("Print_Area"); cell = workSHeetCategoryRow.Cells.Add(); cell.StyleID = "s66"; cell.NamedCell.Add("Print_Area"); } WorksheetRow workSHeetDataRow = sheet.Table.Rows.Add(); workSHeetDataRow.Height = 15; workSHeetDataRow.AutoFitHeight = false; WorksheetCell workSHeetDatacell = workSHeetDataRow.Cells.Add(); workSHeetDatacell.StyleID = "s67"; workSHeetDatacell.NamedCell.Add("Print_Area"); workSHeetDatacell.StyleID = "s68"; workSHeetDatacell = workSHeetDataRow.Cells.Add(); workSHeetDatacell.Data.Type = DataType.String; workSHeetDatacell.Data.Text = currItem.Product.ProductName; workSHeetDatacell.NamedCell.Add("Print_Area"); workSHeetDatacell = workSHeetDataRow.Cells.Add(); workSHeetDatacell.StyleID = "s68"; workSHeetDatacell.Data.Type = DataType.Number; workSHeetDatacell.Data.Text = currItem.Quantity.ToString("N0"); workSHeetDatacell.NamedCell.Add("Print_Area"); workSHeetDatacell = workSHeetDataRow.Cells.Add(); workSHeetDatacell.StyleID = "s69"; workSHeetDatacell.Data.Type = DataType.Number; workSHeetDatacell.Data.Text = currItem.Product.Price.ToString(); workSHeetDatacell.NamedCell.Add("Print_Area"); workSHeetDatacell = workSHeetDataRow.Cells.Add(); workSHeetDatacell.StyleID = "s68"; workSHeetDatacell.Data.Type = DataType.String; workSHeetDatacell.Data.Text = currItem.Product.Notes; workSHeetDatacell.NamedCell.Add("Print_Area"); workSHeetDatacell = workSHeetDataRow.Cells.Add(); workSHeetDatacell.StyleID = "s70"; workSHeetDatacell.Data.Type = DataType.Number; workSHeetDatacell.Data.Text = "11.58"; workSHeetDatacell.Formula = "=RC[-3]*RC[-2]"; workSHeetDatacell.NamedCell.Add("Print_Area"); lastCategoryName = currItem.Product.Category.Name; } }
private void GenerateExcelReport(DataTable dtTable, DataTable dtLength, string strFilePath, string ReportName, string strFileName) { //Add a workbook CarlosAg.ExcelXmlWriter.Workbook book = new CarlosAg.ExcelXmlWriter.Workbook(); #region SheetProperties // Specify which Sheet should be opened and the size of window by default book.ExcelWorkbook.ActiveSheetIndex = 1; book.ExcelWorkbook.WindowTopX = 100; book.ExcelWorkbook.WindowTopY = 200; book.ExcelWorkbook.WindowHeight = 7000; book.ExcelWorkbook.WindowWidth = 8000; // Some optional properties of the Document book.Properties.Author = "Secure Proctor Report"; book.Properties.Title = "Excel Export"; book.Properties.Created = DateTime.Now; #endregion #region CellStyle WorksheetStyle CellStyle = book.Styles.Add("CellStyle"); CellStyle.Font.FontName = "Arial"; CellStyle.Font.Size = 11; CellStyle.Font.Color = "#000000"; CellStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center; CellStyle.Alignment.Vertical = StyleVerticalAlignment.Center; CellStyle.Alignment.WrapText = false; CellStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1); CellStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1); CellStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1); CellStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1); CellStyle.NumberFormat = "@"; #endregion #region HeaderStyle WorksheetStyle HeaderStyle = book.Styles.Add("HeaderStyle"); HeaderStyle.Font.FontName = "Arial"; HeaderStyle.Font.Size = 11; HeaderStyle.Font.Bold = true; HeaderStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center; HeaderStyle.Alignment.WrapText = false; HeaderStyle.Font.Color = "#000000"; HeaderStyle.Interior.Color = "#99CCFF"; HeaderStyle.Interior.Pattern = StyleInteriorPattern.Solid; HeaderStyle.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1); HeaderStyle.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1); HeaderStyle.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1); HeaderStyle.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1); #endregion // Add a Worksheet with some data Worksheet sheet = book.Worksheets.Add(ReportName); //Add row with some properties WorksheetRow row = sheet.Table.Rows.Add(); row.Index = 0; row.Height = 35; row.AutoFitHeight = true; //Set worksheet column names int ColNumber = 0; int ColumnLength = 100; int colSeedValue = 7; foreach (DataColumn dc in dtTable.Columns) { ///* SET COLUMN WIDTH - START */ if (dtLength.Rows[0][ColNumber] != null) { if (dtLength.Rows[0][ColNumber].ToString().Length != 0) { if (Convert.ToInt32(dtLength.Rows[0][ColNumber].ToString()) > dc.ColumnName.Length) { ColumnLength = Convert.ToInt32(dtLength.Rows[0][ColNumber].ToString()) * colSeedValue; } else { ColumnLength = dc.ColumnName.Length * colSeedValue; } } else { ColumnLength = dc.ColumnName.Length * colSeedValue; } } else { ColumnLength = dc.ColumnName.Length * colSeedValue; } ///* SET COLUMN WIDTH - END */ //ColumnLength = 100; sheet.Table.Columns.Add(new WorksheetColumn(ColumnLength)); WorksheetCell wcHeader = new WorksheetCell(dc.ColumnName, CarlosAg.ExcelXmlWriter.DataType.String, "HeaderStyle"); row.Cells.Add(wcHeader); ColNumber++; } foreach (DataRow dtrrow in dtTable.Rows) { //Add row to the excel sheet row = sheet.Table.Rows.Add(); //row.Height = 30; row.AutoFitHeight = true; //Loop through each column foreach (DataColumn col in dtTable.Columns) { WorksheetCell wc = new WorksheetCell(dtrrow[col.ColumnName].ToString(), DataType.String, "CellStyle"); row.Cells.Add(wc); } } //Save the work book book.Save(strFilePath); DownloadFile(strFilePath, strFileName); }
public static string GetText(this WorksheetCell cell) { return(GetText(cell.Value)); }
private ResMsg CreateExcel(DataTable dt) { ResMsg msg = new ResMsg(false, ""); if ((dt != null) && (dt.Rows.Count > 0)) { DataTable table = dt; int count = table.Columns.Count; Workbook workbook = new Workbook { ExcelWorkbook = { ActiveSheetIndex = 1, WindowTopX = 0, WindowTopY = 0, WindowHeight = 0x1b58, WindowWidth = 0x1f40 }, Properties = { Author = "PSWeb", Title = "User Information List", Created = DateTime.Now } }; WorksheetStyle style = workbook.Styles.Add("HeaderStyle"); style.Font.FontName = "宋体"; style.Font.Size = 14; style.Font.Bold = true; style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Font.Color = "Black"; style = workbook.Styles.Add("ColumnCaptionStyle"); style.Font.FontName = "宋体"; style.Font.Bold = true; style.Font.Size = 11; style = workbook.Styles.Add("Default"); style.Font.FontName = "宋体"; style.Font.Size = 10; Worksheet worksheet = workbook.Worksheets.Add("用户信息列表"); for (int i = 0; i < table.Columns.Count; i++) { if (i == (table.Columns.Count - 1)) { worksheet.Table.Columns.Add(new WorksheetColumn(130)); } else { worksheet.Table.Columns.Add(new WorksheetColumn(0x41)); } } WorksheetCell cell = worksheet.Table.Rows.Add().Cells.Add("用户信息列表"); cell.MergeAcross = count - 1; cell.StyleID = "HeaderStyle"; WorksheetRow row = worksheet.Table.Rows.Add(); for (int j = 0; j < table.Columns.Count; j++) { row.Cells.Add(table.Columns[j].Caption).StyleID = "ColumnCaptionStyle"; } for (int k = 0; k < table.Rows.Count; k++) { row = worksheet.Table.Rows.Add(); for (int m = 0; m < table.Columns.Count; m++) { row.Cells.Add(table.Rows[k][m].ToString()).StyleID = "Default"; } } try { string str = DateTime.Now.Ticks.ToString() + ".xls"; workbook.Save(this.context.Server.MapPath(".").Replace("WebServices", "WebAdmin") + @"\" + str); CommonUtil.RemoveFiles(this.context.Server.MapPath("."), ".xls"); msg.Result = true; msg.Message = str; } catch (Exception exception) { msg.Message = exception.Message; } } return(msg); }
public static void GenerateExcel(String OutputFileName, DeivationList lstDeviation) { Workbook workbook = new Workbook(); workbook.ExcelWorkbook.ActiveSheetIndex = (3); workbook.ExcelWorkbook.WindowTopX = (100); workbook.ExcelWorkbook.WindowTopY = (200); workbook.ExcelWorkbook.WindowHeight = (7000); workbook.ExcelWorkbook.WindowWidth = (10000); workbook.Properties.Author = ("CarlosAg"); workbook.Properties.Title = "DeviationApp"; workbook.Properties.Created = (DateTime.Now); workbook.Styles.Add("HeaderStyle").Font.FontName = ("Cordia New"); WorksheetStyle worksheetStyle1 = workbook.Styles.Add("Default"); worksheetStyle1.Font.FontName = "Cordia New"; worksheetStyle1.Font.Size = 14; worksheetStyle1.Font.Bold = (true); worksheetStyle1.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1); worksheetStyle1.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1); worksheetStyle1.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1); worksheetStyle1.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1); Worksheet worksheet1 = workbook.Worksheets.Add("DeviationSheet"); worksheet1.Table.Columns.Add(new WorksheetColumn(120)); //1 worksheet1.Table.Columns.Add(new WorksheetColumn(120)); //2 worksheet1.Table.Columns.Add(new WorksheetColumn(120)); //3 /// Header /// WorksheetRow worksheetRow1 = worksheet1.Table.Rows.Add(); worksheetRow1.Cells.Add(""); worksheetRow1 = worksheet1.Table.Rows.Add(); worksheetRow1.Cells.Add("N (seq)"); worksheetRow1.Cells.Add("X (input)"); worksheetRow1.Cells.Add("(Xi - Xavg)^2"); WorksheetStyle style33 = workbook.Styles.Add("s31"); style33.Font.FontName = "Cordia New"; style33.Font.Size = 14; style33.Font.Bold = false; style33.Interior.Pattern = StyleInteriorPattern.Solid; style33.Borders.Add(StylePosition.Bottom, LineStyleOption.Continuous, 1); style33.Borders.Add(StylePosition.Left, LineStyleOption.Continuous, 1); style33.Borders.Add(StylePosition.Right, LineStyleOption.Continuous, 1); style33.Borders.Add(StylePosition.Top, LineStyleOption.Continuous, 1); WorksheetStyle style = workbook.Styles.Add("s32"); style.Font.FontName = "Cordia New"; style.Font.Size = 14; style.Font.Bold = false; style.NumberFormat = "#,###,##0"; WorksheetCell worksheetCell = null; foreach (DeviationEntity dnEntity in lstDeviation.ListDeviationEntity) { worksheetRow1 = worksheet1.Table.Rows.Add(); worksheetCell = new WorksheetCell(dnEntity.NumSeq.ToString(), DataType.Number, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetCell = new WorksheetCell(dnEntity.NumX.ToString(), DataType.Number, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetCell = new WorksheetCell(dnEntity.NumDeviation.ToString(), DataType.Number, "s31"); worksheetRow1.Cells.Add(worksheetCell); } worksheetRow1 = worksheet1.Table.Rows.Add(); worksheetCell = new WorksheetCell("Result Summary", DataType.String, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetCell = new WorksheetCell("Sum (X) : " + lstDeviation.SumX, DataType.String, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetCell = new WorksheetCell("Sum (Deviation) : " + lstDeviation.SumDeviation, DataType.String, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetRow1 = worksheet1.Table.Rows.Add(); worksheetCell = new WorksheetCell("", DataType.String, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetCell = new WorksheetCell("Result Power Deviation", DataType.String, "s31"); worksheetRow1.Cells.Add(worksheetCell); worksheetCell = new WorksheetCell(lstDeviation.DeviationPow1.ToString(), DataType.Number, "s31"); worksheetRow1.Cells.Add(worksheetCell); workbook.Save(OutputFileName); Process.Start(OutputFileName); Console.WriteLine("Please Enter Finish Program (Y/N)"); Console.Read(); }
public static void SetCellValue(WorksheetCell cell, object value, IMeasure measured = null, object tag = null, string fieldName = null) { var @switch = new TypeSwitch <object>() .Case((int i) => { //cell.CellFormat.Alignment = HorizontalCellAlignment.Right; return(i); }) .Case((double d) => { if (tag != null && string.Equals(tag.ToString(), "UseMeasureConverter")) { if (measured != null && measured.UseBindedConverter) { var ud = (double)measured.UsedUnitDigit / (double)measured.SelectedUnitDigit; if (ud != 1) { d = Math.Round(d, 5) * ud; } else { d = Math.Round(d, 5); } } else { d = Math.Round(d, 7); } } if ((d % 1) != 0) { cell.CellFormat.FormatString = CommonEx.ExcelformatString; } else { cell.CellFormat.FormatString = _intFormatted; } return(d); }) .Case((DateTime dt) => { string formatString; if (tag != null) { var templateName = tag.ToString(); switch (templateName) { case ConstantHelper.HalfHourstoRangeTimeTemplateName: case ConstantHelper.HourstoRangeTimeTemplateName: formatString = "HH:mm"; var dtTo = dt.TimeOfDay.Add(templateName == ConstantHelper.HourstoRangeTimeTemplateName ? GlobalEnums.Delta60Minute : GlobalEnums.Delta30Minute); formatString += "\"-" + dtTo.Hours.ToString("00") + ":" + dtTo.Minutes.ToString("00") + "\""; if (dt.TimeOfDay == TimeSpan.Zero) { formatString = "dd.MM.yyyy " + formatString; } break; case ConstantHelper.DateTemplateName: formatString = "dd.MM.yyyy"; break; default: formatString = "dd.MM.yyyy HH:mm"; break; } } else { formatString = "dd.MM.yyyy HH:mm"; } cell.CellFormat.FormatString = formatString; return(dt); }) .Case((TPS_isCA ps) => { // if (ps.IsCA) { return(EnumClientServiceDictionary.ContrPSList[ps.PS_ID]); } return(EnumClientServiceDictionary.DetailPSList[ps.PS_ID].HierarchyObject); }) .Case((TFormulaValidateByOneValues ti) => { // if (ti.TI_Ch_ID.IsCA) { return(EnumClientServiceDictionary.TICAList[ti.TI_Ch_ID.TI_ID]); } return(EnumClientServiceDictionary.TIHierarchyList[ti.TI_Ch_ID.TI_ID]); }) .Case((TGroupTPResult tpResult) => { if (string.IsNullOrEmpty(fieldName)) { return(null); } switch (fieldName) { case "PowerMax": { //Здась надо учесть IsAbsolutely return(tpResult.PowerMax); } case "PowerFactUsed": return(tpResult.PowerFactUsed != null ? tpResult.PowerFactUsed.F_VALUE : 0); case "PowerMaxReserved": return(tpResult.PowerMaxReserved); case "GroupTP_MAX_VALUE.EventDateTime": return(tpResult.TotalPowerInfoInSystemOperatorsHours != null && tpResult.TotalPowerInfoInSystemOperatorsHours.GroupTP_MAX_VALUE != null ? tpResult.TotalPowerInfoInSystemOperatorsHours.GroupTP_MAX_VALUE.EventDateTime as DateTime? : null); } return(null); }) .Case((enumTypeHierarchy th) => { string hierarchyName; if (GlobalEnumsDictionary.EnumTypeHierarchyName.TryGetValue(th, out hierarchyName)) { return(hierarchyName); } return(th.ToString()); }) .Case((bool b) => { if (b) { cell.CellFormat.Alignment = HorizontalCellAlignment.Center; return(char.ConvertFromUtf32(0xF0FE)); } return(string.Empty); }) .Case((EnumForecastObjectFixedType foft) => { string resource; return(!ServiceDictionary.ForecastObjectFixedTypeDictionary.TryGetValue(foft, out resource) ? string.Empty : resource); }) .Case((TTariffPeriodID ch) => { var chName = string.Empty; if (!ChannelFactory.ChanelTypeNameFSK.TryGetValue(ch.ChannelType, out chName)) { chName = " <?>"; } return(ChannelDictionaryClass.MakeZoneNamePrefix(ch.TI_ID, ch.ChannelType, ch.StartDateTime, ch.FinishDateTime) + chName); }) .Case((IFValue fv) => { double v; if (fieldName == "Value_Section" || fieldName == "Value_CA") { v = fv.F_VALUE; } else { ServiceReference.ARM_20_Service.EnumUnitDigit?unitDigit; if (measured != null && measured.UseBindedConverter) { var ud = (double)measured.UsedUnitDigit / (double)measured.SelectedUnitDigit; if (ud != 1) { v = Math.Round(fv.F_VALUE, 5) * ud; } else { v = Math.Round(fv.F_VALUE, 5); } unitDigit = measured.SelectedUnitDigit; } else { v = Math.Round(fv.F_VALUE, 7); unitDigit = null; } //Если нужно форматировать, то раскоментировать здесь, но почему то тормоза if ((v % 1) != 0) { cell.CellFormat.FormatString = CommonEx.GetExcelFormatString(unitDigit); } else { cell.CellFormat.FormatString = _intFormatted; } Color color; if (fv.F_FLAG.HasFlag(ServiceReference.ARM_20_Service.VALUES_FLAG_DB.DataNotComplete) || (fv.F_FLAG.HasFlag(ServiceReference.ARM_20_Service.VALUES_FLAG_DB.NotCorrect))) { color = Color.IndianRed; } else if (fv.F_FLAG.HasFlag(ServiceReference.ARM_20_Service.VALUES_FLAG_DB.DataNotFull)) { color = Color.LightGray; } else { color = Color.LightGreen; } //if (cell.Comment == null) cell.Comment = new WorksheetCellComment(); //cell.Comment.Text = new FormattedString(TVALUES_DB.FLAG_to_String(valuesDb.F_FLAG, ",\n")); cell.CellFormat.Alignment = HorizontalCellAlignment.Right; cell.CellFormat.Fill = new CellFillPattern(new WorkbookColorInfo(color), new WorkbookColorInfo(color), FillPatternStyle.Solid); } return(v); }) .Case((IForecastValue fVal) => { double v; if (measured != null && measured.UseBindedConverter) { v = fVal.F_VALUE / (double)measured.SelectedUnitDigit; } else { v = fVal.F_VALUE; } cell.CellFormat.FormatString = CommonEx.ExcelformatString; Color color; if (fVal.F_FLAG.HasFlag(Proryv.Servers.Forecast.Client_ServiceReference.ForecastServiceReference.EnumForecastValueValid.DataNotComplete) || (fVal.F_FLAG.HasFlag(Proryv.Servers.Forecast.Client_ServiceReference.ForecastServiceReference.EnumForecastValueValid.NotCorrect))) { color = Color.IndianRed; } else if (fVal.F_FLAG.HasFlag(Proryv.Servers.Forecast.Client_ServiceReference.ForecastServiceReference.EnumForecastValueValid.DataNotFull)) { color = Color.LightGray; } else { color = Color.LightGreen; } //if (cell.Comment == null) cell.Comment = new WorksheetCellComment(); //cell.Comment.Text = new FormattedString(TFORECAST_DB.ForecastFlagToString(fVal.F_FLAG, "\n")); cell.CellFormat.Alignment = HorizontalCellAlignment.Right; cell.CellFormat.Fill = new CellFillPattern(new WorkbookColorInfo(color), new WorkbookColorInfo(color), FillPatternStyle.Solid); //if (cell.Comment == null) cell.Comment = new WorksheetCellComment(); //cell.Comment.Text = new FormattedString(TFORECAST_DB.ForecastFlagToString(fVal.F_FLAG, "\n")); //cell.CellFormat.Alignment = HorizontalCellAlignment.Right; //cell.CellFormat.Fill = new CellFillPattern(new WorkbookColorInfo(color), new WorkbookColorInfo(color), FillPatternStyle.Solid); if ((v % 1) != 0) { cell.CellFormat.FormatString = CommonEx.ExcelformatString; } else { cell.CellFormat.FormatString = _intFormatted; } return(v); }) .Case((RequestStatus rs) => { cell.CellFormat.Alignment = HorizontalCellAlignment.Center; switch (rs) { case RequestStatus.Aborted: return("Отклонен"); case RequestStatus.Applied: return("Утвержден"); case RequestStatus.Created: return("На рассмотрении"); } return(string.Empty); //e.FormatSettings.HorizontalAlignment = HorizontalCellAlignment.Center; }) .Case((List <KeyValuePair <int, int> > list) => { var res = string.Empty; foreach (var l in list) { res += (res.Length > 0 ? ", " : "") + EnumClientServiceDictionary.TIHierarchyList[l.Value]; } return(res); }) .Case((enumChannelType ct) => { return(ChannelFactory.ChanelTypeNameFSK[(int)ct]); }) .Case((IFreeHierarchyObject fho) => { return(fho.Name); }) .Case((ServiceReference.ARM_20_Service.VALUES_FLAG_DB flag) => { return(FlagToCell(flag, cell)); }) .Case((enumPowerFlag flag) => { return(FlagToCell(flag, cell)); }) .Case((ulong l) => { var flag = (ServiceReference.ARM_20_Service.VALUES_FLAG_DB)l; //if (!Enum.IsDefined(typeof(VALUES_FLAG_DB), flag)) return l; return(FlagToCell(flag, cell)); }) .Case((IEnumerable enumerable) => { //var sb = new StringBuilder(); //foreach (var v in (IEnumerable)value) //{ // sb.Append(v).Append("\n"); //} //return sb.ToString(); return(string.Empty); }) .Case((ID_TypeHierarchy id) => { string un; if (id.ID > 0) { un = id.ID.ToString(); } else { un = id.StringId; } var hierObject = HierarchyObjectHelper.ToHierarchyObject(un, id.TypeHierarchy); if (hierObject != null) { return(hierObject.Name); } return(string.Empty); }) .Case((ObjectIdCollection ids) => { var result = new StringBuilder(); foreach (var id in ids.Source) { var hierObject = HierarchyObjectHelper.ToHierarchyObject(id.ID, id.TypeHierarchy); if (hierObject != null) { result.Append(hierObject.Name).Append(" ; "); } } return(result.ToString()); }) //.Case((ArchTechValue archTech) => //{ // double v = archTech.F_VALUE * 1000; // if (measured != null && measured.UseBindedConverter) // { // v = archTech.F_VALUE / (double)measured.SelectedUnitDigit; // } // else // { // v = archTech.F_VALUE; // } // if ((v % 1) != 0) // { // cell.CellFormat.FormatString = CommonEx.ExcelformatString; // } // else // { // cell.CellFormat.FormatString = _intFormatted; // } // Color color; // if (archTech.F_FLAG.HasFlag(EnumArchTechStatus.DataNotComplete) || // (archTech.F_FLAG.HasFlag(EnumArchTechStatus.NotCorrect))) // color = Color.IndianRed; // else if (archTech.F_FLAG.HasFlag(EnumArchTechStatus.DataNotFull)) // color = Color.LightGray; // else // color = Color.LightGreen; // cell.CellFormat.Alignment = HorizontalCellAlignment.Right; // cell.CellFormat.Fill = new CellFillPattern(new WorkbookColorInfo(color), new WorkbookColorInfo(color), FillPatternStyle.Solid); // return v; //}) .Case((long l) => { return(l); }); cell.Value = @switch.Switch(value); }
private static object FlagToCell(ServiceReference.ARM_20_Service.VALUES_FLAG_DB flag, WorksheetCell cell) { Color color; if (flag.HasFlag(ServiceReference.ARM_20_Service.VALUES_FLAG_DB.DataNotComplete) || (flag.HasFlag(ServiceReference.ARM_20_Service.VALUES_FLAG_DB.NotCorrect))) { color = Color.IndianRed; } else if (flag.HasFlag(ServiceReference.ARM_20_Service.VALUES_FLAG_DB.DataNotFull)) { color = Color.LightGray; } else { color = Color.LightGreen; } cell.CellFormat.Alignment = HorizontalCellAlignment.Center; cell.CellFormat.VerticalAlignment = VerticalCellAlignment.Center; cell.CellFormat.Fill = new CellFillPattern(new WorkbookColorInfo(color), new WorkbookColorInfo(color), FillPatternStyle.Solid); return(ServiceReference.ARM_20_Service.TVALUES_DB.FLAG_to_String(flag, ",\n")); }
public static void ToExcel(this IEnumerable list, string userName, string title, string filterflag, string fileFullName) { Workbook excelBook = new Workbook(); excelBook = WorkbookStyle(excelBook, userName, title); Worksheet sheet = excelBook.Worksheets.Add(DateTime.Now.ToString("yyyy-MM-dd")); WorksheetRow row; WorksheetCell cell; IEnumerable <string> hiddenProperties = null; WorksheetRow headRow = sheet.Table.Rows.Add(); headRow.AutoFitHeight = false; headRow.Height = 30; WorksheetCell headCell = headRow.Cells.Add(title); headCell.StyleID = "TitleStyle"; int rowCount = 1; int cellCount = 0; foreach (var item in list) { row = sheet.Table.Rows.Add(); row.AutoFitHeight = false; var properties = item.GetType().GetProperties().ToList(); if (rowCount == 1) { hiddenProperties = properties.Where(p => p.GetValue(item, null).ToString() == filterflag).Select(p => p.Name); cellCount = properties.Count - hiddenProperties.Count() - 1; } foreach (var p in properties) { if (hiddenProperties != null && hiddenProperties.Contains(p.Name)) { continue; } var value = p.GetValue(item, null); cell = row.Cells.Add(value == null ? string.Empty : value.ToString().Replace("<b>", "").Replace("</b>", ""), DataType.String, "DefaultStyle"); if (value != null && value.ToString().IndexOf("<b>") >= 0) { cell.StyleID = "HeaderStyle"; //如果列表内容有加粗标志<b> 则导出也相应加粗 } if (rowCount == 1) { cell.StyleID = "HeaderStyle"; //第一行 表头字体加粗 } } rowCount++; } headCell.MergeAcross = cellCount; using (FileStream ms = new FileStream(fileFullName, FileMode.Create)) { excelBook.Save(ms); } }
private void RecurseRender(Element elmt, decimal top, decimal left) { top += elmt.Top; left += elmt.Left; if (elmt is TextElement) { TextElement te = elmt as TextElement; TextStyle ts = null; if (te.Style is TextStyle) { ts = (TextStyle)te.Style; } Int32 row = _rows.FindIndex(delegate(decimal d) { return(d == top); }); Int32 col; if (ts != null && ts.TextAlign == RdlEngine.Style.TextAlignEnum.Right) { col = _cols.FindIndex(delegate(decimal d) { return(d == left + elmt.Width); }) - 1; } else { col = _cols.FindIndex(delegate(decimal d) { return(d == left); }); } WorksheetCell cell = _ws.Rows[row].Cells[col]; cell.Value = te.Text; if (ts != null) { int fontHeight = (int)(ts.FontSize.points * _fontHeight); if (ts.BorderStyle != null && ts.BorderStyle != null) { BorderStyle bs = ts.BorderStyle; cell.CellFormat.TopBorderStyle = ExcelBorderStyleFromRdlBorderStyle(bs.Top); cell.CellFormat.BottomBorderStyle = ExcelBorderStyleFromRdlBorderStyle(bs.Bottom); cell.CellFormat.LeftBorderStyle = ExcelBorderStyleFromRdlBorderStyle(bs.Left); cell.CellFormat.RightBorderStyle = ExcelBorderStyleFromRdlBorderStyle(bs.Right); } if (ts.BorderColor != null) { cell.CellFormat.TopBorderColor = System.Drawing.Color.FromName(ts.BorderColor.Top); cell.CellFormat.BottomBorderColor = System.Drawing.Color.FromName(ts.BorderColor.Bottom); cell.CellFormat.LeftBorderColor = System.Drawing.Color.FromName(ts.BorderColor.Left); cell.CellFormat.RightBorderColor = System.Drawing.Color.FromName(ts.BorderColor.Right); } cell.CellFormat.Font.Name = ts.FontFamily; cell.CellFormat.Font.Height = fontHeight; RdlEngine.Style.FontStyleEnum style = ts.FontStyle; if (style == RdlEngine.Style.FontStyleEnum.Italic) { cell.CellFormat.Font.Italic = ExcelDefaultableBoolean.True; } switch (ts.FontWeight) { case RdlEngine.Style.FontWeightEnum.Bold: case RdlEngine.Style.FontWeightEnum.Bolder: case RdlEngine.Style.FontWeightEnum._600: case RdlEngine.Style.FontWeightEnum._700: case RdlEngine.Style.FontWeightEnum._800: case RdlEngine.Style.FontWeightEnum._900: cell.CellFormat.Font.Bold = ExcelDefaultableBoolean.True; break; } cell.CellFormat.Font.Color = System.Drawing.Color.FromName(ts.Color); cell.CellFormat.Alignment = (HorizontalCellAlignment)Enum.Parse(typeof(HorizontalCellAlignment), ts.TextAlign.ToString(), true); if (ts.BackgroundColor != null) { cell.CellFormat.FillPatternForegroundColor = System.Drawing.Color.FromName(ts.BackgroundColor); cell.CellFormat.FillPatternBackgroundColor = System.Drawing.Color.White; cell.CellFormat.FillPattern = FillPatternStyle.Solid; } } } if (elmt is Container) { foreach (Element child in ((Container)elmt).Children) { RecurseRender(child, top, left); } } }
public static string Generate(DataSet data) { CultureInfo oldCurrentCulture = System.Threading.Thread.CurrentThread.CurrentCulture; CultureInfo oldCurrentUICulture = System.Threading.Thread.CurrentThread.CurrentUICulture; // L'appli est passée en culture en-US pour la gestion correcte des ToString dans le fichier Excel System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); System.Threading.Thread.CurrentThread.CurrentUICulture = new System.Globalization.CultureInfo("en-US"); Workbook rapport = new Workbook(); Worksheet rapportSheet = null; int tbCount = 0; WorksheetStyle headerStyle = rapport.Styles.Add("HeaderStyle"); headerStyle.Font.Bold = true; headerStyle.Alignment.Horizontal = StyleHorizontalAlignment.Center; headerStyle.Interior.Color = "#DEDEDE"; headerStyle.Interior.Pattern = StyleInteriorPattern.Solid; WorksheetStyle dateStyle = rapport.Styles.Add("dateStyle"); dateStyle.NumberFormat = FormatSettings.DateTime; string ExcelFileName = string.Format("{1}{0:yyyy_MM_dd}.xml", DateTime.Now, RapportSettings.OutFilePrefix); foreach (DataTable curTable in data.Tables) { if (null == rapportSheet && false == RapportSettings.BySheetOutput) { rapportSheet = rapport.Worksheets.Add(string.Format("{1}{0:dd-MM-yyyy}", DateTime.Now, RapportSettings.SheetNamePrefix)); } else if (true == RapportSettings.BySheetOutput) { rapportSheet = rapport.Worksheets.Add(string.Format("{1}{0}", ++tbCount, RapportSettings.SheetNamePrefix)); } WorksheetRow headerRow = rapportSheet.Table.Rows.Add(); foreach (DataColumn curColumn in curTable.Columns) { WorksheetCell curCell = headerRow.Cells.Add(curColumn.ColumnName); curCell.StyleID = "HeaderStyle"; } foreach (DataRow curRow in curTable.Rows) { WorksheetRow row = rapportSheet.Table.Rows.Add(); foreach (DataColumn curColumn in curTable.Columns) { object curRawData = curRow[curColumn]; string curData = string.Empty; if (curRawData != null && curRawData != DBNull.Value) { curData = curRawData.ToString(); } if (curRawData is bool) { curData = Convert.ToBoolean(curRawData) ? "1" : "0"; } else if (curRawData is DateTime) { curData = Convert.ToDateTime(curRawData).ToString("s"); } WorksheetCell curCell = row.Cells.Add(curData); if (curRawData is int || curRawData is short || curRawData is byte || curRawData is long || curRawData is float || curRawData is double || curRawData is decimal) { curCell.Data.Type = DataType.Number; } else if (curRawData is bool) { curCell.Data.Type = DataType.Boolean; } else if (curRawData is DateTime) { curCell.Data.Type = DataType.DateTime; curCell.StyleID = "dateStyle"; } } } if (false == RapportSettings.BySheetOutput) { rapportSheet.Table.Rows.Add(); } else { rapportSheet.AutoFilter.Range = "R1C1:R1C" + curTable.Columns.Count; } } rapport.Save(ExcelFileName); // L'appli est passée en culture en-US pour la gestion correcte des ToString dans le fichier Excel System.Threading.Thread.CurrentThread.CurrentCulture = oldCurrentCulture; System.Threading.Thread.CurrentThread.CurrentUICulture = oldCurrentUICulture; return(ExcelFileName); }
/// <summary> /// Exports a grid field caption (header or footer) to the specified worksheet cell. /// /// </summary> protected virtual void ExportGridFieldCaption(WebDataGrid grid, GridFieldCaption caption, WorksheetRow wsRow, WorksheetCell wsCell, int rowIndex, int columnIndex, string gridCssClass, string captionRowCssClass, bool isHeader, int rowSpan, int colSpan, int colOffset) { WorksheetMergedCellsRegion mergedCellsRegion = rowSpan > 1 || colSpan > 1 ? wsRow.Worksheet.MergedCellsRegions.Add(rowIndex, columnIndex + colOffset, rowIndex + rowSpan - 1, columnIndex + colOffset + colSpan - 1) : (WorksheetMergedCellsRegion)null; ExcelCellExportingEventArgs e1 = new ExcelCellExportingEventArgs(wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false); this.OnCellExporting(e1); if (e1.Cancel) return; GridFieldCaptionExportingEventArgs e2 = new GridFieldCaptionExportingEventArgs(caption, wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false); this.OnGridFieldCaptionExporting(e2); if (e2.Cancel) return; if (mergedCellsRegion != null) mergedCellsRegion.Value = (object)caption.Text; else wsCell.Value = (object)caption.Text; if (this.EnableStylesExport) { string styleClassString = grid.RunBot.StyleBot.GetStyleClassString((int)caption.Role, caption.CssClassResolved); if (caption.OwnerField is GroupField && caption.Role == WebDataGridRoles.HeaderCaption) styleClassString = grid.RunBot.StyleBot.GetStyleClassString(10, styleClassString); CssSelector selector1 = new CssSelector(); selector1.AddClasses(string.Format("{0} {1} {2}", (object)gridCssClass, (object)captionRowCssClass, (object)styleClassString)); CssStyle styleObject1 = this._currentStyleSheet.GetStyleObject(selector1); this.ApplyCellFormatFromStyle(wsCell.Worksheet.Workbook, wsCell.CellFormat, styleObject1, grid); if (mergedCellsRegion != null) this.ApplyCellFormatFromStyle(mergedCellsRegion.Worksheet.Workbook, mergedCellsRegion.CellFormat, styleObject1, grid); CssSelector selector2 = new CssSelector(); selector2.AddClasses(styleClassString); CssStyle styleObject2 = this._currentStyleSheet.GetStyleObject(selector2); this.ApplyCellBorderFromStyle(wsCell.CellFormat, styleObject2); if (mergedCellsRegion != null) this.ApplyCellBorderFromStyle(mergedCellsRegion.CellFormat, styleObject2); } if (wsCell.Value != null) { this.SetColumnWidth(wsCell.Worksheet.Columns[wsCell.ColumnIndex], wsCell); if (wsCell.Value.ToString().Contains(Environment.NewLine)) wsCell.CellFormat.WrapText = ExcelDefaultableBoolean.True; } if (mergedCellsRegion != null && mergedCellsRegion.Value != null && mergedCellsRegion.Value.ToString().Contains(Environment.NewLine)) mergedCellsRegion.CellFormat.WrapText = ExcelDefaultableBoolean.True; this.OnGridFieldCaptionExported(new GridFieldCaptionExportedEventArgs(caption, wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false)); this.OnCellExported(new ExcelCellExportedEventArgs(wsRow.Worksheet, wsCell, rowIndex, columnIndex, wsRow.OutlineLevel, isHeader, !isHeader, false)); }
/// <summary> /// Sets the width of the columns in the exported document. /// /// </summary> /// /// <remarks> /// If the contents of the current cell are wider than the /// contents of previous cells in the same column, then the column's width /// is expanded to accomodate the widest cell. /// /// </remarks> private void SetColumnWidth(WorksheetColumn wsCol, WorksheetCell cell) { string str = cell.Value != null ? cell.Value.ToString() : string.Empty; if (str == string.Empty) return; double a = !str.Contains(Environment.NewLine) ? this.GetWidthOfText(str) : this.GetWidestLineLength(str); if (cell.CellFormat.Font.Height > 0 && cell.CellFormat.Font.Height != 240) a *= (double)cell.CellFormat.Font.Height / 240.0; if (cell.CellFormat.Font.Bold == ExcelDefaultableBoolean.True) a *= 1.1; double num = (double)(int)Math.Ceiling(a); if (num <= (double)wsCol.Width) return; wsCol.Width = (int)num; }
private void excelButton_Click(object sender, EventArgs e) { Workbook book = new Workbook(); // Some optional properties of the Document book.Properties.Author = "Yerevan State University, Faculty of Informatics and Applied Mathematics," + "Chair of Programming and Information Technologies"; book.Properties.Title = "Value Table"; book.Properties.Created = DateTime.Now; // Add some styles to the Workbook WorksheetStyle style = book.Styles.Add("HeaderStyle"); style.Font.FontName = "TimesNewRoman"; style.Font.Size = 14; style.Font.Bold = true; style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Font.Color = "White"; style.Interior.Color = "Gray"; style.Interior.Pattern = StyleInteriorPattern.Solid; style = book.Styles.Add("ColumnNames"); style.Font.FontName = "TimesNewRoman"; style.Font.Size = 12; style.Font.Bold = true; style.Alignment.Horizontal = StyleHorizontalAlignment.Center; style.Font.Color = "White"; style.Interior.Color = "Gray"; style.Interior.Pattern = StyleInteriorPattern.Solid; style = book.Styles.Add("Default"); style.Font.FontName = "TimesNewRoman"; style.Font.Size = 11; style.Alignment.Horizontal = StyleHorizontalAlignment.Center; Worksheet sheet = book.Worksheets.Add("Value Table"); sheet.Table.Columns.Add(new WorksheetColumn(220)); sheet.Table.Columns.Add(new WorksheetColumn(220)); WorksheetRow row = sheet.Table.Rows.Add(); WorksheetCell generationCell = new WorksheetCell(this.generationCmbBox.Text, "HeaderStyle"); generationCell.Comment.Data.Text = GenerationParameters.Text; row.Cells.Add(generationCell); generationCell.MergeAcross = 1; row = sheet.Table.Rows.Add(); WorksheetCell optionCell = new WorksheetCell(this.optionCmbBox.Text, "HeaderStyle"); row.Cells.Add(optionCell); optionCell.Comment.Data.Text = OptionNameLabel.Text; optionCell.MergeAcross = 1; if (this.currentResult.type == StAnalyzeType.Local) { row = sheet.Table.Rows.Add(); WorksheetCell approximationCell = new WorksheetCell(this.apprLabel.Text + " - " + this.currentResult.approximationType.ToString() , "HeaderStyle"); row.Cells.Add(approximationCell); approximationCell.MergeAcross = 1; } row = sheet.Table.Rows.Add(); WorksheetCell realizationCountCell = new WorksheetCell(this.realizationCountLabel.Text + " = " + this.realizationCountTxt.Text, "HeaderStyle"); row.Cells.Add(realizationCountCell); realizationCountCell.MergeAcross = 1; row = sheet.Table.Rows.Add(); row.Cells.Add(this.ValuesGrd.Columns[0].HeaderText, DataType.String, "ColumnNames"); row.Cells.Add(this.ValuesGrd.Columns[1].HeaderText, DataType.String, "ColumnNames"); // Generate values for (int i = 0; i < this.ValuesGrd.Rows.Count; i++) { row = sheet.Table.Rows.Add(); row.Cells.Add(new WorksheetCell(this.ValuesGrd.Rows[i].Cells[0].Value.ToString(), DataType.String, "Default")); row.Cells.Add(new WorksheetCell(this.ValuesGrd.Rows[i].Cells[1].Value.ToString(), DataType.String, "Default")); } saveFileDialog.FileName = "ValueTable.xls"; if (this.saveFileDialog.ShowDialog() == DialogResult.OK) { book.Save(this.saveFileDialog.FileName); } }
void OnCellExported(object sender, CellExportedEventArgs e) { var value = e.Value; if (value == null) { return; } var exporter = sender as ProryvDataPresenterExcelExporter; var measured = exporter != null ? exporter.Measured : null; if (value is string) { FormatExportValue(e, measured); return; } WorksheetRow row = null; try { row = e.CurrentWorksheet.Rows[e.CurrentRowIndex]; } catch { } if (row == null) { return; } WorksheetCell cell = null; try { cell = row.Cells[e.CurrentColumnIndex]; } catch {} if (cell == null) { return; } //var value = e.Value; System.Windows.Style cellStyle = e.Field.Settings.EditorStyle; if (cellStyle != null) { var cSetter = cellStyle.Setters.FirstOrDefault(s => s is Setter && (s as Setter).Property.Name.Equals(TextEditorBase.ValueToDisplayTextConverterProperty.Name)) as Setter; if (cSetter != null) { var c = cSetter.Value as IValueConverter; if (c != null) { cell.Value = c.Convert(value, null, null, null); return; } //e.FormatSettings.FontColor = (fgSetter.Value as SolidColorBrush).Color; //e.FormatSettings.FillPatternForegroundColor = (bgSetter.Value as SolidColorBrush).Color; } } //var measured = e.Record.DataPresenter.FindParent<IMeasure>(); SetCellValue(value, e, cell, measured); }