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
{
}
}