C#编程学习27: C#操作Excel从入门到精通

public class PublicMethod
{
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
public static void Kill(Microsoft.Office.Interop.Excel.Application excel)
{
IntPtr t = new IntPtr(excel.Hwnd);//得到这个句柄，具体作用是得到这块内存入口
int k = 0;
GetWindowThreadProcessId(t, out k); //得到本进程唯一标志k
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(k); //得到对进程k的引用
p.Kill(); //关闭进程k
}
}

方法二：

//创建进程对象
Process[] ExcelProcess = Process.GetProcessesByName("Excel");
//关闭进程
foreach (Process p in ExcelProcess)
{
p.Kill();
}

3 打开Excel数据表

以2019年上半年居民收入和消费支出情况数据为例，开展本博文的相关实验

3.1 由Excel应用打开和关闭数据表的两种方式

object missing = System.Reflection.Missing.Value;//设置object的默认值，需要添加名称空间using System.Reflection;

（1） open方式--打开已有文件

//打开实验数据
string str = @"E:C#Example200LearnExcelLearnExceldata.xlsx";
Excel.Workbook wb = myApp.Workbooks.Open(str);
Excel.WorkShee ws = myApp.WoekSheets.Add();
//....
ws.Save();
//关闭数据表
wb.Close();
myApp.Quit();
myApp = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);

（2） Add() -- 先创建表格之后再保存到指定路径的方法

//打开实验数据
string str = @"E:C#Example200LearnExcelLearnExceldata.xlsx";
Excel.Workbook wb = myApp.Workbooks.Add(true);
Excel.WorkShee ws = myApp.WorkSheets.Add();
//....
ws.SaveAs(str);
//关闭数据表
wb.Close();
myApp.Quit();
myApp = null;
System.Runtime.InteropServices.Marshal.ReleaseComObject(myApp);

3.2 打开数据表的工作表

//根据索引获取感兴趣的数据表
Excel.Worksheet ws = wb.Worksheets[1];//sheet的索引从1开始
//获取工作表的名称
string wsName = ws.Name;
//数据表的行数
int wsRows = ws.Rows.Count;
//数据表的列数
int wsColumns = ws.Columns.Count;
//数据表的有效数据行数
int wsUsedRows = ws.UsedRange.Rows.Count;
//数据表的有效数据列数
int wsUsedColumns = ws.UsedRange.Columns.Count;

3.3添加数据表

先判断是否存在同名的数据表，不存在再创建

for (int i = 1; i < openwb.Worksheets.Count; i++) //循环sheet工作表
{
string sheet = ((Worksheet)openwb.Worksheets[i]).Name;
sheets.Add(sheet);
}
//--------------------------------定义新增Excel工作表名称------------------------------
string addsheet = "新增工作表";
if (sheets.Contains(addsheet)) //判断Excel中是否存在该工作表
{
Console.WriteLine("新增工作表已存在");
}
else //没有则新增该工作表
{
ws = (Worksheet)openwb.Worksheets.Add(missing, missing, 1, missing); //添加新的Excel工作表
ws.Name = addsheet;
openwb.Save();//保存Excel文件
App.DisplayAlerts = false;//不显示提示对话框
//App.Visible = true;
}

3.3 对数据表的操作

字体相关的设置

属性	功能
Size	字号的大小
Bold	是否加粗
Italic	是否倾斜
colorIndex	文字的颜色
SubScript	是否下标
Superscript	是否上标
Color	字体颜色

行高列宽设置：

使用ColumnWidth和RowHeight两个属性设置

ws.Rows[1, Missing.Value].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);//删除第一行
ws.Cells[2, 1].HorizontalAlignment = XlVAlign.xlVAlignCenter;//垂直居中
ws.Cells[2, 1].HorizontalAlignment = XlHAlign.xlHAlignCenter;//水平居中
ws.Rows[1, Missing.Value].Font.Bold = true;//设置是否粗体
ws.Cells[1, "A"].Font.Size = 8;//设置字体大小
ws.Rows[1, Missing.Value].Interior.ColorIndex = 3;//设置第一行为红色
ws.get_Range("A1", "P1").Borders.LineStyle = 1;//设置表格的线宽
//删除数据表的第一行第一个元素（下边数据上移）
ws.Cells[1, 1].Delete(Excel.XlDeleteShiftDirection.xlShiftUp);
//删除工作表第一行第三列(右侧单元格左移)
ws.Cells[1, 3].Delete(Excel.XlDeleteShiftDirection.xlShiftToLeft);
ws.Rows[3, Missing.Value].RowHeight = 5;//设置行高
ws.Rows[3, Missing.Value].ColumnWidth = 5;//设置列宽
ws.get-Range("A1").EntireColumn.NumberFormat = "@";//将A1列设置为【文本】格式

3.4 思维导图

4 对单元格的操作

4.1 获取单元格的信息

Excel.Range rang = (Excel.Range)ws.Cells[2, 2];//单元格B2
rang.EntireColumn.AutoFit();//自动列宽
string content = rang.Text;//该单元格文本
double height = rang.Height;//单元格的高度
double width = rang.Width;//单元格的宽度

4.2 设置单元格的值

//设置单元格的值
ws.Cells[2, 3] = "null";

4.3 合并单元格

//合并单元格
Excel.Range mergeRange = ws.get_Range("A1", "b2");
mergeRange.Merge();

4.4 将DataTable数据添加到数据表

DataTable dt = new DataTable();
//添加表头
dt.Columns.Add("姓名");
dt.Columns.Add("年龄");
dt.Columns.Add("性别");
//添加数据项
dt.Rows.Add("姓名", "年龄", "性别");
dt.Rows.Add("张三", "23", "男");
dt.Rows.Add("李思", "12", "女");
dt.Rows.Add("张琴", "33", "女");
dt.Rows.Add("王高", "62", "男");
dt.Rows.Add("郑涛", "56", "男");
int rowIndex = 1;
foreach (DataRow row in dt.Rows)
{
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
ws.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
rowIndex++;
}

5 excel与Datatable的数据交换

5.1 将Excel的数据读入到DataTable中

/// <summary>
/// 从excel文件读取内容
/// </summary>
/// <param name="fileName">excel文件名</param>
/// <returns>获取读取数据的表</returns>
static public DataTable ImportFromExcel(string fileName)
{
Excel.Application excelApp = null;
Excel.Workbooks wbks = null;
Excel._Workbook wbk = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;//是打开不可见
wbks = excelApp.Workbooks;
wbk = wbks.Add(fileName);
object Nothing = Missing.Value;
Excel._Worksheet whs;
whs = (Excel._Worksheet)wbk.Sheets[2];//获取第一张工作表
whs.Activate();
DataTable dt = new DataTable(whs.Name);
//读取excel表格的列标题
int col_count=whs.UsedRange.Columns.Count;
for (int col = 1; col <= col_count; col++)
{
dt.Columns.Add(((Excel.Range)whs.Cells[1,col]).Text.ToString());
}
//读取数据
for (int row = 2; row <= whs.UsedRange.Rows.Count; row++)
{
DataRow dr = dt.NewRow();
for (int col = 1; col < col_count; col++)
{
dr[col - 1] = ((Excel.Range)whs.Cells[row, col]).Text.ToString();
}
dt.Rows.Add(dr);
}
return dt;
}
catch (Exception e)
{
throw e;
}
finally
{
//wbks.Close();//关闭工作簿
excelApp.Quit();//关闭excel应用程序
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);//释放excel进程
excelApp = null;
}
}

5.2 将DataTable数据输出到Excel中

/// <summary>
/// 导出数据到excel文件
/// </summary>
/// <param name="dt">要导出的数据集</param>
/// <returns>生成的文件名</returns>
static public string ExportToExcel(DataTable dt)
{
Excel.Application excelApp = null;
Excel.Workbooks wbks = null;
Excel._Workbook wbk = null;
try
{
excelApp = new Excel.Application();
excelApp.Visible = false;//是打开不可见
wbks = excelApp.Workbooks;
wbk = wbks.Add(true);
String version = excelApp.Version;//获取你使用的excel 的版本号
int FormatNum;//保存excel文件的格式
if (Convert.ToDouble(version) < 12)//You use Excel 97-2003
{
FormatNum = -4143;
}
else//you use excel 2007 or later
{
FormatNum = 56;
}
object Nothing = Missing.Value;
Excel._Worksheet whs;
whs = (Excel._Worksheet)wbk.Sheets[1];//获取第一张工作表
whs.Activate();
//写入标题行
int rowIndex=1;
for (int col = 0; col < dt.Columns.Count; col++)
{
whs.Cells[rowIndex, col+1] = dt.Columns[col].Caption.ToString();
}
rowIndex++;
//写入数据内容
foreach (DataRow row in dt.Rows)
{
for (int colIndex = 0; colIndex < dt.Columns.Count; colIndex++)
{
whs.Cells[rowIndex, colIndex + 1] = row[colIndex].ToString();
}
rowIndex++;
}
excelApp.DisplayAlerts = false;
//保存excel文件
//wbk.SaveCopyAs(@"D: est.xls");
string newFileName = @"D:导出的excel文件.xls";
wbk.SaveAs(newFileName, FormatNum);
//关闭文件
wbk.Close(false, Nothing, Nothing);
return newFileName;
}
catch (Exception e)
{
throw e;
}
finally
{
//wbks.Close();//关闭工作簿
excelApp.Quit();//关闭excel应用程序
System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);//释放excel进程
excelApp = null;
}
}

5.3 海量DataTable导出到Excel

将DataTable中的大量数据导出到Excel表格中。

但每张Excel表单只能有65536行，所以当DataTable数据多于65536行时，Excel要考虑分页功能

代码在Office 2003 环境下通过。

using Excel = Microsoft.Office.Interop.Excel;
public Excel.Application m_xlApp = null;
/// <summary>
/// 将DataTable数据导出到Excel表
/// </summary>
/// <param name="tmpDataTable">要导出的DataTable</param>
/// <param name="strFileName">Excel的保存路径及名称</param>
public void DataTabletoExcel(System.Data.DataTable tmpDataTable, string strFileName)
{
if (tmpDataTable == null)
{
return;
}
long rowNum = tmpDataTable.Rows.Count;//行数
int columnNum = tmpDataTable.Columns.Count;//列数
Excel.Application m_xlApp = new Excel.Application();
m_xlApp.DisplayAlerts = false;//不显示更改提示
m_xlApp.Visible = false;
Excel.Workbooks workbooks = m_xlApp.Workbooks;
Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
try
{
if (rowNum > 65536)//单张Excel表格最大行数
{
long pageRows = 65535;//定义每页显示的行数,行数必须小于65536
int scount = (int)(rowNum / pageRows);//导出数据生成的表单数
if (scount * pageRows < rowNum)//当总行数不被pageRows整除时，经过四舍五入可能页数不准
{
scount = scount + 1;
}
for (int sc = 1; sc <= scount; sc++)
{
if (sc > 1)
{
object missing = System.Reflection.Missing.Value;
worksheet = (Excel.Worksheet)workbook.Worksheets.Add(
missing, missing, missing, missing);//添加一个sheet
}
else
{
worksheet = (Excel.Worksheet)workbook.Worksheets[sc];//取得sheet1
}
string[,] datas = new string[pageRows + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;//表头信息
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int init = int.Parse(((sc - 1) * pageRows).ToString());
int r = 0;
int index = 0;
int result;
if (pageRows * sc >= rowNum)
{
result = (int)rowNum;
}
else
{
result = int.Parse((pageRows * sc).ToString());
}
for (r = init; r < result; r++)
{
index = index + 1;
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[index, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;//Sheet表最大化
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[index + 1, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
}
else
{
string[,] datas = new string[rowNum + 1, columnNum];
for (int i = 0; i < columnNum; i++) //写入字段
{
datas[0, i] = tmpDataTable.Columns[i].Caption;
}
Excel.Range range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, columnNum]);
range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Bold = true;
range.Font.Size = 9;
int r = 0;
for (r = 0; r < rowNum; r++)
{
for (int i = 0; i < columnNum; i++)
{
object obj = tmpDataTable.Rows[r][tmpDataTable.Columns[i].ToString()];
datas[r + 1, i] = obj == null ? "" : "'" + obj.ToString().Trim();//在obj.ToString()前加单引号是为了防止自动转化格式
}
System.Windows.Forms.Application.DoEvents();
//添加进度条
}
Excel.Range fchR = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
fchR.Value2 = datas;
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应。
m_xlApp.WindowState = Excel.XlWindowState.xlMaximized;
range = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[rowNum + 1, columnNum]);
//range.Interior.ColorIndex = 15;//15代表灰色
range.Font.Size = 9;
range.RowHeight = 14.25;
range.Borders.LineStyle = 1;
range.HorizontalAlignment = 1;
}
workbook.Saved = true;
workbook.SaveCopyAs(strFileName);
}
catch (Exception ex)
{
MessageBox.Show("导出异常：" + ex.Message, "导出异常", MessageBoxButtons.OK, MessageBoxIcon.Warning);
}
finally
{
EndReport();
}
}
/// <summary>
/// 退出报表时关闭Excel和清理垃圾Excel进程
/// </summary>
private void EndReport()
{
object missing = System.Reflection.Missing.Value;
try
{
m_xlApp.Workbooks.Close();
m_xlApp.Workbooks.Application.Quit();
m_xlApp.Application.Quit();
m_xlApp.Quit();
}
catch { }
finally
{
try
{
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Workbooks);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp.Application);
System.Runtime.InteropServices.Marshal.ReleaseComObject(m_xlApp);
m_xlApp = null;
}
catch { }
try
{
//清理垃圾进程
this.killProcessThread();
}
catch { }
GC.Collect();
}
}
/// <summary>
/// 杀掉不死进程
/// </summary>
private void killProcessThread()
{
ArrayList myProcess = new ArrayList();
for (int i = 0; i < myProcess.Count; i++)
{
try
{
System.Diagnostics.Process.GetProcessById(int.Parse((string)myProcess[i])).Kill();
}
catch { }
}
}

6 Excel数据快速写出方法

6.1 将DataTable的数据写入到数组中，然后在整体输出

//快速写入（先写入数组，然后一次性将数组写入到EXCEL中）
private void CopyDataToSheet(System.Data.DataTable Table, _Worksheet Sheet)
{
int colCount, rowCount;
colCount = Table.Columns.Count;
rowCount = Table.Rows.Count;
Range range;
//写入标题行
range = Sheet.get_Range("A1", Missing.Value);
range = range.get_Resize(1, colCount);
object[,] headerData = new object[1, colCount];
for (int iCol = 0; iCol < colCount; iCol++)
{
headerData[0, iCol] = Table.Columns[iCol].ColumnName;
}
range.set_Value(Missing.Value, headerData);
//写入数据行
range = Sheet.get_Range("A2", Missing.Value);
range = range.get_Resize(rowCount, colCount);
object[,] cellData = new object[rowCount, colCount];
for (int iRow = 0; iRow < rowCount; iRow++)
{
for (int iCol = 0; iCol < colCount; iCol++)
{
cellData[iRow, iCol] = Table.Rows[iRow][iCol].ToString();
}
}
range.set_Value(Missing.Value, cellData);
}

6.2 将Grid的数据写出到Excel中

public static void OfficeExcel(Type type, SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)
{
dynamic _app = new Microsoft.Office.Interop.Excel.Application();
dynamic _workbook;
_workbook = _app.Workbooks.Add(true);
_Worksheet objSheet;
objSheet = _workbook.ActiveSheet;
Range range;
try
{
range = objSheet.get_Range("A1", Missing.Value);
range = range.get_Resize(rowsStr.Count, colsStr.Count);
object[,] saRet = new object[rowsStr.Count, colsStr.Count];
for (int iRow = 0; iRow < rowsStr.Count; iRow++)
{
int row = rowsStr[iRow];
for (int iCol = 0; iCol < colsStr.Count; iCol++)
{
int col = colsStr[iCol];
saRet[iRow, iCol] = grid[row, col].Value;
}
}
range.set_Value(Missing.Value, saRet);
_app.Visible = true;
_app.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}

6.3 WPS的操作

需要注意的是在此引用了wps的 Kingsoft ET 2.0 Object Library（WPS需要用到 ET.dll 和 KSO.dll），在电脑中下载了wsp之后在引用的COM中引用。我的开发环境中在其它地方用到Microsoft.Office.Interop.Excel.dll，因此在声明Excel的时候默认是office的，WPS需要加上ET引用。

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using SCFBaseLib;
using TYYW.AGTJ.Common;
using System.Drawing;
using System.Reflection;
using Microsoft.Office.Interop.Excel;
//以上是所需要的引用
#region 导出SourceGrid数据（最新版，批量快速输出）
/// <summary>
/// 导出SourceGrid数据
/// </summary>
/// <param name="grid">SourceGrid</param>
/// <param name="rowsStr">需要导出的行</param>
/// <param name="colsStr">需要导出的列</param>
//Excel导出的时候有两种软件插件可以使用（一种是office一种wps），因为各个插件的dll使用的方法不一样，因此要判断用户安装了哪个软件。
public static void NewExportSourceGridCell(SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)
{
//个人做的是政府项目，讲究国产化，在这里我先判断用户是否安装了wps。
string excelType = "wps";
Type type;
type = Type.GetTypeFromProgID("ET.Application");//V8版本类型
if (type == null)//没有安装V8版本
{
type = Type.GetTypeFromProgID("Ket.Application");//V9版本类型
if (type == null)//没有安装V9版本
{
type = Type.GetTypeFromProgID("Kwps.Application");//V10版本类型
if (type == null)//没有安装V10版本
{
type = Type.GetTypeFromProgID("EXCEL.Application");//MS EXCEL类型
excelType = "office";
if (type == null)
{
ModuleBaseUserControl.ShowError("检测到您的电脑上没有安装office或WSP软件，请先安装！");
return;//没有安装Office软件
}
}
}
}
if (excelType == "wps")
{
WpsExcel(type, grid, rowsStr, colsStr);
}
else
{
OfficeExcel(type, grid, rowsStr, colsStr);
}
}
//安装了wps
public static void WpsExcel(Type type, SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)
{
dynamic _app = Activator.CreateInstance(type); //根据类型创建App实例
dynamic _workbook; //声明一个文件
_workbook = _app.Workbooks.Add(Type.Missing); //创建一个Excel
ET.Worksheet objSheet; //声明Excel中的页
objSheet = _workbook.ActiveSheet; //创建一个Excel
ET.Range range;
try
{
range = objSheet.get_Range("A1", Missing.Value);
object[,] saRet = new object[rowsStr.Count, colsStr.Count]; //声明一个二维数组
for (int iRow = 0; iRow < rowsStr.Count; iRow++) //把sourceGrid中的数据组合成二维数组
{
int row = rowsStr[iRow];
for (int iCol = 0; iCol < colsStr.Count; iCol++)
{
int col = colsStr[iCol];
saRet[iRow, iCol] = grid[row, col].Value;
}
}
range.set_Value(ET.ETRangeValueDataType.etRangeValueDefault, saRet); //把组成的二维数组直接导入range
_app.Visible = true;
_app.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
//安装了office
public static void OfficeExcel(Type type, SourceGrid.Grid grid, List<int> rowsStr, List<int> colsStr)
{
dynamic _app = new Microsoft.Office.Interop.Excel.Application();
dynamic _workbook;
_workbook = _app.Workbooks.Add(true);
_Worksheet objSheet;
objSheet = _workbook.ActiveSheet;
Range range;
try
{
range = objSheet.get_Range("A1", Missing.Value);
range = range.get_Resize(rowsStr.Count, colsStr.Count);
object[,] saRet = new object[rowsStr.Count, colsStr.Count];
for (int iRow = 0; iRow < rowsStr.Count; iRow++)
{
int row = rowsStr[iRow];
for (int iCol = 0; iCol < colsStr.Count; iCol++)
{
int col = colsStr[iCol];
saRet[iRow, iCol] = grid[row, col].Value;
}
}
range.set_Value(Missing.Value, saRet);
_app.Visible = true;
_app.UserControl = true;
}
catch (Exception theException)
{
String errorMessage;
errorMessage = "Error: ";
errorMessage = String.Concat(errorMessage, theException.Message);
errorMessage = String.Concat(errorMessage, " Line: ");
errorMessage = String.Concat(errorMessage, theException.Source);
MessageBox.Show(errorMessage, "Error");
}
}
#endregion

7 参考博文

专栏1：C#实战开发历程

专栏2：C#读写Excel文件（6篇博文）

博文3：C#报表数据批量快速导出到Excel（百万级数据秒级内完成）

博文4：C#将DataTable海量数据导出到Excel