C#导入导出Excel表的数据

C#导⼊导出Excel表的数据
⼀：C#导⼊导出EXCEL⽂件的类
代码如下：
⾸先将Microsoft Excel 14.0 Object Library 引⽤导⼊
using System;
using System.Data;
using System.Data.OleDb;
namespace ZFSoft.Joint
{
public class ExcelIO
{
private int _ReturnStatus;
private string _ReturnMessage;
///<summary>
///执⾏返回状态
///</summary>
public int ReturnStatus
{
get
{
return _ReturnStatus;
}
}
///<summary>
///执⾏返回信息
///</summary>
public string ReturnMessage
{
get
{
return _ReturnMessage;
}
}
public ExcelIO()
{
}
///<summary>
///导⼊EXCEL到DataSet
///</summary>
///<param name="fileName">Excel全路径⽂件名</param>
///<returns>导⼊成功的DataSet</returns>
public DataTable ImportExcel(string fileName)
{
//判断是否安装EXCEL
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "⽆法创建Excel对象，可能您的计算机未安装Excel";
return null;
}
//判断⽂件是否被其他进程使⽤
Microsoft.Office.Interop.Excel.Workbook workbook;
try
{
workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0); }
catch
{
_ReturnStatus = -1;
_ReturnMessage = "Excel⽂件处于打开状态，请保存关闭";
return null;
}
//获得所有Sheet名称
int n = workbook.Worksheets.Count;
string[] SheetSet = new string[n];
System.Collections.ArrayList al = new System.Collections.ArrayList();
for (int i = 1; i <= n; i++)
{
SheetSet[i - 1] = ((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name;
}
//释放Excel相关对象
workbook.Close(null, null, null);
xlApp.Quit();
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
//把EXCEL导⼊到DataSet
DataSet ds = new DataSet();
DataTable table = new DataTable();
string connStr = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + fileName + ";Extended Properties=Excel 8.0";
using (OleDbConnection conn = new OleDbConnection(connStr))
{
conn.Open();
OleDbDataAdapter da;
string sql = "select * from [" + SheetSet[0] + "$] ";
da = new OleDbDataAdapter(sql, conn);
da.Fill(ds, SheetSet[0]);
da.Dispose();
table = ds.Tables[0];
conn.Close();
conn.Dispose();
}
return table;
}
///<summary>
///把DataTable导出到EXCEL
///</summary>
///<param name="reportName">报表名称</param>
///<param name="dt">数据源表</param>
///<param name="saveFileName">Excel全路径⽂件名</param>
///<returns>导出是否成功</returns>
public bool ExportExcel(string reportName, System.Data.DataTable dt, string saveFileName)
{
if (dt == null)
{
_ReturnStatus = -1;
_ReturnMessage = "数据集为空！";
return false;
}
bool fileSaved = false;
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
_ReturnStatus = -1;
_ReturnMessage = "⽆法创建Excel对象，可能您的计算机未安装Excel";
return false;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells.Font.Size = 10;
Microsoft.Office.Interop.Excel.Range range;
long totalCount = dt.Rows.Count;
long rowRead = 0;
float percent = 0;
worksheet.Cells[1, 1] = reportName;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 12;
((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;
//写⼊字段
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;
range = (Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];
range.Interior.ColorIndex = 15;
range.Font.Bold = true;
}
//写⼊数值
for (int r = 0; r < dt.Rows.Count; r++)
{
for (int i = 0; i < dt.Columns.Count; i++)
{
worksheet.Cells[r + 3, i + 1] = dt.Rows[r][i].ToString();
}
rowRead++;
percent = ((float)(100 * rowRead)) / totalCount;
}
//此⾏有可能会出错，解决办法见下⾯
range = worksheet.get_Range(worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);
range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin, Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);
if (dt.Rows.Count > 0)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHorizontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
if (dt.Columns.Count > 1)
{
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].LineStyle = Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;
range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVertical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;
}
//保存⽂件
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
fileSaved = true;
}
catch (Exception ex)
{
fileSaved = false;
_ReturnStatus = -1;
_ReturnMessage = "导出⽂件时出错,⽂件可能正被打开！\n" + ex.Message;
}
}
else
{
fileSaved = false;
}
//释放Excel对应的对象
if (range != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(range);
range = null;
}
if (worksheet != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);
worksheet = null;
}
if (workbook != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);
workbook = null;
}
if (workbooks != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);
workbooks = null;
}
xlApp.Application.Workbooks.Close();
xlApp.Quit();
if (xlApp != null)
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);
xlApp = null;
}
GC.Collect();
return fileSaved;
}
}
}
这种写法可能出现，Object未包含get_range定义，解决办法是将get_range变为Range：
range = worksheet.Range[worksheet.Cells[2, 1], worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]];
调⽤该类的具体使⽤，导⼊表中的数据到dataGridView，实际上也是先到dataset⾥⾯：
private void button1_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files|*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
string filename = ofd.FileName;
ExcelIO ex = new ExcelIO();
dataGridView1.DataSource = ex.ImportExcel(filename);
}
}
调⽤该类的具体使⽤，从数据库导出到Excel，实际上也是先到dataset⾥⾯：
private void button2_Click(object sender, EventArgs e)
{
CarTableAdapter ca = new CarTableAdapter();
DataSet1.CarDataTable table = ca.GetData();
ExcelIO ex = new ExcelIO();
ex.ExportExcel("qiche",table,@"C:\Users\Administrator\Desktop\例⼦.xlsx");
}
⼆：C# WinForm导出Excel⽅法
在.NET应⽤中，导出Excel是很常见的需求，导出Excel报表⼤致有以下三种⽅式：Office PIA，⽂件流和NPOI开源库，.NET开发⼈员⾸选的⽅法，通过COM组件调⽤Office软件本⾝来实现⽂件的创建和读写，但是数据量较⼤的时候异常缓慢；如下代码所⽰已经做了优化，将⼀个⼆维对象数组赋值到⼀个单元格区域中（下⾯的代码中只能⽤于导出列数不多于26列的数据导出）：
OFFICE PIA：
public static void ExportToExcel(DataSet dataSet, string outputPath)
{
Excel.ApplicationClass excel = new Excel.ApplicationClass();
Excel.Workbook workbook = excel.Workbooks.Add(Type.Missing);
int sheetIndex = 0;
foreach (System.Data.DataTable dt in dataSet.Tables)
{
object[,] data = new object[dt.Rows.Count + 1, dt.Columns.Count];
for (int j = 0; j < dt.Columns.Count; j++)
{
data[0, j] = dt.Columns[j].ColumnName;
}
for (int j = 0; j < dt.Columns.Count; j++)
{
for (int i = 0; i < dt.Rows.Count; i++)
{
data[i + 1, j] = dt.Rows[i][j];
}
}
string finalColLetter = string.Empty;
string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
int colCharsetLen = colCharset.Length;
if (dt.Columns.Count > colCharsetLen)
{
finalColLetter = colCharset.Substring(
(dt.Columns.Count - 1) / colCharsetLen - 1, 1);
}
finalColLetter += colCharset.Substring(
(dt.Columns.Count - 1) % colCharsetLen, 1);
Excel.Worksheet sheet = (Excel.Worksheet)workbook.Sheets.Add(
workbook.Sheets.get_Item(++sheetIndex),
Type.Missing, 1, Excel.XlSheetType.xlWorksheet);
= dt.TableName;
string range = string.Format("A1:{0}{1}", finalColLetter, dt.Rows.Count + 1);
sheet.get_Range(range, Type.Missing).Value2 = data;
((Excel.Range)sheet.Rows[1, Type.Missing]).Font.Bold = true;
}
workbook.SaveAs(outputPath, Excel.XlFileFormat.xlWorkbookNormal, Type.Missing,
Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlExclusive,
Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
workbook.Close(true, Type.Missing, Type.Missing);
workbook = null;
excel.Quit();
KillSpecialExcel(excel);
excel = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
[DllImport("user32.dll", SetLastError = true)]
static extern int GetWindowThreadProcessId(IntPtr hWnd, out int processId);
static void KillSpecialExcel(Excel.Application app)
{
try
{
if (app != null)
{
int processId;
GetWindowThreadProcessId(new IntPtr(app.Hwnd), out processId);
System.Diagnostics.Process.GetProcessById(processId).Kill();
}
}
catch (Exception ex)
{
throw ex;
}
}
⽂件流
这种⽅法的效率明显⾼于第⼀种，⽽且也不需要安装Office，但是导出的xls⽂件并不符合Excel的格式标准，在打开⽣成的xls⽂件时会提⽰：The file you are trying to open is in a different format that specified by the file extension. Verify that the file is not corrupted and is from a trusted source before opening the file.
public static void ExportToExcel(System.Data.DataSet ds, string path)
{
StreamWriter sw = null;
try
{
long totalCount = ds.Tables[0].Rows.Count;
sw = new StreamWriter(path, false, Encoding.Unicode);
StringBuilder sb = new StringBuilder();
for (int i = 0; i < ds.Tables[0].Columns.Count; i++)
{
sb.Append(ds.Tables[0].Columns[i].ColumnName + "\t");
}
sb.Append(Environment.NewLine);
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
sb.Append(ds.Tables[0].Rows[i][j].ToString() + "\t");
}
sb.Append(Environment.NewLine);
}
sw.Write(sb.ToString());
sw.Flush();
}
catch (IOException ioe)
{
throw ioe;
}
finally
{
if (sw != null)
{
sw.Close();
}
}
}
三：WinForm项⽬开发中Excel⽤法实例解析
在实际项⽬的开发过程中，所涉及的EXCEL往往会⽐较复杂，并且列中还会带有⼀些计算公式，这就给读取带来了很⼤的困难，曾经尝试过⼀些免费的第三⽅dll，譬如Myxls，NPOI，IExcelDataReader都会出现⼀些问题，最后采⽤OLEDB形式读取，再x64操作系统上有点问题，不过采⽤⼩技巧即可解决。

namespace DBUtilHelpV2
{
public class OLEDBExcelToolV2
{
static readonly string xls = ".xls";
static readonly string xlsx = ".xlsx";
string _ExcelExtension = string.Empty;//后缀
string _ExcelPath = string.Empty;//路径
string _ExcelConnectString = string.Empty;//链接字符串
static bool _X64Version = false;//是否强制使⽤x64链接字符串，即xlsx形式
public OLEDBExcelToolV2(string excelPath, bool x64Version)
{
if (string.IsNullOrEmpty(excelPath))
throw new ArgumentNullException("excelPath");
if (!File.Exists(excelPath))
throw new ArgumentException("excelPath");
string _excelExtension = Path.GetExtension(excelPath);
_ExcelExtension = _excelExtension.ToLower();
_ExcelPath = excelPath;
_X64Version = x64Version;
_ExcelConnectString = BuilderConnectionString();
}
///<summary>
///创建链接字符串
///</summary>
///<returns></returns>
private string BuilderConnectionString()
{
Dictionary<string, string> _connectionParameter = new Dictionary<string, string>(); if (!_ExcelExtension.Equals(xlsx) && !_ExcelExtension.Equals(xls))
{
throw new ArgumentException("excelPath");
}
if (!_X64Version)
{
if (_ExcelExtension.Equals(xlsx))
{
// XLSX - Excel 2007, 2010, 2012, 2013
_connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
_connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'"; }
else if (_ExcelExtension.Equals(xls))
{
// XLS - Excel 2003 and Older
_connectionParameter["Provider"] = "Microsoft.Jet.OLEDB.4.0";
_connectionParameter["Extended Properties"] = "'Excel 8.0;IMEX=1'";
}
}
else
{
_connectionParameter["Provider"] = "Microsoft.ACE.OLEDB.12.0;";
_connectionParameter["Extended Properties"] = "'Excel 12.0 XML;IMEX=1'";
}
_connectionParameter["Data Source"] = _ExcelPath;
StringBuilder _connectionString = new StringBuilder();
foreach (KeyValuePair<string, string> parameter in _connectionParameter)
{
_connectionString.Append(parameter.Key);
_connectionString.Append('=');
_connectionString.Append(parameter.Value);
_connectionString.Append(';');
}
return _connectionString.ToString();
}
///<summary>
/// Excel操作
/// DELETE不⽀持
///</summary>
///<param name="sql"></param>
///<returns></returns>
public int ExecuteNonQuery(string sql)
{
int _affectedRows = -1;
using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
{
try
{
sqlcon.Open();
using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))
{
_affectedRows = sqlcmd.ExecuteNonQuery();
}
}
catch (Exception)
{
return -1;
}
}
return _affectedRows;
}
///<summary>
/// Excel操作
///获取EXCEL内sheet集合
///</summary>
///<param name="sql"></param>
///<returns></returns>
public string[] GetExcelSheetNames()
{
DataTable _schemaTable = null;
using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
{
try
{
sqlcon.Open();
_schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); String[] _excelSheets = new String[_schemaTable.Rows.Count];
int i = 0;
foreach (DataRow row in _schemaTable.Rows)
{
_excelSheets[i] = row["TABLE_NAME"].ToString().Trim();
i++;
}
return _excelSheets;
}
catch (Exception)
{
return null;
}
finally
{
if (_schemaTable != null)
{
_schemaTable.Dispose();
}
}
}
}
///<summary>
///读取sheet
/// eg:select * from [Sheet1$]
///</summary>
///<param name="sql"></param>
///<returns></returns>
public DataTable ExecuteDataTable(string sql)
{
using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
{
try
{
using (OleDbCommand sqlcmd = new OleDbCommand(sql, sqlcon))
{
using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))
{
DataTable _dtResult = new DataTable();
sqldap.Fill(_dtResult);
return _dtResult;
}
}
}
catch (Exception)
{
return null;
}
}
}
///<summary>
///获取excel所有sheet数据
///</summary>
///<returns>DataSet</returns>
public DataSet ExecuteDataSet()
{
DataSet _excelDb = null;
using (OleDbConnection sqlcon = new OleDbConnection(_ExcelConnectString))
{
try
{
sqlcon.Open();
DataTable _schemaTable = sqlcon.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); if (_schemaTable != null)
{
int i = 0;
_excelDb = new DataSet();
foreach (DataRow row in _schemaTable.Rows)
{
string _sheetName = row["TABLE_NAME"].ToString().Trim();
string _sql = string.Format("select * from [{0}]", _sheetName);
using (OleDbCommand sqlcmd = new OleDbCommand(_sql, sqlcon))
{
using (OleDbDataAdapter sqldap = new OleDbDataAdapter(sqlcmd))
{
DataTable _dtResult = new DataTable();
_dtResult.TableName = _sheetName;
sqldap.Fill(_dtResult);
_excelDb.Tables.Add(_dtResult);
}
}
i++;
}
}
}
catch (Exception)
{
return null;
}
}
return _excelDb;
}
}
}
代码使⽤⽅法：
///<summary>
///合并EXCEL数据
///</summary>
///<param name="_excelPath">excel路径</param>
private void HandleMergeExcel(string _excelPath)
{
if (!string.IsNullOrEmpty(_excelPath))
{
OLEDBExcelToolV2 _excelHelper = new OLEDBExcelToolV2(_excelPath, true);
DataSet _excelSource = _excelHelper.ExecuteDataSet();
HandleExcelSource(_excelSource);
}
}
若在x64操作系统，将第⼆个参数设置true，并且按照AccessDatabaseEngine_X64.exe即可正常读取。