Microsoft.Office.Interop.Excel
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; }}
[解决办法]
，後用
[解决办法]
http://blog.csdn.net/fangxinggood/archive/2006/04/08/655313.aspx
[解决办法]
答案已出。。
2代。
[解决办法]
- C# code
using Excel = Microsoft.Office.Interop.Excel;using ExcelApplication = Microsoft.Office.Interop.Excel.Application;/// 生成Excel /// </summary> /// <param name="exportPath">导出Excel的完整路径</param> /// <param name="ds">数据源</param> /// <param name="ds">名</param> /// <param name="passWord">密码保护,可以为空</param> /// <param name="isDeleteFile">是否除生成的Excel文件</param> public void GenerateExcel(string exportPath, System.Data.DataSet ds,string[] wrokSheetName,string[] passWord,bool isDeleteFile) { DateTime starttime = DateTime.Now; object miss = Type.Missing; try { ExcelApplication app = new ExcelApplication(); Excel.Workbook workbook = app.Workbooks.Add(true);//默认会创建1个wroksheet if (ds.Tables.Count > 1) {//如果有多表 workbook.Sheets.Add(miss, workbook.Sheets[1], ds.Tables.Count - 1, miss); } if(exportPath == "" || exportPath == null){ exportPath = System.Configuration.ConfigurationSettings.AppSettings["ReportExportedTempReportPath"] + DateTime.Now.ToString("yyyyMMddHHmmss") + new Random().Next(100,10000).ToString() + ".xls"; } for(int i=0;i<workbook.Sheets.Count;i++){ Excel.Worksheet worksheet = workbook.Worksheets[ i+1] as Excel.Worksheet;//选择第一个wroksheet Excel类库中数组下标从1开始 worksheet.Name = wrokSheetName[i];//名 int totalCellsCount = ds.Tables[i].Columns.Count; Excel.Range headerCell = worksheet.get_Range(worksheet.Cells[1, 1], worksheet.Cells[1, totalCellsCount]);//要创建的列数 第一个参数 表示起始列 第二个表示截止列 int Index = 1; foreach (DataColumn column in ds.Tables[i].Columns) {//表头 默认在第一行 headerCell[1, Index] = column.ColumnName; //headerCell.Cells.HorizontalAlignment = Excel.XlVAlign.xlVAlignCenter; headerCell.Font.Bold = true; headerCell.Font.Size = 10; ((Excel.Range)headerCell.Cells[1,Index]).EntireColumn.AutoFit();//自适应宽度 ((Excel.Range)headerCell.Cells[1,Index]).EntireRow.AutoFit();//自适应高度 Index++; } int RowIndex = 2, ColunmIndex = 1;//RowIndex 表示行(索引);ColunmIndex表示列(索引) foreach (DataRow row in ds.Tables[0].Rows) { foreach (DataColumn column in ds.Tables[0].Columns) { worksheet.Cells[RowIndex, ColunmIndex] = row[column].ToString(); ((Excel.Range)worksheet.Cells[RowIndex,ColunmIndex]).EntireColumn.AutoFit(); ((Excel.Range)worksheet.Cells[RowIndex,ColunmIndex]).EntireRow.AutoFit(); ColunmIndex++; } ColunmIndex = 1; RowIndex++; } if(passWord != null){//设置密码保护 if(passWord[i] != string.Empty && passWord[i] != null){ worksheet.Protect(passWord[i], true, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); } } System.Runtime.InteropServices.Marshal.ReleaseComObject(headerCell); headerCell = null; System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet); worksheet = null; } ((Excel.Worksheet)workbook.Sheets[1]).Select(true);//中第一worksheet workbook.SaveAs(exportPath, Excel.XlFileFormat.xlExcel9795, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, miss, miss, miss, miss, miss); workbook.Close(true,miss,miss);//关闭workbook, 并保存对workbook的所有修改 System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook); workbook = null; app.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(app); app = null; GC.Collect(); GC.WaitForPendingFinalizers(); DateTime endTime = DateTime.Now; Util.KillProcess("EXCEL", starttime, endTime); new Util().DownLoadFile(exportPath,isDeleteFile); } catch (Exception ex) { throw ex; } }