文章介绍了如何在WindowsForm应用中，通过Microsoft.Office.Interop.Excel库操作Excel，包括创建工作簿、设置工作表、导入DataTable数据并格式化单元格，最终实现数据导出的功能。
我个人用winform窗体做的测试道理都是一样的
需要引入Microsoft.Office.Interop.Excel DLL文件
private void button17_Click(object sender, EventArgs e)
{
string fileName = "测试表";//这里定义导出Excel的名称
string saveFileName = "";
SaveFileDialog saveDialog = new SaveFileDialog();//实例化文件对象
saveDialog.DefaultExt = "xlsx";//文件默认扩展名
saveDialog.Filter = "Excel文件|*.xlsx";//获取或设置当前文件名筛选器字符串，该字符串决定对话框的“另存为文件类型”或“文件类型”框中出现的选择内容。
saveDialog.FileName = fileName;
saveDialog.ShowDialog();//打开保存窗口给你选择路径和设置文件名
saveFileName = saveDialog.FileName;
if (saveFileName.IndexOf(":") < 0) return; //被点了取消
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
if (xlApp == null)
{
MessageBox.Show("无法创建Excel对象，您的电脑可能未安装Excel");
return;
}
**//重点在这个方法中
DataTabletoExcel1(saveFileName);**
}
public void DataTabletoExcel1( string strFileName)
{
try
{
//ApplicationClass引入不进来需要设置引用文件Microsoft.Office.Interop.Excel嵌入互通类型为False即可 若是还是不行改为Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();
//xlApp.DefaultFilePath = ""; ///默认文件路径，将其设置路径后发现没什么变化。导出excel的路径还是在参数strFileName里设置
//xlApp.DisplayAlerts = true;
//xlApp.SheetsInNewWorkbook = 1;///返回或设置 Microsoft Excel 自动插入到新工作簿中的工作表数目。Long 类型，可读写。设置为2之后没发现什么区别
//创建一个新工作簿
Workbook xlBook = xlApp.Workbooks.Add("");
///在工作簿中得到sheet。
_Worksheet oSheet = (_Worksheet)xlBook.Worksheets[1];
//设置sheet名称
oSheet.Name = "图纸信息表";
#region 绘制列
///自定义方法，想sheet中绘制列
RangeBuild(oSheet, "A1", "A1", "'序号");
RangeBuild(oSheet, "B1", "B1", "'订单备注");
RangeBuild(oSheet, "C1", "C1", "'班组");
RangeBuild(oSheet, "D1", "D1", "'图名");
RangeBuild(oSheet, "E1", "E1", "'图号");
RangeBuild(oSheet, "F1", "F1", "'机型");
RangeBuild(oSheet, "G1", "G1", "'打印备注");
RangeBuild(oSheet, "H1", "H1", "'部门");
RangeBuild(oSheet, "I1", "I1", "'状态");
RangeBuild(oSheet, "J1", "J1", "'签收人");
RangeBuild(oSheet, "K1", "K1", "'打印时间");
RangeBuild(oSheet, "L1", "L1", "'归还时间");
if (excelsta == "-1")
{
//查询数据放入datatable中
System.Data.DataTable seldata = Common.selcetdtExecuteNonQusery1(ExcelOutStr);
for (int i = 0; i < seldata.Rows.Count; i++)
{
int xid = i + 2;
int xvalue = i + 1;
//序号
RangeBuild(oSheet, "A" + xid, "A" + xid, "'" + xvalue.ToString());
//订单备注
RangeBuild(oSheet, "B" + xid, "B" + xid, "'" + seldata.Rows[i][1].ToString());
//班组
RangeBuild(oSheet, "C" + xid, "C" + xid, "'" + seldata.Rows[i][2].ToString());
//图名
RangeBuild(oSheet, "D" + xid, "D" + xid, "'" + seldata.Rows[i][3].ToString());
RangeBuild(oSheet, "E" + xid, "E" + xid, "'" + seldata.Rows[i][4].ToString());
RangeBuild(oSheet, "F" + xid, "F" + xid, "'" + seldata.Rows[i][5].ToString());
RangeBuild(oSheet, "G" + xid, "G" + xid, "'" + seldata.Rows[i][6].ToString());
RangeBuild(oSheet, "H" + xid, "H" + xid, "'" + seldata.Rows[i][7].ToString());
RangeBuild(oSheet, "I" + xid, "I" + xid, "'" + seldata.Rows[i][8].ToString());
RangeBuild(oSheet, "J" + xid, "J" + xid, "'" + seldata.Rows[i][9].ToString());
RangeBuild(oSheet, "K" + xid, "K" + xid, "'" + seldata.Rows[i][10].ToString());
RangeBuild(oSheet, "L" + xid, "L" + xid, "'" + seldata.Rows[i][11].ToString());
}
}
else if (excelsta == "0")
{
System.Data.DataTable seldata = Common.selcetdtExecuteNonQusery1(ExcelOutStr);
for (int i = 0; i < seldata.Rows.Count; i++)
{
int xid = i + 2;
int xvalue = i + 1;
//序号
RangeBuild(oSheet, "A" + xid, "A" + xid, "'" + xvalue.ToString());
//订单备注
RangeBuild(oSheet, "B" + xid, "B" + xid, "'" + seldata.Rows[i][0].ToString());
//班组
RangeBuild(oSheet, "C" + xid, "C" + xid, "'" + seldata.Rows[i][1].ToString());
//图名
RangeBuild(oSheet, "D" + xid, "D" + xid, "'" + seldata.Rows[i][2].ToString());
RangeBuild(oSheet, "E" + xid, "E" + xid, "'" + seldata.Rows[i][3].ToString());
RangeBuild(oSheet, "F" + xid, "F" + xid, "'" + seldata.Rows[i][4].ToString());
RangeBuild(oSheet, "G" + xid, "G" + xid, "'" + seldata.Rows[i][5].ToString());
RangeBuild(oSheet, "H" + xid, "H" + xid, "'" + seldata.Rows[i][6].ToString());
RangeBuild(oSheet, "I" + xid, "I" + xid, "'" + seldata.Rows[i][7].ToString());
RangeBuild(oSheet, "J" + xid, "J" + xid, "'" + seldata.Rows[i][8].ToString());
RangeBuild(oSheet, "K" + xid, "K" + xid, "'" + seldata.Rows[i][9].ToString());
RangeBuild(oSheet, "L" + xid, "L" + xid, "'" + seldata.Rows[i][10].ToString());
}
}
#endregion
///保存,路径一块穿进去。否则回到一个很奇妙的地方，貌似是system32里 temp下....
MessageBox.Show(oSheet.Name + "导出成功");
oSheet.SaveAs(strFileName);
//关闭打开创建的EXCEL线程，
xlApp.Quit();
}
catch (Exception ee)
{
Common.RecordError("导出生成表格信息方法异常" + ee.ToString());
}
}
下面是字体设计的方法
private static void RangeBuild(_Worksheet oSheet, string startcell, string endcell, string value)
{
///创建一个区域对象。第一个参数是开始格子号，第二个参数是终止格子号。比如选中A1——D3这个区域。
Microsoft.Office.Interop.Excel.Range range = (Microsoft.Office.Interop.Excel.Range)oSheet.get_Range(startcell, endcell);
///合并方法，0的时候直接合并为一个单元格
range.Merge(0);
///合并单元格之后，设置其中的文本
range.Value = value;
//横向居中
range.HorizontalAlignment = XlVAlign.xlVAlignCenter;
///字体大小
range.Font.Size = 9;
///字体
range.Font.Name = "宋体";
///行高
range.RowHeight = 18.5;
//自动调整列宽
range.EntireColumn.AutoFit();
//填充颜色
// range.Interior.ColorIndex = 20;
//设置单元格边框的粗细
range.Cells.Borders.LineStyle = 1;
}
扫一扫
5986
到【灌水乐园】发言
