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