/// <summary>
/// DataTable导出数据到Excel
/// </summary>
public static void DataTableToExcel(System.Data.DataTable dt, string[] sHeader)
{
XlsDocument xls = new XlsDocument();
xls.FileName = "List" + DateTime.Now.ToString("yyyyMMddhhmmss");
org.in2bits.MyXls.Worksheet sheet = xls.Workbook.Worksheets.Add("Sheet1");//状态栏标题名称
//设置文档列属性
ColumnInfo cinfo = new ColumnInfo(xls, sheet);//设置xls文档的指定工作页的列属性
cinfo.Collapsed = true;
cinfo.ColumnIndexStart = 0; //列开始
cinfo.ColumnIndexEnd = 6; //列结束
cinfo.Collapsed = true;
cinfo.Width = 15 * 256; //列宽度
sheet.AddColumnInfo(cinfo);
//设置文档列属性结束
Cells cells = sheet.Cells;//Cells实例是sheet页中单元格（cell）集合
//单元格1-base
Cell[] cellHeader = new Cell[sHeader.Length];
#region 对Excel进行赋值
for (int j = 0; j < dt.Rows.Count; j++) //行数
{
for (int i = 0; i < sHeader.Length; i++) //列数
{
cellHeader[i] = cells.Add(1, i + 1, sHeader[i]);
cellHeader[i].Font.Height = 20 * 10;
cellHeader[i].BottomLineColor = Colors.Black;
cellHeader[i].BottomLineStyle = 2;
cellHeader[i].Pattern = 1;
cellHeader[i].PatternBackgroundColor = Colors.Black;
cellHeader[i].PatternColor = Colors.White; // "#ECE9D8";
if (j + 2 < 65536) //excel2003的最大行
{
cellHeader[i] = cells.Add(j + 2, i + 1, dt.Rows[j][i].ToString());
}
}
}
#endregion
xls.Send();
}
public void ColumnWidth5120()
{
ushort colWidth = 5120;
MyXlsTestFixture.XlsDocumentDelegate docDelegate = delegate(XlsDocument doc)
{
Worksheet sheet = doc.Workbook.Worksheets.Add("Sheet1");
ColumnInfo columnInfo = new ColumnInfo(doc, sheet);
sheet.AddColumnInfo(columnInfo);
columnInfo.Width = colWidth;
Assert.AreEqual(colWidth, columnInfo.Width, "Column Width setting");
};
string fileName = WriteDocument(docDelegate); //48.762
string actualString = GetCellPropertyViaExcelOle(fileName, CellProperties.Width);
double actual = double.NaN;
Assert.IsTrue(double.TryParse(actualString, out actual), "Column width didn't parse");
Assert.AreEqual(colWidth / 48.762, actual, 0.01, "Column width"); //NOTE: This factor (48.762) depends on the default (first) font in the file
}
