Microsoft.Office.Interop.Excel
如何用这个服务生成Excel文件？
------解决方案--------------------------------------------------------
- C# code
using System;using System.Collections.Generic;using System.Linq;using System.Text;using System.IO;using Microsoft.Office.Interop.Excel;using System.Data.OleDb;using DAL;namespace BLL{ public class OperateDataFromExcel { public string strError = null;//错误信息 /// <summary> /// 通过向单元格写数据的方法将数据写入Excel /// </summary> /// <param name="dt">数据表</param> /// <param name="strExcelFileName">Excel文件的路径名</param> /// <param name="strTableName">文件名(sheet名)</param> /// <param name="strTitle">数据表的标题(空或null则不写)</param> /// <param name="bIsOpenExcel">是否立即打开Excel表</param> /// <returns>0--成功,-1--导入到Excel失败,-2--销毁进程失败,-3打开Excel表失败</returns> public int WriteToExcelByCell(System.Data.DataTable dt, string strExcelFileName, string strTableName, string strTitle, bool bIsOpenExcel) { DateTime datetime = DateTime.Now; try { Application excelApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); excelApp.DisplayAlerts = true; excelApp.SheetsInNewWorkbook = 1; Workbook excelBook = excelApp.Workbooks.Add(Type.Missing); Worksheet excelSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelBook.ActiveSheet; excelSheet.Name = strTableName; int nRowIndex = 1;//行号 if (string.IsNullOrEmpty(strTitle) == false) { string a = (Convert.ToChar(64 + dt.Columns.Count)).ToString() + "1"; Range rH = excelSheet.get_Range("A1", a); rH.Merge(0); rH.HorizontalAlignment = XlVAlign.xlVAlignCenter; rH.VerticalAlignment = XlVAlign.xlVAlignCenter; excelApp.Cells[nRowIndex++, 1] = strTitle; } for (int i = 1; i <= dt.Columns.Count; i++) { excelApp.Cells[nRowIndex, i] = dt.Columns[i - 1].ColumnName; } nRowIndex++; for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { excelApp.Cells[nRowIndex, j + 1] = dt.Rows[i][j]; } nRowIndex++; } excelBook.Saved = true; excelBook.SaveCopyAs(strExcelFileName); if (excelApp != null) { excelApp.Workbooks.Close(); excelApp.Quit(); int generation = System.GC.GetGeneration(excelApp); System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp); excelApp = null; System.GC.Collect(generation); } GC.Collect();//强行销毁 } catch (Exception ex) { strError = "导入到Excel出错:" + ex.Message; return -1; } if (KillExcelProcess(datetime) == false) { return -2; } if (bIsOpenExcel == true) { try { System.Diagnostics.Process.Start(strExcelFileName); } catch (Exception ex) { strError = "打开Excel表失败:" + ex.Message; return -3; } } return 0; } /// <summary> /// 销毁进程 /// </summary> /// <param name="datetime">销毁进程的时间</param> /// <returns></returns> private bool KillExcelProcess(DateTime datetime) { try { System.Diagnostics.Process[] excelProc = System.Diagnostics.Process.GetProcessesByName("EXCEL"); for (int m = 0; m < excelProc.Length; m++) { if (datetime < excelProc[m].StartTime) { excelProc[m].Kill(); } } return true; } catch (Exception ex) { strError = "销毁进程出错:" + ex.Message; return false; } } #region 通过向单元格写数据的方法将数据写入Excel /// <summary> /// 通过向单元格写数据的方法将数据写入Excel /// </summary> /// <param name="dt">数据表</param> /// <param name="strExcelFileName">Excel文件的路径名</param> /// <param name="strTitle">数据表的标题(空或null则不写)</param> /// <param name="bIsOpenExcel">是否立即打开Excel表</param> /// <returns>0--成功,-1--导入到Excel失败,-2--销毁进程失败,-3打开Excel表失败</returns> public int WriteToExcelByTab(System.Data.DataTable dt, string strExcelFileName, string strTitle, bool bIsOpenExcel) { DateTime datetime = DateTime.Now; try { System.IO.FileStream fs = new System.IO.FileStream(strExcelFileName, System.IO.FileMode.Create); System.IO.StreamWriter sw = new System.IO.StreamWriter(fs, System.Text.Encoding.Unicode); if (string.IsNullOrEmpty(strTitle) == false) { sw.WriteLine(strTitle); } for (int i = 0; i < dt.Columns.Count; i++) { sw.Write(dt.Columns[i].ColumnName + "\t"); } sw.Write("\n"); for (int i = 0; i < dt.Rows.Count; i++) { for (int j = 0; j < dt.Columns.Count; j++) { sw.Write(dt.Rows[i][j].ToString() + "\t"); } sw.Write("\n"); } sw.Close(); fs.Close(); } catch (Exception ex) { strError = "导入到Excel出错:" + ex.Message; return -1; } if (KillExcelProcess(datetime) == false) { return -2; } if (bIsOpenExcel == true) { try { System.Diagnostics.Process.Start(strExcelFileName); } catch (Exception ex) { strError = "打开Excel表失败:" + ex.Message; return -3; } } return 0; } /// <summary> /// /// </summary> /// <param name="dt">需要填充的数据表</param> /// <param name="strExcelFileName">Excel文件的路径名</param> /// <param name="strTableName">文件名(sheet名)</param> /// <param name="strSelectField">需要查询的字段和要翻译过来的字段名,为null时选择所有并不进行字段重命名</param> /// <param name="strWhere">查询的限制条件</param> /// <param name="strOrderBy">排序</param> /// <returns></returns> public int GetDataFromExcel(out System.Data.DataTable dt, string strExcelFileName, string strTableName, string[,] strSelectField, string strWhere, string strOrderBy) { string strSelect; dt = null; try { if (strSelectField == null) { strSelect = "* "; } else { StringBuilder sb = new StringBuilder(); for (int i = 0; i < strSelectField.Length / 2; i++) { sb.Append(strSelectField[i, 0]); sb.Append(" as "); sb.Append(strSelectField[i, 1]); sb.Append(", "); } sb.Remove(sb.Length - 2, 1); strSelect = sb.ToString(); } } catch { strError = "所传的选择字段参数错误!"; return -1; } try { if (File.Exists(strExcelFileName) == false) { strError = "文件 " + strExcelFileName + "不存在！"; return -2; } string strCommand = "SELECT " + strSelect + "FROM [" + strTableName + "$] " + strWhere + " " + strOrderBy; new exportExcelDAL().exeCommand(strCommand,strExcelFileName ); } catch (Exception ex) { strError = "导出Excel出错:" + ex.Message; return -1; } return 0; } #endregion; }}