public bool Save(System.Data.DataTable excelTable, string stFilename) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = excelTable.Rows[i][j].ToString(); wSheet.Cells[i + 2, j + 1] = str; } } } int size = excelTable.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(stFilename); app.SaveWorkspace(stFilename); app.Quit(); app = null; return(true); } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return(false); } finally { } }
public static bool SaveDataToExcel <T>(List <T> tList, string tableName, string filePath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; PropertyInfo[] tPropertys = typeof(T).GetProperties(); if (tList.Count > 0) { int row = 0; row = tList.Count; int col = tPropertys.Length; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { var proValue = tPropertys[j].GetValue(tList[i], null); if (proValue != DBNull.Value) { string str = Convert.ToString(proValue); wSheet.Cells[i + 2, j + 1] = str; } } } for (int i = 0; i < col; i++) { wSheet.Cells[1, 1 + i] = tPropertys[i].Name; } } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath); app.SaveWorkspace(filePath); app.Quit(); app = null; return(true); } catch (Exception ex) { throw ex; } }
public static bool SaveDataTableToExcel(System.Data.DataTable excelTable, string filePath) { Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.ApplicationClass(); try { app.Visible = false; Workbook wBook = app.Workbooks.Add(true); Worksheet wSheet = wBook.Worksheets[1] as Worksheet; if (excelTable.Rows.Count > 0) { int row = 0; row = excelTable.Rows.Count; int col = excelTable.Columns.Count; for (int i = 0; i < row; i++) { for (int j = 0; j < col; j++) { string str = excelTable.Rows[i][j].ToString(); wSheet.Cells[i + 2, j + 1] = str; } } } int size = excelTable.Columns.Count; for (int i = 0; i < size; i++) { wSheet.Cells[1, 1 + i] = excelTable.Columns[i].ColumnName; } //设置禁止弹出保存和覆盖的询问提示框 app.DisplayAlerts = false; app.AlertBeforeOverwriting = false; //保存工作簿 wBook.Save(); //保存excel文件 app.Save(filePath); app.SaveWorkspace(filePath); app.Quit(); app = null; return true; } catch (Exception err) { MessageBox.Show("导出Excel出错!错误原因:" + err.Message, "提示信息", MessageBoxButtons.OK, MessageBoxIcon.Information); return false; } finally { } }
/// <summary> /// !!!!!!!!!!!!!!!!!!! /// 注意:该函数存在问题!未进行完善 /// 写入excel数据 /// </summary> /// <param name="ent">预警规则实体</param> /// <param name="selectedRowID">选择行编号</param> /// <returns>是否插入成功?true:false</returns> public static bool updateInfoToExcelSheet(PreWarningRules ent, int selectedRowID, string sheetName) { bool ret = false; Excel.Application objExcelApp = null; Excel.Workbooks objExcelWorkBooks = null; Excel.Workbook objExcelWorkBook = null; Excel.Worksheet objExcelWorkSheet = null; try { objExcelApp = new Excel.ApplicationClass(); objExcelWorkBooks = objExcelApp.Workbooks; objExcelWorkBook = objExcelWorkBooks.Open(_excelFilePath, 0, false, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, false, false); objExcelWorkSheet = (Worksheet)objExcelWorkBook.Worksheets[sheetName]; objExcelWorkSheet.Select(Type.Missing); Worksheet objExcelWorkSheetTemp = (Worksheet)objExcelApp.ActiveSheet; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 1] = ent.RuleCode; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 2] = ent.RuleType; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 3] = ent.WarningType; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 4] = ent.WarningLevel; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 5] = ent.SuitableLocation; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 6] = ent.RuleDescription; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 7] = ent.IndicatorType; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 8] = ent.Operator; objExcelWorkSheetTemp.Cells[selectedRowID + 1, 9] = ent.ModifyDate.ToString(); objExcelWorkSheetTemp.Cells[selectedRowID + 1, 10] = ent.Remarks;//MARK FIELD //objExcelApp.Visible = true; objExcelApp.DisplayAlerts = false; objExcelApp.SaveWorkspace(); ret = true; } catch (Exception ex) { Alert.alert(ex.Message); ret = false; } finally { if (objExcelApp != null) { objExcelApp.Quit(); GC.Collect();//不加该行代码Excel不会退出! } } return(ret); }