本文摘自：C#Office.Interop.Excel.dll读写表格_笨鸟未必先飞的博客-CSDN博客_c# microsoft.office.interop.excel
一.写入excel
/// <summary>
/// 创建一个Excel表格
/// Excel中形如Cells[x][y]的写法，前面的数字是列，后面的数字是行!
/// Excel中的行、列都是从1开始的，而不是0
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_CreateExcel_Click(object sender, EventArgs e)
{
//创建一个文档实例
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.DisplayAlerts = false; //在程序运行时不被提示和警报消息打扰
app.Workbooks.Add(true);
app.ActiveSheet.Name = "总成绩统计表"; //工作表名
//合并单元格
Range range = app.get_Range("B2", "D2"); //合并B1~D1的位置
range.ClearComments(); //清空要合并的区域
range.MergeCells = true; //合并单元格
app.Cells[2][2].Value = "成绩统计表"; //设置单元格（刚刚合并的单元格）的内容
app.Rows[2].RowHeight = 27; //设置表格第二行的行高为27（磅）
app.Rows[2].HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置表格第二行的内容居中
app.Rows[2].RowHeight = 27; //设置表格第二行的行高为27（磅）
app.Rows[2].HorizontalAlignment = XlVAlign.xlVAlignCenter; //设置表格第二行的内容居中
app.Rows[3].RowHeight = 27;
app.Rows[3].HorizontalAlignment = XlVAlign.xlVAlignCenter;
app.Rows[4].RowHeight = 27;
app.Rows[4].HorizontalAlignment = XlVAlign.xlVAlignCenter;
app.Rows[5].RowHeight = 27;
app.Rows[5].HorizontalAlignment = XlVAlign.xlVAlignCenter;
app.Rows[6].RowHeight = 27;
app.Rows[6].HorizontalAlignment = XlVAlign.xlVAlignCenter;
app.Cells[2].ColumnWidth = 9; //设置表格中第二列的列宽为9
app.Cells[3].ColumnWidth = 9;
app.Cells[4].ColumnWidth = 9;
//给表格增加边框，外部和内部都增加了
for(int i = 2; i< dataGridView1.Rows.Count + 4;i++)
{
Range range1;
range1 = app.get_Range("B" + i.ToString(), "D" + i.ToString());
range1.Borders.LineStyle = XlLineStyle.xlContinuous;
}
for (int i = 0; i < dataGridView1.Rows.Count; i++) //行
{
for (int j = 0; j < dataGridView1.Columns.Count; j++) //列
{
app.Cells[j + 2][3] = dataGridView1.Columns[j].HeaderText; //将dataGridView 中的列标题写入表格
app.Cells[j + 2][i + 4] = dataGridView1.Rows[i].Cells[j].Value; //将dataGridView 中的三行数据写入表格
}
}
try
{
//设置新建表格的保存路径以及名称
string path = "C:\\Users\\Administrator\\Desktop\\成绩表.xlsx";
app.ActiveWorkbook.SaveAs(path);
app.Quit();
app = null;
MessageBox.Show("创建表格成功");
}
catch(Exception ex)
{
MessageBox.Show("创建表格失败" + ex.Message);
}
}
二.读excel
/// <summary>
/// 读Excel表格，更新dataGridView显示
/// ws.Cells[i,j] ，i是行，j是列
/// ws.Cells[i][j],i是列，j是行
/// </summary>
/// <param name="sender"></param>
/// <param name="e"></param>
private void btn_ReadExcel_Click(object sender, EventArgs e)
{
OpenFileDialog openfile = new OpenFileDialog();
openfile.Filter = "Excel 文件(*.xls;*.xlsx)|*.xls;*.xlsx"; //设置删选器
openfile.Title = "打开"; //对话框表标题
openfile.RestoreDirectory = true; //下次打开的是上次选择的目录
if (openfile.ShowDialog() == DialogResult.OK)
{
object missing = System.Reflection.Missing.Value;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Workbook wb = null;
//以只读的形式打开Excel文件
try
{
wb = excel.Workbooks.Open(openfile.FileName, missing, true, missing, missing, missing,
missing, missing, missing, true, missing, missing, missing, missing, missing);
}
catch(Exception ex)
{
MessageBox.Show("打开表格文件失败" + ex.Message);
}
//获取第一个工作表
Worksheet ws = wb.Worksheets.get_Item(1);
int rowsint = ws.UsedRange.Cells.Rows.Count; //获取行数
int colCount = ws.UsedRange.Cells.Columns.Count;//获取列数
//将Excel表格中的值更新到Excel
//因为我在程序初始化时已经设置dataGridView了行和列标题，所以这里直接读取行的数据更新一下dataGridView即可。没有Add行和更新列标题
for (int i = 4; i <= rowsint + 1; i++) //行，从表格的第四行开始循环
{
for (int j = 2; j <= colCount + 1; j++)
{
dataGridView1.Rows[i - 4].Cells[j - 2].Value = ws.Cells[i,j].Value;
}
}
}
}
关于Office.Interop.Excel.dll详细信息，参考微软：Microsoft.Office.Interop.Excel Namespace | Microsoft Learn
参考：使用Microsoft.Office.Interop.Excel批量编辑Excel文件_xyypublic的博客-CSDN博客_microsoft.office.interop.excel
本文内容由网友自发贡献，版权归原作者所有，本站不承担相应法律责任。如您发现有涉嫌抄袭侵权的内容，请联系:hwhale#tublm.com(使用前将#替换为@)
