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);
}
