一、DataTable和xls文件的相互转换;
(1)xls转DataTable
服务端不必启动Excel.exe进程
/// <summary> /// xls文件的某个sheet转成表 /// 备注：此方法不会启动服务端的Excel进程 /// </summary> /// <param name="strExcelFileName">xls路径</param> /// <param name="strSheetName">xls中的某张sheetName</param> /// <returns></returns> public static System.Data.DataTable XlsToDataTable(string strExcelFileName, string strSheetName) { //源的定义 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=NO;IMEX=1';"; //Sql语句 //string strExcel = string.Format("select * from [{0}$]", strSheetName); 这是一种方法 string strExcel = "select * from [sheet1$]"; //定义存放的数据表 DataSet ds = new DataSet(); //连接数据源 OleDbConnection conn = new OleDbConnection(strConn); conn.Open(); //适配到数据源 OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, strConn); adapter.Fill(ds, strSheetName); conn.Close(); return ds.Tables[strSheetName]; }
(2)DataTable转xls
使用服务端的Excel.exe完成工作
/// <summary> /// dataTable存成excel表格 /// 备注：如果strFileName处已经存在文件,则报错 /// </summary> /// <param name="dtSource">dataTable</param> /// <param name="strFileName">在服务端完整路径</param> public static void DataTableToExcel(System.Data.DataTable dtSource, string strFileName) { int rowNum = dtSource.Rows.Count; int columnNum = dtSource.Columns.Count; int rowIndex = 1; int columnIndex = 0; if (dtSource == null || string.IsNullOrEmpty(strFileName)) { return; } if (rowNum > 0) { Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass(); xlApp.DefaultFilePath = ""; xlApp.DisplayAlerts = true; xlApp.SheetsInNewWorkbook = 1; Microsoft.Office.Interop.Excel.Workbook xlBook = xlApp.Workbooks.Add(true); //将DataTable的列名导入Excel表第一行 foreach (DataColumn dc in dtSource.Columns) { columnIndex++; xlApp.Cells[rowIndex, columnIndex] = dc.ColumnName; } //将DataTable中的数据导入Excel中 xlApp.Cells.NumberFormat = "@";//设置所有cells格式为字符串。设置类型要在个Cell赋值之前 for (int i = 0; i < rowNum; i++) { rowIndex++; columnIndex = 0; for (int j = 0; j < columnNum; j++) { columnIndex++; xlApp.Cells[rowIndex, columnIndex] = dtSource.Rows[i][j].ToString(); } } xlBook.SaveCopyAs(strFileName); //结束Excel进程的代码 xlBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);//关闭book xlApp.Quit();//关闭excel Marshal.ReleaseComObject(xlBook);//释放COM组件 Marshal.ReleaseComObject(xlApp); xlBook = null;//如果有Ragne,Sheet,WorkBooks等对象,也要null xlApp = null; GC.GetTotalMemory(false);//回收调用xlApp产生的垃圾 GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true); } }
二、关闭服务端的Excel进程
如果使用NPOI等组件,那么不启动服务端Excel进程即可生成xls文件。如果服务端用Excel进程产生xls文件,那么如下用于关闭Excel.exe进程
//结束Excel进程的代码 xlBook.Close(false, System.Reflection.Missing.Value, System.Reflection.Missing.Value);//关闭book xlApp.Quit();//关闭excel Marshal.ReleaseComObject(xlBook);//释放COM组件 Marshal.ReleaseComObject(xlApp); xlBook = null;//如果有Ragne,Sheet,WorkBooks等对象,也要null xlApp = null; GC.GetTotalMemory(false);//回收调用xlApp产生的垃圾 GC.Collect(); GC.WaitForPendingFinalizers(); GC.Collect(); GC.GetTotalMemory(true);
三、用静态类写数据库配置类，数据库操作类;
配置文件中写连接字符串
<connetionString>
<add name="BalloonShopConfiguration" ProviderName="System.Data.SqlClient" ConnectionString="Server=;User Id=;Password=;DataBase=";
</connetionString>
1.数据库配置类：
变量:string providerName, string connectionString
属性：string ProviderName,string ConnectionString
方法：构造函数（从配置文件connectionString 中读providerName和connectionString,初始化 这两个变量）
2.数据库操作类:
方法： (1)public static DbCommand CreateCommand() //创建操作数据库的命令
(2)public static DataTable ExcuteCommand(DbCommand) //执行命令，返回DataTable
/// <summary> /// 通用数据库类 /// </summary> public static class GenericDataAccess { /// <summary> /// 获取datatable /// </summary> /// <param name="dbCommand">必须已经包含的Conn信息，即是用Conn.CreateCommand()</param> /// <returns></returns> public static DataTable ExecutSelectCommnd(DbCommand dbCommand) { DbConnection conn = dbCommand.Connection; conn.Open(); DbDataReader dr = dbCommand.ExecuteReader(); DataTable dt = new DataTable(); dt.Load(dr); dr.Close();//关闭dr. dataReader是执行效率最高的类。但是，在一个dataReader关闭之前，不能执行其它数据库操作。 conn.Close();//关闭连接 return dt; } /// <summary> /// 创建命令 /// CommandType默认是 StoredProcedure /// </summary> /// <returns></returns> public static DbCommand CreateCommand() { string providerName = BalloonShopConfiguration.DbProviderName;//获取providerName DbProviderFactory fac = DbProviderFactories.GetFactory(providerName); DbConnection conn = fac.CreateConnection(); conn.ConnectionString = BalloonShopConfiguration.DbConnectionString; DbCommand command = conn.CreateCommand();//DbCommand 是conn下属Command command.CommandType = CommandType.StoredProcedure; return command; } }