public void addSheet(string Path, DataTable dt)//From:www.uzhanbao.com
{
var SlDoc = new SLDocument(Path);
SlDoc.AddWorksheet("sheet2");//添加新的sheet
addData(SlDoc, dt);//填充数据
SlDoc.SaveAs(Path);
}
public void addData(SLDocument SlDoc, DataTable dt)//针对Excel的每列进行塞数据
{
var strCell = Columns(dt);
for (int j = 0; j < dt.Columns.Count; j++)
{
SlDoc.SetCellValue(strCell[j] + 1, dt.Columns[j].ToString());//先塞列名
}
if (dt != null && dt.Rows.Count > 0)
{
//按行进行对Excel塞查出的Table中数据
for (int j = 0; j < dt.Rows.Count; j++)
{
for (int i = 0; i < strCell.Length; i++)
SlDoc.SetCellValue(strCell[i] + (j + 2), dt.Rows[j][i].ToString());
}
}
}
public string[] Columns(DataTable dt)//获取Excel的而每个单元格的列名A,B,C......
{
var num = 65;
var str = new string[dt.Columns.Count];
for (int i = 0; i < dt.Columns.Count; i++)
{
if (i < 26)
{
str[i] = ((char)num).ToString();
}
else
{
var aa = (i / 26) + 64;
var bb = num - (i / 26) * 26;
str[i] = ((char)aa).ToString() + ((char)bb).ToString();
}