// 输出 Excel 格式的表格 // parameters: // nTopLines 顶部预留多少行 public void OutputExcelTable(Table table, ExcelDocument doc, int nTopLines, int nMaxLines = -1) { // StringBuilder strResult = new StringBuilder(4096); int i, j; if (nMaxLines == -1) nMaxLines = table.Count; int _lineIndex = nTopLines; // 表格标题 // strResult.Append("<tr class='column'>\r\n"); int nColIndex = 0; for (j = 0; j < this.Count; j++) { PrintColumn column = (PrintColumn)this[j]; if (column.Colspan == 0) continue; if (column.Colspan > 1) { doc.WriteExcelTitle(_lineIndex, nColIndex, column.Colspan, column.Title); #if NO cells.Add(new CellData(nColIndex, column.Title)); #endif nColIndex += column.Colspan; } else { doc.WriteExcelCell( _lineIndex, nColIndex++, column.Title, true); #if NO cells.Add(new CellData(nColIndex, column.Title)); #endif } } #if NO if (cells.Count > 0) doc.WriteExcelLine(_lineIndex, cells); #endif // 合计数组 object[] sums = null; // 2008/12/1 new changed if (this.SumLine) { sums = new object[this.Count]; for (i = 0; i < sums.Length; i++) { sums[i] = null; } } NumberFormatInfo nfi = new CultureInfo("zh-CN", false).NumberFormat; nfi.NumberDecimalDigits = 2; // 内容行循环 for (i = 0; i < Math.Min(nMaxLines, table.Count); i++) { Line line = table[i]; string strLineCssClass = "content"; if (this.OutputLine != null) { OutputLineEventArgs e = new OutputLineEventArgs(); e.Line = line; e.Index = i; e.LineCssClass = strLineCssClass; this.OutputLine(this, e); if (e.Output == false) continue; strLineCssClass = e.LineCssClass; } // strResult.Append("<tr class='" + strLineCssClass + "'>\r\n"); _lineIndex++; List<CellData> cells = new List<CellData>(); // 列循环 for (j = 0; j < this.Count; j++) { PrintColumn column = (PrintColumn)this[j]; if (column.ColumnNumber < -1) { throw (new Exception("PrintColumn对象ColumnNumber列尚未初始化,位置" + Convert.ToString(j))); } string strText = ""; if (column.ColumnNumber != -1) { if (column.DataType == DataType.PriceDouble) { if (line.IsNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { double v = line.GetDouble(column.ColumnNumber); /* NumberFormatInfo provider = new NumberFormatInfo(); provider.NumberDecimalDigits = 2; provider.NumberGroupSeparator = "."; provider.NumberGroupSizes = new int[] { 3 }; strText = Convert.ToString(v, provider); * */ strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.PriceDecimal) { if (line.IsNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { decimal v = line.GetDecimal(column.ColumnNumber); strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.PriceDecimal) { if (line.IsNull(column.ColumnNumber) == true) strText = column.DefaultValue; else { decimal v = line.GetDecimal(column.ColumnNumber); strText = v.ToString("N", nfi); } } else if (column.DataType == DataType.Price) { // Debug.Assert(false, ""); if (line.IsNull(column.ColumnNumber) == true) strText = column.DefaultValue; // 2005/5/26 else strText = line.GetPriceString(column.ColumnNumber); } else strText = line.GetString(column.ColumnNumber, column.DefaultValue); } else { strText = line.Entry; } #if NO doc.WriteExcelCell( _lineIndex, j, strText, true); #endif cells.Add(new CellData(j, strText)); if (this.SumLine == true && column.Sum == true && column.ColumnNumber != -1) { try { // if (column.DataType != DataType.Currency) { object v = line.GetObject(column.ColumnNumber); if (this.SumCell != null) { SumCellEventArgs e = new SumCellEventArgs(); e.DataType = column.DataType; e.ColumnNumber = column.ColumnNumber; e.LineIndex = i; e.Line = line; e.Value = v; this.SumCell(this, e); if (e.Value == null) continue; v = e.Value; } if (sums[j] == null) sums[j] = v; else { sums[j] = AddValue(column.DataType, sums[j], v); // sums[j] = ((decimal)sums[j]) + v; } } /* else { string v = line.GetString(column.ColumnNumber); if (this.SumCell != null) { SumCellEventArgs e = new SumCellEventArgs(); e.DataType = column.DataType; e.ColumnNumber = column.ColumnNumber; e.LineIndex = i; e.Line = line; e.Value = v; this.SumCell(this, e); if (e.Value == null) continue; v = (string)e.Value; } sums[j] = PriceUtil.JoinPriceString((string)sums[j], v); } * */ } catch (Exception ex) // 俘获可能因字符串转换为整数抛出的异常 { throw new Exception("在累加 行 " + i.ToString() + " 列 " + column.ColumnNumber.ToString() + " 值的时候,抛出异常: " + ex.Message); } } } // strResult.Append("</tr>\r\n"); doc.WriteExcelLine(_lineIndex, cells); } if (this.SumLine == true) { // strResult.Append("<tr class='sum'>\r\n"); _lineIndex++; List<CellData> cells = new List<CellData>(); for (j = 0; j < this.Count; j++) { PrintColumn column = (PrintColumn)this[j]; string strText = ""; if (j == 0) strText = "合计"; else if (column.Sum == true && sums[j] != null) { if (column.DataType == DataType.PriceDouble) strText = ((double)sums[j]).ToString("N", nfi); else if (column.DataType == DataType.PriceDecimal) strText = ((decimal)sums[j]).ToString("N", nfi); else if (column.DataType == DataType.Price) { strText = StatisUtil.Int64ToPrice((Int64)sums[j]); } else strText = Convert.ToString(sums[j]); if (column.DataType == DataType.Currency) { string strSomPrice = ""; string strError = ""; // 汇总价格 int nRet = PriceUtil.SumPrices(strText, out strSomPrice, out strError); if (nRet == -1) strText = strError; else strText = strSomPrice; } } else strText = column.DefaultValue; // " "; #if NO doc.WriteExcelCell( _lineIndex, j, strText, true); #endif cells.Add(new CellData(j, strText)); } // strResult.Append("</tr>\r\n"); doc.WriteExcelLine(_lineIndex, cells); } }