internal void Import(XmlReader reader) { foreach (XmlReaderAttributeItem xa in reader.GetAttributes()) { switch (xa.LocalName) { case "Color": { Color = XmlStyle.ExcelFormatToColor(xa.Value); break; } case "PatternColor": { PatternColor = XmlStyle.ExcelFormatToColor(xa.Value); break; } case "Pattern": { Pattern = ObjectExtensions.ParseEnum <Pattern>(xa.Value); break; } } } }
internal void Export(XmlWriter writer) { // Font writer.WriteStartElement("Font"); writer.WriteAttributeString("ss", "FontName", null, Name); if (Size != 0) { writer.WriteAttributeString("ss", "Size", null, Size.ToString( CultureInfo.InvariantCulture)); } // Color writer.WriteAttributeString("ss", "Color", null, XmlStyle.ColorToExcelFormat(Color)); // Bold? if (Bold) { writer.WriteAttributeString("ss", "Bold", null, "1"); } // Italic? if (Italic) { writer.WriteAttributeString("ss", "Italic", null, "1"); } // Underline? if (Underline) { writer.WriteAttributeString("ss", "Underline", null, "Single"); } if (Strikeout) { writer.WriteAttributeString("ss", "Strikeout", null, "1"); } // Font end writer.WriteEndElement(); }
internal object GetCellStyleProperty(StylePropertyAccessor getDelegate) { if (GetParentBook() == null) { return(getDelegate(Parent.FirstCell())); } XmlStyle style = GetParentBook().GetStyleByID(Parent.StyleID); return(getDelegate(style)); }
private void ImportStyles(XmlReader reader) { while (reader.Read() && !(reader.Name == "Styles" && reader.NodeType == XmlNodeType.EndElement)) { XmlStyle style = XmlStyle.Import(reader); if (style != null) { Styles.Add(style); } } }
private void Initialize() { Properties = new DocumentProperties(); Styles = new List<XmlStyle>(); _Worksheets = new List<Worksheet>(); NamedRanges = new List<NamedRange>(); XmlStyle style = new XmlStyle(); style.ID = "Default"; style.Alignment.Vertical = VerticalAlignment.Bottom; Styles.Add(style); }
internal void SetCellStyleProperty(StylePropertyAccessor setDelegate) { if (GetParentBook() == null) { Parent.IterateAndApply(cell => setDelegate(cell)); } else { XmlStyle style = new XmlStyle(GetParentBook().GetStyleByID(Parent.StyleID)); setDelegate(style); Parent.StyleID = GetParentBook().AddStyle(style); } }
private void ExportBorder(XmlWriter writer, string border) { writer.WriteStartElement("Border"); writer.WriteAttributeString("ss", "Position", null, border); writer.WriteAttributeString("ss", "LineStyle", null, LineStyle.ToString()); writer.WriteAttributeString("ss", "Weight", null, Weight.ToString( CultureInfo.InvariantCulture)); if (Color != Color.Black) { writer.WriteAttributeString("ss", "Color", null, XmlStyle.ColorToExcelFormat(Color)); } writer.WriteEndElement(); }
private void Initialize() { Properties = new DocumentProperties(); Styles = new List <XmlStyle>(); _Worksheets = new List <Worksheet>(); NamedRanges = new List <NamedRange>(); XmlStyle style = new XmlStyle(); style.ID = "Default"; style.Alignment.Vertical = VerticalAlignment.Bottom; Styles.Add(style); }
/// <summary> /// Creates a new instance from another instance of XmlStyle /// </summary> /// <param name="style">Instance to copy</param> public XmlStyle(XmlStyle style) { if (style == null) { Initialize(); SetDefaults(); return; } ID = ""; _Font = new FontOptions(style._Font); _Interior = new InteriorOptions(style._Interior); _Alignment = new AlignmentOptions(style._Alignment); _Border = new BorderOptions(style._Border); DisplayFormat = style.DisplayFormat; }
internal bool CheckForMatch(XmlStyle style) { if (style == null) { return(false); } if (_Font.CheckForMatch(style._Font) && _Alignment.CheckForMatch(style._Alignment) && _Interior.CheckForMatch(style._Interior) && _Border.CheckForMatch(style._Border) && DisplayFormat == style.DisplayFormat) { return(true); } return(false); }
internal string AddStyle(XmlStyle style) { XmlStyle oldStyle = FindStyle(style); if (oldStyle == null) { int iterator = Styles.Count; style.ID = String.Format(CultureInfo.InvariantCulture, "S{0:00}", iterator++); while (HasStyleID(style.ID)) { style.ID = String.Format(CultureInfo.InvariantCulture, "S{0:00}", iterator++); } Styles.Add(style); return(style.ID); } return(oldStyle.ID); }
internal void Export(XmlWriter writer) { if (Color != Color.Empty || PatternColor != Color.Empty) { // Interior start writer.WriteStartElement("Interior"); if (Color != Color.Empty) { writer.WriteAttributeString("ss", "Color", null, XmlStyle.ColorToExcelFormat(Color)); } if (PatternColor != Color.Empty) { writer.WriteAttributeString("ss", "PatternColor", null, XmlStyle.ColorToExcelFormat(PatternColor)); } writer.WriteAttributeString("ss", "Pattern", null, Pattern.ToString()); // Interior end writer.WriteEndElement(); } }
internal bool CheckForMatch(XmlStyle style) { if (style == null) return false; if (_Font.CheckForMatch(style._Font) && _Alignment.CheckForMatch(style._Alignment) && _Interior.CheckForMatch(style._Interior) && _Border.CheckForMatch(style._Border) && DisplayFormat == style.DisplayFormat) { return true; } return false; }
internal static XmlStyle Import(XmlReader reader) { XmlStyle style = new XmlStyle(); bool isEmpty = reader.IsEmptyElement; XmlReaderAttributeItem xa = reader.GetSingleAttribute("ID"); if (xa != null) style.ID = xa.Value; if (isEmpty) return xa == null ? null : style; while (reader.Read() && !(reader.Name == "Style" && reader.NodeType == XmlNodeType.EndElement)) { if (reader.NodeType == XmlNodeType.Element) { switch (reader.Name) { case "Font": { style._Font.Import(reader); break; } case "Alignment": { style._Alignment.Import(reader); break; } case "Interior": { style._Interior.Import(reader); break; } case "Borders": { style._Border.Import(reader); break; } case "NumberFormat": { XmlReaderAttributeItem nfAttr = reader.GetSingleAttribute("Format"); if (nfAttr != null) { string format = nfAttr.Value; switch (format) { case "Short Date": { style.DisplayFormat = DisplayFormatType.ShortDate; break; } case "General Date": { style.DisplayFormat = DisplayFormatType.GeneralDate; break; } case "@": { style.DisplayFormat = DisplayFormatType.Text; break; } default: { if (format == DateTimeFormatInfo.CurrentInfo.LongDatePattern) style.DisplayFormat = DisplayFormatType.LongDate; string timeFormat = DateTimeFormatInfo.CurrentInfo.LongTimePattern; if (timeFormat.Contains("t")) timeFormat = timeFormat.Replace("t", "AM/PM"); if (timeFormat.Contains("tt")) timeFormat = timeFormat.Replace("tt", "AM/PM"); if (format == timeFormat) style.DisplayFormat = DisplayFormatType.Time; try { style.DisplayFormat = ObjectExtensions.ParseEnum<DisplayFormatType>(format); } catch (ArgumentException) { if (format.IsNullOrEmpty()) style.DisplayFormat = DisplayFormatType.None; else { style.DisplayFormat = DisplayFormatType.Custom; style.CustomFormatString = format; } } break; } } } break; } } } } return style; }
internal string AddStyle(XmlStyle style) { XmlStyle oldStyle = FindStyle(style); if (oldStyle == null) { int iterator = Styles.Count; style.ID = String.Format(CultureInfo.InvariantCulture, "S{0:00}", iterator++); while (HasStyleID(style.ID)) style.ID = String.Format(CultureInfo.InvariantCulture, "S{0:00}", iterator++); Styles.Add(style); return style.ID; } return oldStyle.ID; }
internal void Import(XmlReader reader) { foreach (XmlReaderAttributeItem xa in reader.GetAttributes()) { switch (xa.LocalName) { case "FontName": { Name = xa.Value; break; } case "Size": { int i; if (xa.Value.ParseToInt(out i)) { Size = i; } break; } case "Color": { Color = XmlStyle.ExcelFormatToColor(xa.Value); break; } case "Bold": { Bold = xa.Value == "1" ? true : false; break; } case "Italic": { Italic = xa.Value == "1" ? true : false; break; } case "Underline": { Underline = xa.Value == "Single" ? true : false; break; } case "Strikeout": { Strikeout = xa.Value == "1" ? true : false; break; } } } }
internal static XmlStyle Import(XmlReader reader) { XmlStyle style = new XmlStyle(); bool isEmpty = reader.IsEmptyElement; XmlReaderAttributeItem xa = reader.GetSingleAttribute("ID"); if (xa != null) { style.ID = xa.Value; } if (isEmpty) { return(xa == null ? null : style); } while (reader.Read() && !(reader.Name == "Style" && reader.NodeType == XmlNodeType.EndElement)) { if (reader.NodeType == XmlNodeType.Element) { switch (reader.Name) { case "Font": { style._Font.Import(reader); break; } case "Alignment": { style._Alignment.Import(reader); break; } case "Interior": { style._Interior.Import(reader); break; } case "Borders": { style._Border.Import(reader); break; } case "NumberFormat": { XmlReaderAttributeItem nfAttr = reader.GetSingleAttribute("Format"); if (nfAttr != null) { string format = nfAttr.Value; switch (format) { case "Short Date": { style.DisplayFormat = DisplayFormatType.ShortDate; break; } case "General Date": { style.DisplayFormat = DisplayFormatType.GeneralDate; break; } case "@": { style.DisplayFormat = DisplayFormatType.Text; break; } default: { if (format == DateTimeFormatInfo.CurrentInfo.LongDatePattern) { style.DisplayFormat = DisplayFormatType.LongDate; } string timeFormat = DateTimeFormatInfo.CurrentInfo.LongTimePattern; if (timeFormat.Contains("t")) { timeFormat = timeFormat.Replace("t", "AM/PM"); } if (timeFormat.Contains("tt")) { timeFormat = timeFormat.Replace("tt", "AM/PM"); } if (format == timeFormat) { style.DisplayFormat = DisplayFormatType.Time; } try { style.DisplayFormat = ObjectExtensions.ParseEnum <DisplayFormatType>(format); } catch (ArgumentException) { if (format.IsNullOrEmpty()) { style.DisplayFormat = DisplayFormatType.None; } else { style.DisplayFormat = DisplayFormatType.Custom; style.CustomFormatString = format; } } break; } } } break; } } } } return(style); }
/// <summary> /// 頁籤=[報表] 欄位[報表類型]=彙總,按下Button[匯出]鈕 /// </summary> private void but_TAB3_Print_Total() { try { #region 檢查條件 BCO.CAACommon CAAComm = new BCO.CAACommon(); ArrayList arl_Check_Condition = Check_Condition("TAB 報表"); #region 如果檢查有誤,則Return if (arl_Check_Condition[1].ToString() != string.Empty) { #region 錯誤訊息 this.ErrorMsgLabel.Text = arl_Check_Condition[1].ToString(); #endregion #region Focus欄位 if (arl_Check_Condition[0].ToString() != string.Empty) { string s_ScriptManager_Script = CAAComm.ToMakeUp_SetFocus_Script(arl_Check_Condition[0].ToString(), true); ScriptManager.RegisterStartupScript(this.up_Msg, typeof(UpdatePanel), "CAA181", s_ScriptManager_Script, true); } #endregion return; } #endregion #endregion #region 傳入參數 ParameterList.Clear(); ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_B.Text, "string", false));//[結帳年月]起 20110520修改為起迄 ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_E.Text, "string", false));//[結帳年月]迄 ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_ACCT_B.Text, "string", false));//[結帳年月-財會]起 20110520修改為起迄 ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_CLOSE_MONTH_ACCT_E.Text, "string", false));//[結帳年月-財會]迄 ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_PROFIT_NO_B.Text, "string", false));//[利潤中心]起 ParameterList.Add(CAAComm.GetValueSetParameter(this.slp_3_PROFIT_NO_E.Text, "string", false));//[利潤中心]迄 ParameterList.Add(CAAComm.GetValueSetParameter(Session["UID"].ToString(), "string", false));//V_LOG_UID #endregion #region 連接資料庫 DataSet ds_Return = new DataSet(); BCO.MaintainDiscRecord bco = new BCO.MaintainDiscRecord(ConntionDB); ds_Return = bco.QUERY_CAA18_REPORT_SUMMARY(ParameterList); #endregion #region 檢查回傳資料 if (ds_Return.Tables["SUMMARY"].Rows.Count == 0) { this.ErrorMsgLabel.Text = "查無資料"; return; } #endregion #region 組合匯出的Excel #region 設定變數 ExcelXmlWorkbook exl_WorkSheet = new ExcelXmlWorkbook(); Worksheet sheet = exl_WorkSheet[0]; sheet.Name = "折讓入帳彙總表"; #region 設定長和寬 int i_Vertical = 0;//直的有幾個欄位(依據資料庫[利潤中心]) int i_Horizontal = 0;//橫的有幾個欄位(依據資料庫[型式]) int i_Vertical_Fix = 3;//直的固定欄位[型式、稅別、合計] int i_Horizontal_Fix = 4;//橫的固定欄位[標題抬頭、利潤中心抬頭、合計上方要空一行、合計] i_Vertical = ds_Return.Tables["PROFIT"].Rows.Count + i_Vertical_Fix; i_Horizontal = (ds_Return.Tables["DISC_TYPE"].Rows.Count * 3) + i_Horizontal_Fix; #endregion #endregion #region 設定格式 #region 設定欄位寬度 for (int i_Style = 0; i_Style < i_Vertical; i_Style++) { sheet.Columns(i_Style).Width = 100; } #endregion #region 設定合併儲存格 Range rang_1 = new Range(sheet[0, 0], sheet[i_Vertical - 1, 0]); rang_1.Merge(); #endregion #region 設定Style XmlStyle style_1 = new XmlStyle(); style_1.Alignment.Vertical = VerticalAlignment.Center; style_1.Border.LineStyle = Borderline.Continuous; style_1.Border.Color = System.Drawing.Color.Black; style_1.Border.Sides = BorderSides.All; style_1.Border.Weight = 1; style_1.Font.Size = 10; style_1.Font.Name = "新細明體"; XmlStyle style_2 = new XmlStyle(); style_2.Alignment.Vertical = VerticalAlignment.Center; style_2.Alignment.Horizontal = HorizontalAlignment.Center; style_2.Border.LineStyle = Borderline.Continuous; style_2.Border.Color = System.Drawing.Color.Black; style_2.Border.Sides = BorderSides.All; style_2.Border.Weight = 1; style_2.Font.Size = 10; style_2.Font.Name = "新細明體"; for (int x = 0; x < i_Vertical; x++) { for (int y = 0; y < i_Horizontal; y++) { if (y != 0) { sheet[x, y].Style = style_1; } } } #endregion #endregion #region 組合匯出資料 for (int i = 0; i < i_Horizontal; i++) { switch (i) { #region 橫的第一行 case 0: sheet[0, 0].Value = this.slp_3_CLOSE_MONTH_ACCT_B.Text == string.Empty ? "折讓入帳彙總表" : this.slp_3_CLOSE_MONTH_ACCT_B.Text + "-" + this.slp_3_CLOSE_MONTH_ACCT_E.Text + "月折讓入帳彙總表"; sheet[0, 0].Style = style_2; break; #endregion #region 橫的第二行 case 1: sheet[0, 1].Value = string.Empty; sheet[1, 1].Value = string.Empty; for (int i_case_1 = 0; i_case_1 < ds_Return.Tables["PROFIT"].Rows.Count; i_case_1++) { sheet[i_case_1 + 2, 1].Value = ds_Return.Tables["PROFIT"].Rows[i_case_1]["PROFIT_NAME"].ToString(); } sheet[i_Vertical - 1, 1].Value = "合計"; break; #endregion #region 其它行 default: switch (i_Horizontal - i) { #region 橫的倒數第二行(合計上面一行要空白) case 2: sheet[0, i].Value = string.Empty; break; #endregion #region 橫的最後一行(合計那一行) case 1: sheet[0, i].Value = "合計"; for (int i_case_last = 0; i_case_last < ds_Return.Tables["PROFIT"].Rows.Count; i_case_last++) { sheet[i_case_last + 2, i_Horizontal - 1].Value = FormulaHelper.Formula("SUM", new Range(sheet[i_case_last + 2, 0], sheet[i_case_last + 2, i_Horizontal - 2])); } sheet[i_Vertical - 1, i_Horizontal - 1].Value = FormulaHelper.Formula("SUM", new Range(sheet[i_Vertical - 1, 0], sheet[i_Vertical - 1, i_Horizontal - 2])); break; #endregion #region 橫的其他行 default: #region 設定變數 int i_Get_Disc_Type = (i - 2) / 3; int i_Get_Tax_Type = (i - 2) % 3; string s_FilterExpression = string.Empty;//篩選條件 string s_DISC_SOURCE = string.Empty;//來源(1=系統、2=人工) string s_DISC_TYPE = string.Empty;//型式 string s_TAX_TYPE = string.Empty;//稅別 string s_PROFIT_NO = string.Empty;//利潤中心 #endregion #region 第1欄(型式) sheet[0, i].Value = ds_Return.Tables["DISC_TYPE"].Rows[i_Get_Disc_Type]["DISC_TYPE_NAME"].ToString(); #endregion #region 第2欄(稅別) switch (i_Get_Tax_Type) { case 0: sheet[1, i].Value = "應稅"; break; case 1: sheet[1, i].Value = "稅額"; break; case 2: sheet[1, i].Value = "免稅"; break; } #endregion #region 其他資料欄 for (int i_case_default = 0; i_case_default < ds_Return.Tables["PROFIT"].Rows.Count; i_case_default++) { #region 設定[來源][型式][稅別][利潤中心]參數 //[來源][型式] switch (i_Get_Disc_Type) { case 0://第1個一定是系統-一般 s_DISC_SOURCE = "1";//來源(1=系統、2=人工) s_DISC_TYPE = "1";//型式=1=一般 break; case 1://第2個一定是人工-一般 s_DISC_SOURCE = "2";//來源(1=系統、2=人工) s_DISC_TYPE = "1";//型式=1=一般 break; default: s_DISC_SOURCE = "2";//來源(1=系統、2=人工) s_DISC_TYPE = ds_Return.Tables["DISC_TYPE"].Rows[i_Get_Disc_Type]["DISC_TYPE_NAME"].ToString(); break; } //[稅別] switch (i_Get_Tax_Type) { case 0: s_TAX_TYPE = "0";//稅別=0=應稅,稅別=1=免稅 break; case 1: s_TAX_TYPE = "0";//稅別=0=應稅,稅別=1=免稅 break; case 2: s_TAX_TYPE = "1";//稅別=0=應稅,稅別=1=免稅 break; } //[利潤中心] s_PROFIT_NO = ds_Return.Tables["PROFIT"].Rows[i_case_default]["CODE"].ToString(); #endregion #region 組合查詢條件,查詢資料 s_FilterExpression = "DISC_SOURCE='" + s_DISC_SOURCE + "' and DISC_TYPE='" + s_DISC_TYPE + "' and TAX_TYPE='" + s_TAX_TYPE + "' and PROFIT_NO='" + s_PROFIT_NO + "'"; DataRow[] dr = ds_Return.Tables["SUMMARY"].Select(s_FilterExpression); #endregion #region 將資料寫入欄位 switch (dr.Length) { case 1: if (i_Get_Tax_Type != 1) { sheet[i_case_default + 2, i].Value = int.Parse(dr[0]["DISC_UAMT"].ToString()); } else { sheet[i_case_default + 2, i].Value = int.Parse(dr[0]["DISC_TAX"].ToString()); } break; default: sheet[i_case_default + 2, i].Value = 0; break; } #endregion } #endregion #region 合計欄(Excel右邊的[合計]欄) sheet[i_Vertical - 1, i].Value = FormulaHelper.Formula("SUM", new Range(sheet[0, i], sheet[i_Vertical - 2, i])); #endregion break; #endregion } break; #endregion } } #endregion #region 匯出Excel string s_FileName = HttpUtility.UrlEncode("折讓入帳彙總表.XLS", System.Text.Encoding.UTF8);//設定輸出檔名 Response.Charset = "big5"; Response.ContentType = "application/x-excel;charset='utf-8'"; Response.AddHeader("content-disposition", "attachment; filename=" + s_FileName);//excel檔名 System.IO.MemoryStream stream = new System.IO.MemoryStream(); exl_WorkSheet.Export(stream); if (stream == null || stream.Length == 0) { return; } stream.Flush(); stream.Position = 0; System.IO.StreamReader sr = new System.IO.StreamReader(stream); string producedExcel = sr.ReadToEnd(); stream.Close(); Response.Write(producedExcel); Response.Flush(); Response.End(); #endregion #endregion } catch (Exception ex) { WaringLogProcess(ex.Message); this.ErrorMsgLabel.Text = ex.Message; } }
internal XmlStyle FindStyle(XmlStyle style) { return Styles.Find(xs => xs.CheckForMatch(style)); }
private void ImportTable(XmlReader reader) { if (reader.IsEmptyElement) { return; } int column = 0; while (reader.Read() && !(reader.Name == "Table" && reader.NodeType == XmlNodeType.EndElement)) { if (reader.NodeType == XmlNodeType.Element) { switch (reader.Name) { case "Column": { double width = 0; bool hidden = false; int span = 1; XmlStyle style = null; foreach (XmlReaderAttributeItem xa in reader.GetAttributes()) { if (xa.LocalName == "Width" && xa.HasValue) { double d; if (xa.Value.ParseToInt(out d)) { width = d; } } if (xa.LocalName == "Hidden" && xa.HasValue) { hidden = xa.Value == "1"; } if (xa.LocalName == "Index" && xa.HasValue) { xa.Value.ParseToInt(out column); } if (xa.LocalName == "Span" && xa.HasValue) { xa.Value.ParseToInt(out span); } if (xa.LocalName == "StyleID" && xa.HasValue) { style = ParentBook.GetStyleByID(xa.Value); } } for (int i = 1; i <= span; i++) { Columns(column).Width = width; Columns(column).Hidden = hidden; if (style != null) { Columns(column).Style = style; } column++; } break; } case "Row": { ImportRow(reader); break; } } } } }
private void ImportCell(XmlReader reader, Row row) { bool isEmpty = reader.IsEmptyElement; int cellIndex = row._Cells.Count; int mergeDown = 0; int mergeAcross = 0; XmlStyle style = null; string formula = ""; string reference = ""; foreach (XmlReaderAttributeItem xa in reader.GetAttributes()) { if (xa.LocalName == "Index" && xa.HasValue) { xa.Value.ParseToInt(out cellIndex); cellIndex--; } if (xa.LocalName == "StyleID" && xa.HasValue) { style = ParentBook.GetStyleByID(xa.Value); } if (xa.LocalName == "HRef" && xa.HasValue) { reference = xa.Value; } if (xa.LocalName == "Formula" && xa.HasValue) { formula = xa.Value; } if (xa.LocalName == "MergeAcross" && xa.HasValue) { xa.Value.ParseToInt(out mergeAcross); } if (xa.LocalName == "MergeDown" && xa.HasValue) { xa.Value.ParseToInt(out mergeDown); } } Cell cell = Cells(cellIndex, row.RowIndex); if (style != null) { cell.Style = style; } if (!reference.IsNullOrEmpty()) { cell.HRef = reference; } if (!formula.IsNullOrEmpty()) { FormulaParser.Parse(cell, formula); return; } if (isEmpty) { return; } if (mergeDown > 0 || mergeAcross > 0) { cell.MergeStart = true; Range range = new Range(cell, Cells(cellIndex + mergeAcross, row.RowIndex + mergeDown)); _MergedCells.Add(range); cell.ColumnSpan = range.ColumnCount; cell.RowSpan = range.RowCount; } while (reader.Read() && !(reader.Name == "Cell" && reader.NodeType == XmlNodeType.EndElement)) { if (reader.NodeType == XmlNodeType.Element) { if (reader.Name == "Data") { ImportCellData(reader, cell); } else if (reader.Name == "Comment") { ImportCellComment(reader, cell); } } } }
private void ImportRow(XmlReader reader) { bool isEmpty = reader.IsEmptyElement; int rowIndex = _Rows.Count; double height = -1; XmlStyle style = null; bool hidden = false; foreach (XmlReaderAttributeItem xa in reader.GetAttributes()) { if (xa.LocalName == "Height" && xa.HasValue) { xa.Value.ParseToInt(out height); } if (xa.LocalName == "Index" && xa.HasValue) { xa.Value.ParseToInt(out rowIndex); rowIndex--; } if (xa.LocalName == "StyleID" && xa.HasValue) { style = ParentBook.GetStyleByID(xa.Value); } if (xa.LocalName == "Hidden" && xa.HasValue) { hidden = xa.Value == "1"; } } Row row = GetRowByIndex(rowIndex); row.Hidden = hidden; if (height != -1) { row.Height = height; } if (style != null) { row.Style = style; } if (isEmpty) { return; } while (reader.Read() && !(reader.Name == "Row" && reader.NodeType == XmlNodeType.EndElement)) { if (reader.NodeType == XmlNodeType.Element) { if (reader.Name == "Cell") { ImportCell(reader, row); } } } }
internal XmlStyle FindStyle(XmlStyle style) { return(Styles.Find(xs => xs.CheckForMatch(style))); }
public static bool exportToExcel_old(DataGridView dgv, string fileName) { ExcelXmlWorkbook libro = new ExcelXmlWorkbook(); libro.Properties.Author = "Trust International S.A."; // Tomar el nombre de la empresa de Propertie. Worksheet hoja = libro[0]; hoja.Name = "Exportacion"; //hoja.FreezeTopRows = 3; //hoja.DisplayFormat = DisplayFormatType.None; hoja.PrintOptions.Orientation = PageOrientation.Landscape; hoja.PrintOptions.SetMargins(0.5, 0.4, 0.5, 0.4); XmlStyle estiloCeldaNombreColumnas = new XmlStyle(); estiloCeldaNombreColumnas.Font.Bold = true; estiloCeldaNombreColumnas.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Center; estiloCeldaNombreColumnas.Border.LineStyle = Borderline.Continuous; estiloCeldaNombreColumnas.Border.Weight = 2; estiloCeldaNombreColumnas.Border.Sides = BorderSides.All; // Primero cargo todas las columnas permitiendo hacerle algun formato diferente. foreach (DataGridViewColumn col in dgv.Columns) { hoja[col.Index, 0].Value = col.Name; hoja[col.Index, 0].Style = estiloCeldaNombreColumnas; } XmlStyle estiloCeldaNormal = new XmlStyle(); estiloCeldaNormal.Font.Bold = false; estiloCeldaNormal.Alignment.Horizontal = Yogesh.ExcelXml.HorizontalAlignment.Left; estiloCeldaNormal.Border.LineStyle = Borderline.Continuous; estiloCeldaNormal.Border.Weight = 1; estiloCeldaNormal.Border.Sides = BorderSides.All; //estiloCeldaNormal.DisplayFormat = DisplayFormatType.Text; int fila = 0; int columna = 0; double[] maxWithPerColumn; TimeSpan h; maxWithPerColumn = new double[dgv.Columns.Count]; DisplayFormatType cellType; while (fila < dgv.Rows.Count) { columna = 0; while (columna < dgv.Columns.Count) { if (dgv[columna, fila].ValueType == typeof(DateTime)) { cellType = DisplayFormatType.GeneralDate; if (dgv[columna,fila].Value.ToString() != "") hoja[columna, fila+1].Value = ((DateTime)dgv[columna,fila].Value).ToShortDateString(); } else if (dgv[columna, fila].ValueType == typeof(TimeSpan)) { cellType = DisplayFormatType.Time; h = (TimeSpan)dgv[columna, fila].Value; if (dgv[columna, fila].Value.ToString() != "") hoja[columna, fila + 1].Value = System.Math.Abs(System.Math.Truncate(h.TotalHours)).ToString() + ":" + System.Math.Abs(h.Minutes).ToString(); //((TimeSpan)dgv[columna, fila].Value).ToString(); } else if (dgv[columna, fila].ValueType == typeof(Boolean)) { cellType = DisplayFormatType.Custom; if (dgv[columna, fila].Value.ToString() != "") hoja[columna, fila+1].Value = ((Boolean)dgv[columna, fila].Value); } else if (dgv[columna, fila].ValueType == typeof(int) || dgv[columna, fila].ValueType == typeof(uint) || dgv[columna, fila].ValueType == typeof(byte) || dgv[columna, fila].ValueType == typeof(sbyte)) { cellType = DisplayFormatType.Standard; if (dgv[columna, fila].Value.ToString() != "") hoja[columna, fila + 1].Value = dgv[columna, fila].Value; } else { // String String str = dgv[columna, fila].Value.ToString();//.Replace("\n",Environment.NewLine); cellType = DisplayFormatType.Text; hoja[columna, fila + 1].Value = str; } if (dgv[columna, fila].Value.ToString().Length > maxWithPerColumn[columna]) { maxWithPerColumn[columna] = (double)dgv[columna, fila].Value.ToString().Length; } //hoja[columna, fila].ContentType = ContentType.Formula; estiloCeldaNormal.DisplayFormat = cellType; hoja[columna, fila + 1].Style = estiloCeldaNormal; columna++; } fila++; } columna = 0; while (columna < dgv.Columns.Count) { hoja.Columns(columna).Width = dgv.Columns[columna].Width; columna++; } // no extension is added if not present Stream sw = new FileStream(fileName,FileMode.Create); bool retval; retval = libro.Export(sw); sw.Close(); return retval; }