一、主要代码段
1.添加office的com组件引用
网上有,具体为右击程序段“引用”-添加引用-com-搜索12后,添加。
12.0和5.0是office的版本不同。12应该是可以exls版本操作,具体网查。
2.公式计算部分与另一文章一样
3.表格保存
private void ExportExcels(string fileName, DataGridView myDGV)
{
FileInfo finfo = new FileInfo(fileName);
//文件已存在,则打开并追加数据
if (finfo.Exists)
{
//设置文件属性,去除只读
finfo.Attributes = finfo.Attributes & ~FileAttributes.ReadOnly;
//读取文件属性,只读则返回false
bool fread = finfo.IsReadOnly;
//非只读属性则打开文件,开始添加数据
//开启Excel应用
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("未能创建Excel对象,可能您的机子未安装Excel");
return;
}
Microsoft.Office.Interop.Excel.Workbooks workbooks = xlApp.Workbooks;
Microsoft.Office.Interop.Excel.Workbook workbook = workbooks.Open(fileName, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1
xlApp.Visible = true; //设置excel前台运行
xlApp.DisplayAlerts = false; //设置显示确认修改提示
int row_cnt = worksheet.UsedRange.Rows.Count;//获取不为空的行数
int col_cnt = worksheet.UsedRange.Columns.Count;//获取不为空的列数
//写入标题
//for (int i = 0; i < myDGV.ColumnCount; i++) //myDGV.ColumnCount获取datagridview中显示的列数
//{
// worksheet.Cells[row_cnt + 1, i + 1] = myDGV.Columns[i].HeaderText;
//}
for (int r = 0; r < myDGV.Rows.Count-1; r++) //要写入的行数,r表示行
{
for (int i = 0; i < myDGV.ColumnCount; i++) //要写入的列数,i表示列
{
//可以正常追加添加内容至已有表格exl中
worksheet.Cells[r + row_cnt + 1, i + 1] = myDGV.Rows[r].Cells[i].Value.ToString();
}
System.Windows.Forms.Application.DoEvents();//起到实时响应的作用,不会出现假死的状态
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (fileName != "")
{
try
{
// workbook.Saved = true;//确认有修改过内容
workbook.Saved = true;
workbook.Save();
//workbook.SaveCopyAs(fileName);
//workbook.Close(false, Missing.Value, Missing.Value);//关闭打开的表
workbook.Close(true, fileName, null);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
}
}
//Excel程序退出
xlApp.Quit();
//sheet,book,xls设置为null,防止内存泄露
worksheet = null;
workbook = null;
xlApp = null;
GC.Collect();//强行销毁,系统回收资源
myDGV.Rows.Clear();
MessageBox.Show("文件: " + fileName + "添加成功", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
//文件不存在,则新建并进行保存
else
{
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog(); //文件保存对话框
saveDialog.DefaultExt = "xls"; //默认文件扩展名
saveDialog.Filter = "Excel文件|*.xls"; //对话框默认文件另存为的 扩展名
saveDialog.FileName = fileName; //对话框 文件名
saveDialog.ShowDialog(); //模态显示,模态窗体显示下,不允许操作其他窗体
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //查找文件名字符串中是否有:,<0说明没有盘符,被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("未能创建Excel对象,可能您的机子未安装Excel");
return;
}
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
//写入标题
for (int i = 0; i < myDGV.ColumnCount; i++) //myDGV.ColumnCount获取datagridview中显示的列数
{
worksheet.Cells[1, i + 1] = myDGV.Columns[i].HeaderText;
}
//写入数值
for (int r = 0; r < myDGV.Rows.Count; r++)//myDGV.Rows.Count获取集合中的行数
{
for (int i = 0; i < myDGV.ColumnCount; i++)
{
worksheet.Cells[r + 2, i + 1] = myDGV.Rows[r].Cells[i].Value;
}
System.Windows.Forms.Application.DoEvents();//起到实时响应的作用,不会出现假死的状态
}
worksheet.Columns.EntireColumn.AutoFit();//列宽自适应
if (saveFileName != "")
{
try
{
workbook.Saved = true;
workbook.SaveCopyAs(saveFileName);
//workbook.SaveCopyAs(fileName);
workbook.Close(false, Missing.Value, Missing.Value);//关闭打开的表
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
}
}
xlApp.Quit();
//sheet,book,xls设置为null,防止内存泄露
worksheet = null;
workbook = null;
xlApp = null;
GC.Collect();//强行销毁
myDGV.Rows.Clear();
MessageBox.Show("文件: " + fileName + "保存成功,清空数据!", "信息提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
二、关键点
{
// workbook.Saved = true;//确认有修改过内容
workbook.Saved = true;
workbook.Save();
//workbook.SaveCopyAs(fileName);
//workbook.Close(false, Missing.Value, Missing.Value);//关闭打开的表
workbook.Close(true, fileName, null);
}
catch (Exception ex)
{
MessageBox.Show("导出文件时出错,文件可能正被打开!\n" + ex.Message);
}
上段代码为确认表格存在,进行数据添加,需要利用workbook.Saved = true;来确认有修改过内容,在利用workbook.Save();进行数据保存;利用workbook.Close(true, fileName, null);ture来确认数据更改并进行保存。