public static ExcelDocument Create(string strFilename)
{
ExcelDocument doc = new ExcelDocument();
doc.spreadsheetDocument = SpreadsheetDocument.Create(strFilename,
SpreadsheetDocumentType.Workbook);
doc.Initial();
return(doc);
}
static void SetColumnWidth(ExcelDocument doc,
List<int> widths)
{
Columns columns = new Columns();
uint i = 1;
foreach (int width in widths)
{
DocumentFormat.OpenXml.Spreadsheet.Column column = new DocumentFormat.OpenXml.Spreadsheet.Column();
if (width != -1)
{
// min max 表示列范围编号
column.Min = UInt32Value.FromUInt32(i);
column.Max = UInt32Value.FromUInt32(i);
column.Width = width;
column.CustomWidth = true;
columns.Append(column);
}
i++;
}
doc.WorkSheet.InsertAt(columns, 0);
}
public static ExcelDocument Create(string strFilename)
{
ExcelDocument doc = new ExcelDocument();
doc.spreadsheetDocument = SpreadsheetDocument.Create(strFilename,
SpreadsheetDocumentType.Workbook);
doc.Initial();
return doc;
}
// 输出 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);
}
}
