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; } }
private void consigneProteger() { Excel.Application xlApp; Excel.Workbook xlWorkBook; object misValue = System.Reflection.Missing.Value; xlApp = new Excel.ApplicationClass(); xlApp.Visible = true; xlApp.DisplayAlerts = false; xlWorkBook = xlApp.Workbooks.Open(prefaceNP, 0, false, 5, "", "", false, Excel.XlPlatform.xlWindows, "", true, false, 0, true, true, false); Excel.Worksheet xlWorkSheet = (Excel.Worksheet)xlWorkBook.Worksheets.get_Item("Historique-s"); Excel.Range range = xlWorkSheet.UsedRange; int rowcount = xlWorkSheet.UsedRange.Rows.Count; object[,] values = (object[, ])range.Value2; int rCnt = 0; int cCnt = 0; int col = 0; rCnt = range.Rows.Count; for (cCnt = 1; cCnt <= range.Columns.Count; cCnt++) { string valuecellabs = Convert.ToString(values[rCnt, cCnt]); if (Regex.Equals(valuecellabs, "15000")) { col = cCnt; break; } } //Routine pour modifier col XXXXX marquer ligne proteger -1 for (int i = 1; i < rowcount - 5; i++) { if ((xlWorkSheet.Cells[i, 3] as Excel.Range).Locked.ToString() == "True") { (xlWorkSheet.Cells[i, col] as Excel.Range).Value2 = "-1"; } } xlApp.Save(misValue); xlApp.Quit(); releaseObject(xlWorkSheet); releaseObject(xlWorkBook); releaseObject(xlApp); }
public static void DoForExcel(DataGridView dgv, string reportTitle) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); if (xlApp == null) { MessageBox.Show("Excel无法启动", "错误", MessageBoxButtons.OK, MessageBoxIcon.Error); } int rowIndex = 2; int colIndex = 0; Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); Microsoft.Office.Interop.Excel.Range range = xlApp.get_Range(xlApp.Cells[1, 1], xlApp.Cells[1, dgv.ColumnCount]); range.MergeCells = true; xlApp.ActiveCell.FormulaR1C1 = reportTitle; xlApp.ActiveCell.Font.Size = 18; xlApp.ActiveCell.Font.Bold = true; foreach (DataGridViewColumn column in dgv.Columns) { colIndex = colIndex + 1; xlApp.Cells[2, colIndex] = column.HeaderText; } for (int row = 0; row < dgv.Rows.Count; row++) { rowIndex = rowIndex + 1; for (int col = 0; col < dgv.Columns.Count; col++) { xlApp.Cells[rowIndex, col + 1] = dgv.Rows[row].Cells[col].Value.ToString(); } } xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[2, dgv.Columns.Count]).Font.Bold = true; xlApp.get_Range(xlApp.Cells[2, 1], xlApp.Cells[rowIndex, colIndex]).Borders.LineStyle = 1; xlApp.Cells.EntireColumn.AutoFit(); xlApp.Cells.VerticalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; xlApp.Cells.HorizontalAlignment = Microsoft.Office.Interop.Excel.Constants.xlCenter; try { xlApp.Save(System.DateTime.Now.Millisecond.ToString()); } catch { } finally { xlApp.Quit(); } }
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 { } }