//前几天遇到个导出Excel的问题,然后上网找了很久,终于写出这个方法了,所以分享一下。
/// <summary>
/// 根据DataTable生成Excel
/// </summary>
/// <param name="dataTable">数据源</param>
/// <param name="fileName">要保存的路径</param>
/// <param name="fileName">要保存的路径</param>
/// <param name=" rowsCount ">当一个工作表最多的行数rowsCount,当超过时,则新建工作表。</param>
/// <returns>生成成功则返回True,否则返回False</returns>
public static bool DataTable2Excel(DataTable dataTable, string fileName,int rowsCount)
{
bool rt = false;//用于返回值
if (dataTable==null && rowsCount<1)
{
return false;
}
int rowNum = dataTable.Rows.Count;//获取行数
int colNum = dataTable.Columns.Count;//获取列数
int SheetNum = (rowNum - 1) / rowsCount + 1; //获取工作表数
string sqlText = "";//带类型的列名
string sqlValues = "";//值
string colCaption = "";//列名
for (int i = 0; i < colNum; i++)
{
if (i != 0)
{
sqlText += " , ";
colCaption += " , ";
}
sqlText += "[" + dataTable.Columns[i].Caption.ToString() + "] VarChar";//生成带VarChar列的标题
colCaption += "[" + dataTable.Columns[i].Caption.ToString() + "]";//生成列的标题
}
String sConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + fileName + ";Extended Properties=Excel 8.0;";
OleDbConnection cn = new OleDbConnection(sConnectionString);
try
{
//判断文件是否存在,存在则先删除
if (File.Exists(fileName))
{
File.Delete(fileName);
}
int sheet=1;//表数
int dbRow = 0;//数据的行数
//打开连接
cn.Open();
while(sheet<=SheetNum)
{
string sqlCreate = "CREATE TABLE [Sheet" + sheet.ToString() + "] (" + sqlText + ")";
OleDbCommand cmd = new OleDbCommand(sqlCreate, cn);
//创建Excel文件
cmd.ExecuteNonQuery();
for (int srow = 0; srow < rowsCount; srow++)
{
sqlValues = "";
for (int col = 0; col < colNum; col++)
{
if (col != 0)
{
sqlValues += " , ";
}
sqlValues += "'" + dataTable.Rows[dbRow][col].ToString() + "'";//拼接Value语句
}
String queryString = "INSERT INTO [Sheet"+sheet.ToString()+"] (" + colCaption + ") VALUES (" + sqlValues + ")";
cmd.CommandText = queryString;
cmd.ExecuteNonQuery();//插入数据
dbRow++;//目前数据的行数自增
if (dbRow >= rowNum)
{
//目前数据的行数等于rowNum时退出循环
break;
}
}
sheet++;
}
rt = true;
}
catch
{
}
finally
{
cn.Close();
}
return rt;
}