目录
前言
筛选出一些比较实用的C#写的Excel导入导出代码,并且进行修改、扩充,在项目中用了很多年,稳定性和速度都不错,现拿出来分享。包括判断Excel版本、文件是否存在、完成后是否自动打开等等。还包括csv各式的导入导出。部分源代码使用了谢堂文(Darren Xie)写的,未做修改,保留了作者姓名,感谢。下面是使用方法及主要代码摘要。完整源代码请下载使用,需要先引用压缩包内的Microsoft.Office.Interop.Excel.dll,否则因本机Excel版本不同可能会报错。
下载地址:Excel导入导出C#源代码
一、DataTable导出Excel
/// <summary>
/// 将DataTable写入Excel
/// </summary>
/// <param name="newfilename">目标Excel文件路径及名称</param>
/// <param name="dt">DataTable</param>
/// <param name="openFile">完成后是否打开文件</param>
/// <returns>1成功,0失败</returns>
public static int ExportToExcel(string newfilename, DataTable dt, bool openFile)
主要代码:
using (OleDbConnection conn = new OleDbConnection(GetExcelConnectionStringByWrite(servepath)))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
for (int i = 0; i < dt.Rows.Count; i++)
{
StringBuilder strfield = new StringBuilder();
StringBuilder strvalue = new StringBuilder();
for (int j = 0; j < dt.Columns.Count; j++)
{
strfield.Append("[" + dt.Columns[j].ColumnName + "]");
strvalue.Append("'" + dt.Rows[i][j].ToString() + "'");
if (j != dt.Columns.Count - 1)
{
strfield.Append(",");
strvalue.Append(",");
}
}
if (maxrow == 0)//不需要限制一個表的行數
{
cmd.CommandText = strSQL.Append(" insert into [" + sheetName + "]( ")
.Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString();
}
else
{
//加1才可才防止i=0的情況只寫入一行
string sheetNameT = sheetName + ((i + 1) / maxrow + (Math.IEEERemainder(i + 1, maxrow) == 0 ? 0 : 1)).ToString();
if (!tables.Contains(sheetNameT))
{
tables = GetExcelSheetNames2List(servepath);
string[] cols = new string[dt.Columns.Count];
for (int ii = 0; ii < dt.Columns.Count; ii++)
{
cols[ii] = dt.Columns[ii].ColumnName;
}
if (!(CreateSheet(servepath, sheetNameT, cols)))
{
throw new Exception("在" + servepath + "上創建表" + sheetName + "失敗.");
}
else
{
tables = GetExcelSheetNames2List(servepath);
}
}
cmd.CommandText = strSQL.Append(" insert into [" + sheetNameT + "]( ")
.Append(strfield.ToString()).Append(") values (").Append(strvalue).Append(")").ToString();
}
cmd.ExecuteNonQuery();
strSQL.Remove(0, strSQL.Length);
}
conn.Close();
}
二、DataGridView导出Excel
/// <summary>
/// 将DataGridView写入Excel。字段名使用DataGridView的HeaderText。如果字段类型是DataGridViewComboBoxColumn,将导出显示的内容DisplayMember,而不是ValueMember。
/// </summary>
/// <param name="newfilename">目标Excel文件路径及名称</param>
/// <param name="dgv">DataGridView</param>
/// <param name="openFile">完成后是否打开文件</param>
/// <returns>1成功,0失败</returns>
public static int ExportToExcel(string newfilename, DataGridView dgv, bool openFile)
主要代码同上。
三、DataTable导出csv
/// <summary>
/// 将DataTable中数据写入到CSV文件中
/// </summary>
/// <param name="dt">提供保存数据的DataTable</param>
/// <param name="fileName">CSV的文件路径</param>
public static void SaveCSV(DataTable dt, string fullPath)
主要代码:
FileStream fs = new FileStream(fullPath, System.IO.FileMode.Create, System.IO.FileAccess.Write);
StreamWriter sw = new StreamWriter(fs, System.Text.Encoding.UTF8);
string data = "";
//写出列名称
for (int i = 0; i < dt.Columns.Count; i++)
{
data += dt.Columns[i].ColumnName.ToString();
if (i < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
//写出各行数据
for (int i = 0; i < dt.Rows.Count; i++)
{
data = "";
for (int j = 0; j < dt.Columns.Count; j++)
{
string str = dt.Rows[i][j].ToString();
str = str.Replace("\"", "\"\"");//替换英文冒号 英文冒号需要换成两个冒号
if (str.Contains(",") || str.Contains("\"")
|| str.Contains("\r") || str.Contains("\n")) //含逗号 冒号 换行符的需要放到引号中
{
str = string.Format("\"{0}\"", str);
}
data += str;
if (j < dt.Columns.Count - 1)
{
data += ",";
}
}
sw.WriteLine(data);
}
sw.Close();
fs.Close();
四、填充Excel指定单元格
/// <summary>
/// 从Excel模板创建新文件,并且将数据写入指定单元格
/// </summary>
/// <param name="templatename">模板文件</param>
/// <param name="newfilename">新文件</param>
/// <param name="cellList">需要写入的单元格列表,每个单元格包含值、行列。行列索引从1开始</param>
/// <returns></returns>
public static bool ExportToExcel(string templatename, string newfilename, ExcelCell[] cellList)
{
Excel.Application myApp;
Excel.Workbook myBook;
Excel.Worksheet mySheet;
Excel.Sheets mySheets;
FileInfo mode = new FileInfo(templatename);
mode.CopyTo(newfilename, true); //复制模板
myApp = null; myBook = null; mySheet = null;
object oMissiong = Missing.Value;
myApp = new Excel.ApplicationClass();
myApp.Visible = true;
myApp.Workbooks.Open(newfilename, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong, oMissiong);
myBook = myApp.Workbooks[1];
mySheets = myBook.Worksheets;
mySheet = (Excel.Worksheet)mySheets[1]; //行和列的索引都是从1开始
for (int i = 0; i < cellList.Length; i++)
{
mySheet.Cells[cellList[i].RowId, cellList[i].ColumnId] = cellList[i].CellValue;
}
myBook.Save();
//mySheet.Activate();
//myApp.Workbooks.Close();
return true;
}
五、从Excel读取数据
/// <summary>
/// 从excel读取数据
/// </summary>
/// <param name="filePath">文件名</param>
/// <param name="ExistsHeadText">表中是否有列标题</param>
/// <param name="tableIndex">在多表excel中读取第几个表。序号从0开始</param>
/// <returns>DataSet</returns>
public static DataTable LoadDataFromExcel(string filePath, bool ExistsHeadText, int tableIndex)
主要代码:
string strConn = GetExcelConnectionString(FileFullPath, no_HDR);
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataSet ds = new DataSet();
OleDbDataAdapter odda = new OleDbDataAdapter(string.Format("SELECT * FROM [{0}]", SheetName), conn);
odda.Fill(ds, SheetName);
conn.Close();