c# winform程序如何引用excel导出文件

Question

如题所述

Answer 1

private void BtnExport_Click(object sender, EventArgs e) { string saveFileName = ""; SaveFileDialog saveDialog = new SaveFileDialog(); saveDialog.RestoreDirectory = true; saveDialog.DefaultExt = "xls"; saveDialog.Filter = "Excel文件|*.xls"; saveDialog.FileName = "Sheet1"; saveDialog.ShowDialog(); saveFileName = saveDialog.FileName; if (saveFileName.IndexOf(":") < 0) return; //点了取消 // Create a new DataTable. System.Data.DataTable table = new System.Data.DataTable("ParentTable"); // Declare variables for DataColumn and DataRow objects. DataColumn column; DataRow row; column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "就诊号"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); // Create new DataColumn, set DataType, // ColumnName and add to DataTable. column = new DataColumn(); column.DataType = System.Type.GetType("System.DateTime"); column.ColumnName = "就诊时间"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); // Create second column. column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "姓名"; column.AutoIncrement = false; column.Caption = "ParentItem"; column.ReadOnly = false; column.Unique = false; // Add the column to the table. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.Int32"); column.ColumnName = "年龄"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "婚姻状况"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "文化程度"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "职业"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "固定电话"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "手机"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "电子邮件"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "通信地址"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.Int32"); column.ColumnName = "病程"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "既往史"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); column = new DataColumn(); column.DataType = System.Type.GetType("System.String"); column.ColumnName = "其它"; column.ReadOnly = true; column.Unique = false; // Add the Column to the DataColumnCollection. table.Columns.Add(column); Authentication au = new Authentication(); PttInfo[] ptInfoArray = au.GetAllPatients(); List<string> marryStateList = new List<string>(); marryStateList.Add("未设"); marryStateList.Add("未婚"); marryStateList.Add("已婚"); marryStateList.Add("离婚"); string[] marryStateArray = marryStateList.ToArray(); List<string> cvStateList = new List<string>(); cvStateList.Add("未设"); cvStateList.Add("文盲"); cvStateList.Add("小学"); cvStateList.Add("初中"); cvStateList.Add("高中"); cvStateList.Add("本科"); cvStateList.Add("硕士"); cvStateList.Add("博士"); cvStateList.Add("博士后"); cvStateList.Add("更高"); string[] cvStateArray = cvStateList.ToArray(); for (int i = 0; i <ptInfoArray.Length; i++) { row = table.NewRow(); row["姓名"] = ptInfoArray[i].name; row["就诊号"] = ptInfoArray[i].pttid; row["就诊时间"]=ptInfoArray[i].consultdate; row["年龄"] = ptInfoArray[i].age; row["婚姻状况"]=marryStateArray[ptInfoArray[i].marrystate]; row["文化程度"] = cvStateArray[ptInfoArray[i].cvstate]; row["职业"] = ptInfoArray[i].job; row["固定电话"] = ptInfoArray[i].tel; row["手机"] = ptInfoArray[i].cellphone; row["电子邮件"] = ptInfoArray[i].email; row["通信地址"] = ptInfoArray[i].address; row["病程"] = ptInfoArray[i].duration; row["既往史"] = ptInfoArray[i].diseasehistory; row["其它"] = ptInfoArray[i].other; table.Rows.Add(row); } bool flag = ExportDataTable(table, saveFileName); if (flag) { MessageBox.Show("文件导出成功"); } } public static bool ExportDataTable(System.Data.DataTable dt, string filename) { try { if (filename != "") { if (filename.LastIndexOf(".xls") <= 0) { filename = filename + ".xls"; } if (System.IO.File.Exists(filename)) { System.IO.File.Delete(filename); } Excel.ApplicationClass xlApp = new Excel.ApplicationClass(); if (xlApp == null) { MessageBox.Show("无法创建Excel对象，可能您的机子未安装Excel"); return false; } Excel.Workbooks workbooks = xlApp.Workbooks; Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[1]; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { if (i == 0) { worksheet.Cells[1, j + 1] = dt.Columns[j].ColumnName; } worksheet.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString(); } } workbook.Saved = true; workbook.SaveCopyAs(filename); System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; workbooks.Close(); System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks); workbooks = null; xlApp.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp); xlApp = null; return true; } MessageBox.Show("文件名不能为空！