/// <summary>
/// 导出到 Excel 文件
/// </summary>
/// <param name="rs">当前记录</param>
/// <param name="flds">要导出的字段</param>
/// <param name="fn">文件的路径文件名</param>
/// <param name="dcfg">词典配置工具</param>
public static void ExportToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn, DictionaryConfigure dcfg)
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 建立Excel对象
excel.Application.Workbooks.Add(true); // Excel表为添加状态
for (int i = 0; i < flds.Length; i++) // 填充表头
{
excel.Cells[1, i + 1] = flds[i].Name;
}
for (int i = 0; i < rs.Length; i++) // 填充数据
{
for (int j = 0; j < flds.Length; j++)
{
string txt = rs[i].getStringValue(flds[j].ID);
if (!flds[j].DictionaryID.Equals(""))
{
txt = Business.DictionaryConfigure.GetCatalogNameByDictionary(txt, dcfg.getDictionary(flds[j].DictionaryID));
}
if (flds[j].EditIsDate)
{
txt = (txt.Length > 8 ? txt.Substring(0, 8) : txt);
txt = Utility.StringUtility.Alt(txt, "????-??-??", "?");
}
excel.Cells[i + 2, j + 1] = txt;
}
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
KillExcelProcess(excel);
}
/// <summary>
/// 获取进程标识
/// </summary>
/// <param name="hwnd">输入参数:句柄</param>
/// <param name="ID">输出参数:进程标识</param>
/// <returns></returns>
[DllImport("User32.dll", CharSet = CharSet.Auto)]
public static extern int GetWindowThreadProcessId(IntPtr hwnd, out int ID);
/// <summary>
/// 关闭 Excel 进程
/// </summary>
/// <param name="excel"></param>
private static void KillExcelProcess(Microsoft.Office.Interop.Excel.ApplicationClass excel)
{
//------------ 方法 1 -----------------
//excel.Quit();
//excel = null;
//GC.Collect(); // 垃圾回收
//------------ 方法 2 -----------------
IntPtr handler = new IntPtr(excel.Hwnd); // 句柄
int processid = 0; // 进程标识
GetWindowThreadProcessId(handler, out processid);
System.Diagnostics.Process p = System.Diagnostics.Process.GetProcessById(processid); // 进程
p.Kill(); // 杀除进程
//-----------------------------
}
/// <summary>
/// 从指定的Excel文件导入
/// </summary>
/// <param name="strFileName">导入文件</param>
/// <returns></returns>
public DataSet importFromExcel(string strFileName)
{
if (strFileName == "") return null;
string strConn = " Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + strFileName + ";Extended Properties=Excel 8.0";
OleDbDataAdapter excelDA = new OleDbDataAdapter("select * from [Sheet1$]", strConn); // 连接字符串
DataSet ds = new DataSet(); // 建立数据集,用于存放导入Excel的数据
excelDA.Fill(ds, "ExcelInfo"); // 填充DataSet
return ds;
}
/// <summary>
/// 将记录集导出到 Excel 文件
/// </summary>
/// <param name="rs">当前记录集</param>
/// <param name="flds">要导出的字段</param>
/// <param name="fn">文件的路径文件名</param>
public static void RecordsToExcel(DataAccess.Record[] rs, DataAccess.Field[] flds, string fn)
{
Microsoft.Office.Interop.Excel.ApplicationClass excel = new Microsoft.Office.Interop.Excel.ApplicationClass(); // 建立Excel对象
excel.Application.Workbooks.Add(true); // Excel表为添加状态
for (int i = 0; i < flds.Length; i++) // 填充表头
{
excel.Cells[1, i + 1] = flds[i].Name;
}
for (int i = 0; i < rs.Length; i++) // 填充数据
{
for (int j = 0; j < flds.Length; j++)
{
excel.Cells[i + 2, j + 1] = rs[i].getStringValue(flds[j].ID);
}
}
excel.Visible = false;
excel.DisplayAlerts = false;
excel.ActiveWorkbook.SaveAs(fn, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel7, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
KillExcelProcess(excel);
}