excel的导入和导出

导入函数
#region 读取Excle,并将Excle中的数据保存在Datatable中
///

/// 读取Excle,并将Excle中的数据保持在Datatable中
///

/// 要读取的文件路径
/// 要读取的Excle中的工作表名
///
public DataTable ImportExcelToDataTable(string filePath,string sheetName)
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
OleDbDataAdapter myCommand = new OleDbDataAdapter("SELECT*FROM[" + sheetName + "$]", strConn);
DataTable myDataTable = new DataTable();
try
{
myCommand.Fill(myDataTable);
}
catch (Exception)
{
return null;
}
return myDataTable;
}
}
#endregion

导出

///

/// 将明细表导出到Excel
///

/// 要导入到Excel中的中文表头的Sql,将需要的字段通过sql as成中文名
///
/// 1 成功 -1失败
protected int ExportToExcel(string sql, HttpResponse response)
{
DataTable dt = CommOtherBLL.ExportToExcel(sql);
if (dt != null && dt.Rows.Count > 0)
{
StringBuilder strContent = new StringBuilder();
for (int i = 0; i < dt.Columns.Count; i++)
strContent.Append(dt.Columns[i] + "\t");
strContent.Append("\n");
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int y = 0; y < dt.Columns.Count; y++)
strContent.Append(dt.Rows[i][y] + "\t");
strContent.Append("\n");
}
response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpUtility.UrlEncode("excel", System.Text.Encoding.UTF8) + ".xls");
response.ContentType = "application/ms-excel";
response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
response.Write(strContent.ToString());
response.End();
return 1;
}
else
return -1;
}