C#读写EXCEL

Halcom · 发表于 2017-1-17 18:55:55

C#读写EXCEL：【1】参考链接：http://www.open-open.com/code/view/1430553318349
【2】添加引用-COM组件-找到Excel-添加
【3】using Microsoft.Office.Interop.Excel;

读取Excel

OpenFileDialog fileDialog = new OpenFileDialog();
fileDialog.ShowDialog();
string fileName = fileDialog.FileName;
DataSet ds = ReadExcel.ReadFromExcel(fileName);
object obj = ds.Tables[0].Rows[2][2];

复制代码

在 object obj处添加断点，可以查看数值
ReadExcel.ReadFromExcel函数如下：

using Microsoft.Win32;
using System;
using System.Collections.Generic;
using System.Collections.ObjectModel;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Threading;
using System.Windows;
using System.Windows.Input;
namespace Operate_Excel
{
public class ReadExcel
{
private static DataSet dataSet = new DataSet();
private static List<string> tableList = new List<string>();
public static DataSet ReadFromExcel(string filePath)
{
dataSet.Clear();
tableList.Clear();
string errorMessage = string.Empty;
dataSet = GetExcelData(filePath, ref errorMessage);
return dataSet;
}
private static DataSet GetExcelData(string filePath, ref string errorMeg)
{
try
{
string strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + filePath + ";Extended Properties='Excel 12.0;HDR=No;IMEX=1'";
// 遍历Excel表名
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
tableList.Add((String)dr["TABLE_NAME"]);
}
// 读取表中的数据到DataSet
DataSet ds = new DataSet();
foreach (string tableName in tableList)
{
OleDbDataAdapter da = new OleDbDataAdapter("select*from[" + tableName + "]", strConn);
da.Fill(ds, tableName);
}
return ds;
}
catch (Exception ex)
{
errorMeg = "read Excel file fail" + ex.Message + "!";
return null;
}
}
}
}

复制代码

写Excel

SaveFileDialog saveDialog = new SaveFileDialog();
saveDialog.DefaultExt = "xlsx";
saveDialog.Filter = "Excel文件*.xlsx|*.xlsx|Excel文件*.xls|.*xls";
saveDialog.ShowDialog();
string saveFileName = saveDialog.FileName;
WriteExcel WExcel = new WriteExcel();
WExcel.ExportExcel(saveFileName, "32", 2, 4); // 第2行第4列写入32这个数字
WExcel.ExportExcel(saveFileName, "33", 3, 4); // 第3行第4列写入33这个数字
WExcel.ExportExcel(saveFileName, "34", 4, 4); // 第4行第4列写入34这个数字
MessageBox.Show("执行完毕！！！");

复制代码

WExcel.ExportExcel函数如下：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using System.IO;
using System.Data;
namespace Operate_Excel
{
public class WriteExcel
{
public void ExportExcel(string saveFileName, string data,int RowCount, int ColumnCount)
{
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application ExcelApp;
try
{
ExcelApp = new Microsoft.Office.Interop.Excel.Application();
}
catch (Exception)
{
MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel");
return;
}
finally{ }
Microsoft.Office.Interop.Excel.Workbooks workbooks = ExcelApp.Workbooks;
// 创建一个新的EXCEL表格，原本的内容将被清空
//Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorksheet);
// 在指定的EXCEL中写入新的内容
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Add(@saveFileName);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
worksheet.Cells[RowCount + 1, ColumnCount + 1] = data;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开！n" + ex.Message);
}
}
ExcelApp.Quit();
GC.Collect();//强行销毁
}
}
}

复制代码

欢迎大家分享自己的代码！

		自动登录	找回密码
密码			立即注册