/// <summary>
/// 将DataTable导出为Xls文件
/// neo 2010-12-02
/// </summary>
/// <param name="ExcelData"></param>
/// <param name="GridView1"></param>
/// <param name="sExcelDir"></param>
/// <param name="sSheetName"></param>
/// <returns>返回生成Excel文件本地路径</returns>
public static string SaveAsExcel(DataTable ExcelData, GridView GridView1, string sExcelDir, string sSheetName)
{
if (ExcelData == null)
{
return(string.Empty);
}
NameValueCollection ExcelColumns = new NameValueCollection();
foreach (DataControlField GridColumn in GridView1.Columns)
{
// 隐藏列 不导出
if (GridColumn.Visible == false)
{
continue;
}
// 只导出BoundField列
if (GridColumn.GetType().FullName != "System.Web.UI.WebControls.BoundField")
{
continue;
}
// 如果没有列名 不导出
if (GridColumn.HeaderText == string.Empty)
{
continue;
}
BoundField BoundField1 = GridColumn as BoundField;
ExcelColumns.Add(BoundField1.HeaderText, BoundField1.DataField);
}
// 生成随机唯一文件名
string sExcelFileName = Guid.NewGuid().ToString() + ".xls";
// 创建Excel文档
XlsDocument ExcelDoc = new XlsDocument();
ExcelDoc.FileName = sExcelFileName;
// 添加Sheet
Worksheet Sheet = ExcelDoc.Workbook.Worksheets.Add(sSheetName);
//Sheet.Name = sSheetName;
// 添加第一行列名
ColumnInfo ColumnInfo1 = new ColumnInfo(ExcelDoc, Sheet);
ColumnInfo1.ColumnIndexStart = 0;
ColumnInfo1.ColumnIndexEnd = Convert.ToUInt16(ExcelColumns.Count - 1);
ColumnInfo1.Width = 25 * 150;
Sheet.AddColumnInfo(ColumnInfo1);
// 生成第一行Excel列
int i = 1;
foreach (string sHeaderText in ExcelColumns.AllKeys)
{
Cell CellObj = Sheet.Cells.Add(1, i, sHeaderText);
CellObj.Font.Weight = FontWeight.Bold;
CellObj.Pattern = 1;
CellObj.PatternColor = Colors.Silver;
CellObj.UseBorder = true;
CellObj.LeftLineStyle = 1;
CellObj.LeftLineColor = Colors.Black;
CellObj.RightLineStyle = 1;
CellObj.RightLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.TopLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.BottomLineColor = Colors.Black;
i++;
}
int j = 2;
foreach (DataRow RowObj in ExcelData.Rows)
{
int m = 1;
foreach (string sHeaderText in ExcelColumns.AllKeys)
{
// 获取对应的DataField
string sDataField = ExcelColumns[sHeaderText];
object oValue = RowObj[sDataField];
if (oValue.GetType().FullName == "System.Byte")
{
oValue = Convert.ToInt16(oValue);
}
else if (oValue.GetType().FullName == "System.DBNull")
{
oValue = string.Empty;
}
else if (oValue.GetType().FullName == "System.String")
{
oValue = oValue.ToString();
}
else if (oValue.GetType().FullName == "System.DateTime")
{
oValue = Convert.ToDateTime(oValue).ToString();
}
Cell CellObj = Sheet.Cells.Add(j, m, oValue);
CellObj.UseBorder = true;
CellObj.LeftLineStyle = 1;
CellObj.LeftLineColor = Colors.Black;
CellObj.RightLineStyle = 1;
CellObj.RightLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.TopLineColor = Colors.Black;
CellObj.TopLineStyle = 1;
CellObj.BottomLineColor = Colors.Black;
m++;
}
j++;
}
string sPath = sExcelDir + sExcelFileName;
if (File.Exists(sPath) == true)
{
File.Delete(sPath);
}
ExcelDoc.Save(sExcelDir, true);
return(sPath);
}