Excel导入导出

using System;using System.Data;using System.Configuration;using System.Web;using System.Web.Security;using System.Web.UI;using System.Web.UI.WebControls;using System.Web.UI.WebControls.WebParts; using System.Web.UI.HtmlControls;using System.IO;using System.Data.OleDb;using System.Data.SqlClient;using System.Drawing.Printing;using System.Drawing;///<summary>/// ExportExcel 的摘要说明///</summary>public class ExportExcel{private int _ReturnStatus;private string _ReturnMessage;///<summary>/// Excel启动之前的时间///</summary>private DateTime beforeTime;///<summary>/// Excel启动之后的时间///</summary>private DateTime afterTime;///<summary>///执行返回状态///</summary>public int ReturnStatus{get { return _ReturnStatus; }}///<summary>///执行返回信息///</summary>public string ReturnMessage{get { return _ReturnMessage; }}public ExportExcel(){ }//读取数据public static SqlDataReader getsdr(string sql){SqlConnection con = newSqlConnection(OfficeAutomationSystem.SystemHelp.DBHelp.connectionString) ;con.Open();SqlCommand cmd = new SqlCommand(sql, con);SqlDataReader sdr =cmd.ExecuteReader(CommandBehavior.CloseConnection);return sdr;}///<summary>///从Excel中导入到SQL中///</summary>///<param name="filename">上传表格中sheet的名称</param>///<param name="Save">代表保存在服务器上的路径</param>///<param name="TableName">代表要导入表的名称</param>///<param name="key">代表要导入的表中主键的个数</param>///<param name="h">从Excel表中的第几行开始导入</param>///<returns></returns>public string ImportToSql(string filename, string SavePath, string TableName, int key, int h){string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Save + ";Extended Properties=Excel 8.0";//获取excel文件OleDbConnection cnnxls = new OleDbConnection(mystring);//用数据库连接excelcnnxls.Open();SqlConnection strcon = newSqlConnection(OfficeAutomationSystem.SystemHelp.DBHelp.connectionString) ;strcon.Open();beforeTime = DateTime.Now;Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();afterTime = DateTime.Now;object oMissing = System.Reflection.Missing.Value;string sqlinsert = "insert into " + TableName + " values('";string sqlinsert1 = "";string updatesql = "";oExcel.Workbooks.Add(oMissing);Microsoft.Office.Interop.Excel.Workbook oBook =oExcel.Workbooks[1];Microsoft.Office.Interop.Excel.Worksheet oSheet =(Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];string sheetname = ;try{OleDbDataAdapter myDa = new OleDbDataAdapter("select * from ["+filename+"$]", cnnxls);//创建匹配器DataSet myDs = new DataSet();myDa.Fill(myDs);//将Excel表中查到是数据添加到DataSet中if (myDs.Tables[0].Rows.Count > 0){SqlCommand myCmd = null;//从DataSet中的第h行开始遍历for (int i = h; i < myDs.Tables[0].Rows.Count; i++){int rows = key;//导入表中的主键的个数int j;sqlinsert1 = "";bool check = true;string sql = "select * from " + TableName;//查询导入表中的数据string sql1 = "";//记录表中主键字段名称和所有字段名称string sdrsql = "";//获取表中的主键名称for (j = 0; j < key; j++){string keywords =myDs.Tables[0].Rows[i].ItemArray[j].ToString();string keyName = "";SqlDataReader dr = getsdr(sql);keyName = dr.GetName(j);sql1 += keyName + "=" + keywords + " and ";dr.Close();dr.Dispose();}sql1 = sql1.Substring(0, sql1.Length - 4);sdrsql = sql + " where " + sql1;SqlDataReader sdr = getsdr(sdrsql);if (sdr.Read()){updatesql = "update " + TableName + " set ";for (j = key; j < myDs.Tables[0].Columns.Count; j++){updatesql += sdr.GetName(j) + "='" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "',";}updatesql = updatesql.Substring(0,updatesql.Length - 2) + "' where " + sql1;myCmd = new SqlCommand(updatesql, strcon);myCmd.ExecuteNonQuery();//写入SQL数据库}else{sqlinsert = sqlinsert.Substring(0,sqlinsert.IndexOf("'"));for(j = key; j < myDs.Tables[0].Columns.Count - 1; j++){sqlinsert1 +=myDs.Tables[0].Rows[i].ItemArray[j].ToString();sqlinsert += "'" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "',";}sqlinsert += "'" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "' )";myCmd = new SqlCommand(sqlinsert, strcon);myCmd.ExecuteNonQuery();//写入SQL数据库}sdr.Close();sdr.Dispose();if (sqlinsert1 == ""){continue;//结束本次循环}if (rows > 0){for (; rows > 0; rows--){if (myDs.Tables[0].Rows[i].ItemArray[rows - 1].ToString() == ""){check = false;break;}}}if (check == false){check = true;continue;}}//释放Excel对象,但在 Web程序中只有转向另一个页面时候进程才结束//可以考虑使用KillExcelProcess()杀掉进程//ReleaseComObject 方法递减运行库可调用包装的引用计数. System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);oBook.Close(oMissing, oMissing, oMissing);oExcel.Workbooks.Close();oSheet = null;oBook = null;oMissing = null;oExcel.Quit();oExcel = null;}}catch{KillExcelProcess();}finally{strcon.Close();cnnxls.Close();strcon.Dispose();KillExcelProcess();}return null;}///<summary>///从Excel中导入到SQL中///</summary>///<param name="filename">代表上传文件的路径和文件名</param>///<param name="Save">代表保存在服务器上的路径</param>///<param name="TableName">代表要导入的表的名称</param>///<param name="key">代表要导入的表中主键的个数</param>///<param name="h">代表Excel表中的某一行的第一个单元格</param>///<returns></returns>public string ImportToSql(string filename, string Save, string TableName, int key, int h, string sqlinsert, string query){string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Save + ";Extended Properties=Excel 8.0";//获取excel文件OleDbConnection cnnxls = new OleDbConnection(mystring);//用数据库连接excelcnnxls.Open();SqlConnection strcon = newSqlConnection(OfficeAutomationSystem.SystemHelp.DBHelp.connectionString);strcon.Open();beforeTime = DateTime.Now;Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();afterTime = DateTime.Now;object oMissing = System.Reflection.Missing.Value;//string sqlinsert = "insert into " + TableName + " (FullfillYear,Enterprise,FoodAllowance,LiveAllowance,ManageFee,AreaFund, CheckTime) values('";string sqlinsert1 = "";string updatesql = "";oExcel.Workbooks.Add(oMissing);Microsoft.Office.Interop.Excel.Workbook oBook =oExcel.Workbooks[1];Microsoft.Office.Interop.Excel.Worksheet oSheet =(Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];string sheetname = ;try{OleDbDataAdapter myDa = new OleDbDataAdapter(query, cnnxls);//创建匹配器DataSet myDs = new DataSet();myDa.Fill(myDs);if (myDs.Tables[0].Rows.Count > 0){SqlCommand myCmd = null;for (int i = h; i < myDs.Tables[0].Rows.Count; i++){int rows = key;int j;sqlinsert1 = "";bool check = true;string sql = "select * from " + TableName;string sql1 = "";string sdrsql = "";for (j = 0; j < key; j++){string keywords =myDs.Tables[0].Rows[i].ItemArray[j].ToString();if (keywords == "0"){continue;}else{string keyName = "";SqlDataReader dr = getsdr(sql);keyName = dr.GetName(j);sql1 += keyName + "='" + keywords + "' and "; dr.Close();dr.Dispose();}}sql1 = sql1.Substring(0, sql1.Length - 4);sdrsql = sql + " where " + sql1;SqlDataReader sdr = getsdr(sdrsql);if (sdr.Read()){updatesql = "update " + TableName + " set ";for(j = 1; j < myDs.Tables[0].Columns.Count; j++) {updatesql += sdr.GetName(j) + "='" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "',";}updatesql = updatesql.Substring(0,updatesql.Length - 2) + "' where " + sql1;myCmd = new SqlCommand(updatesql, strcon);myCmd.ExecuteNonQuery();//写入SQL数据库}else{sqlinsert = sqlinsert.Substring(0,sqlinsert.IndexOf("'"));for (j = 1; j < myDs.Tables[0].Columns.Count - 1; j++){sqlinsert1 +=myDs.Tables[0].Rows[i].ItemArray[j].ToString();sqlinsert += "'" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "',";}sqlinsert += "'" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "' )";myCmd = new SqlCommand(sqlinsert, strcon);myCmd.ExecuteNonQuery();//写入SQL数据库}sdr.Close();sdr.Dispose();if (sqlinsert1 == ""){continue;}if (rows > 0){for (; rows > 0; rows--){if (myDs.Tables[0].Rows[i].ItemArray[rows - 1].ToString() == ""){check = false;break;}}}if (check == false){check = true;continue;}}//释放Excel对象,但在 Web程序中只有转向另一个页面时候进程才结束//可以考虑使用KillExcelProcess()杀掉进程//ReleaseComObject 方法递减运行库可调用包装的引用计数.System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel);oBook.Close(oMissing, oMissing, oMissing);oExcel.Workbooks.Close();oSheet = null;oBook = null;oMissing = null;oExcel.Quit();oExcel = null;}if (Save != ""){FileInfo fi = new FileInfo(Save);if (fi.Exists){fi.Delete();//删除导入文件}}}catch (Exception ex){KillExcelProcess();}finally{strcon.Close();cnnxls.Close();strcon.Dispose();KillExcelProcess();}return null;}///<summary>///从Excel中导入到SQL中///</summary>///<param name="filename">代表上传文件的路径和文件名</param>///<param name="Save">代表保存在服务器上的路径</param>///<param name="sqlinsert">sql语句</param>///<param name="h">h代表从Excel的第几行开始导入数据 h=3代表从Excel 的第五行开始导入数据</param>///<returns></returns>public string inporto(string filename, string Save, string sqlinsert, int h){ /*************************************************** filename代表上传文件的路径和文件名* Save代表保存在服务器上的路径* sqlinsert代表插入的sql语句* h代表Excel表中的某一行的第一个单元格* key代表要导入的表中主键的个数* ************************************************/ string mystring = "Provider = Microsoft.Jet.OLEDB.4.0 ; Data Source = " + Save + ";Extended Properties=Excel 8.0";//获取excel文件OleDbConnection cnnxls = new OleDbConnection(mystring);//用数据库连接excelcnnxls.Open();SqlConnection strcon = newSqlConnection(OfficeAutomationSystem.SystemHelp.DBHelp.connectionString) ;strcon.Open();beforeTime = DateTime.Now;Microsoft.Office.Interop.Excel.ApplicationClass oExcel = new Microsoft.Office.Interop.Excel.ApplicationClass();afterTime = DateTime.Now;object oMissing = System.Reflection.Missing.Value;string sqlinsert1 = "";oExcel.Workbooks.Add(oMissing);Microsoft.Office.Interop.Excel.Workbook oBook =oExcel.Workbooks[1];Microsoft.Office.Interop.Excel.Worksheet oSheet =(Microsoft.Office.Interop.Excel.Worksheet)oBook.Sheets[1];string sheetname = ;try{OleDbDataAdapter myDa = new OleDbDataAdapter("select * from [Sheet1$]", cnnxls);//创建匹配器DataSet myDs = new DataSet();myDa.Fill(myDs);if (myDs.Tables[0].Rows.Count > 0){SqlCommand myCmd = null;for (int i = h; i < myDs.Tables[0].Rows.Count; i++){int j;sqlinsert1 = "";bool check = true;sqlinsert = sqlinsert.Substring(0,sqlinsert.IndexOf("'"));for (j = 0; j < myDs.Tables[0].Columns.Count - 1; j++) {sqlinsert1 +=myDs.Tables[0].Rows[i].ItemArray[j].ToString();sqlinsert += "'" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "',";}sqlinsert += "'" +myDs.Tables[0].Rows[i].ItemArray[j].ToString() + "' )";myCmd = new SqlCommand(sqlinsert, strcon);myCmd.ExecuteNonQuery();//写入SQL数据库if (sqlinsert1 == ""){continue;}if (check == false){check = true;continue;}}}//释放Excel对象,但在 Web程序中只有转向另一个页面时候进程才结束//可以考虑使用KillExcelProcess()杀掉进程//ReleaseComObject 方法递减运行库可调用包装的引用计数.System.Runtime.InteropServices.Marshal.ReleaseComObject(oBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(oSheet);System.Runtime.InteropServices.Marshal.ReleaseComObject(oExcel); oBook.Close(oMissing, oMissing, oMissing);oExcel.Workbooks.Close();oSheet = null;oBook = null;oMissing = null;oExcel.Quit();oExcel = null;if (Save != ""){FileInfo fi = new FileInfo(Save);if (fi.Exists){fi.Delete();//删除导入文件}}}catch (Exception ex){KillExcelProcess();throw ex;}finally{strcon.Close();cnnxls.Close();strcon.Dispose();KillExcelProcess();}return null;}///<summary>///将Excel导入到DataSet///</summary>///<param name="fileName">文件路径和文件名</param>///<param name="TableName">要导入的数据的表名</param>///<param name="key">表中主键个数</param>///<param name="h">从Excel的第几行开始导入数据</param>///<returns></returns>public string ExcelToSQL(string fileName, string TableName, int key, int h){//判断是否安装EXCELbeforeTime = DateTime.Now;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();afterTime = DateTime.Now;if (xlApp == null){_ReturnStatus = -1;_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";return null;}//判断文件是否被其他进程使用Microsoft.Office.Interop.Excel.Workbook workbook;try{workbook = xlApp.Workbooks.Open(fileName, 0, false, 5, "", "", false, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows, "", true, false, 0, true, 1, 0);}catch{_ReturnStatus = -1;_ReturnMessage = "Excel文件处于打开状态，请保存关闭";return null;}//获得所有Sheet名称int n = workbook.Worksheets.Count;string[] SheetSet = new string[n];System.Collections.ArrayList al = newSystem.Collections.ArrayList();for (int i = 1; i <= n; i++){SheetSet[i - 1] =((Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[i]).Name; }//把EXCEL导入到DataSetstring sqlinsert1 = "";string sqlinsert = "";string updatesql = "";DataSet ds = new DataSet();using (SqlConnection conn = newSqlConnection(OfficeAutomationSystem.SystemHelp.DBHelp.connectionString) ){conn.Open();SqlDataAdapter da;for (int i = 1; i <= n; i++){string sqlSheet = "select * from ["+ SheetSet[i - 1] + "$] ";da = new SqlDataAdapter(sqlSheet, conn);da.Fill(ds, SheetSet[i - 1]);if (ds.Tables[0].Rows.Count > 0){SqlCommand myCmd = null;for (int k = h; k < ds.Tables[0].Rows.Count; k++){int rows = key, j;sqlinsert1 = "";string sql = "select * from " + TableName;string sql1 = "";string sdrsql = "";for (j = 0; j < key; j++){string keywords =ds.Tables[0].Rows[k].ItemArray[j].ToString();string keyName = "";SqlDataReader dr = OfficeAutomationSystem.SystemHelp.DBHelp.ExecuteReader(sql);keyName = dr.GetName(j);sql1 += keyName + "='" + keywords + "'and "; dr.Close();dr.Dispose();}sql1 = sql1.Substring(0, sql1.Length - 4);sdrsql = sql + " where " + sql1;SqlDataReader sdr =OfficeAutomationSystem.SystemHelp.DBHelp.ExecuteReader(sdrsql);if (sdr.Read()){updatesql = "update " + TableName + " set ";for (j = 0; j < ds.Tables[0].Columns.Count; j++){updatesql += sdr.GetName(j) + "='" + ds.Tables[0].Rows[k].ItemArray[j].ToString() + "',";}updatesql = updatesql.Substring(0, updatesql.Length - 2) + "' where " + sql1;myCmd = new SqlCommand(updatesql, conn);myCmd.ExecuteNonQuery();//写入SQL数据库}else{sqlinsert = "insert into " + TableName + " values(";for(j = 0; j < ds.Tables[0].Columns.Count - 1; j++){sqlinsert1 +=ds.Tables[0].Rows[k].ItemArray[j].ToString();sqlinsert += "'" +ds.Tables[0].Rows[k].ItemArray[j].ToString() + "',";}sqlinsert += "'" +ds.Tables[0].Rows[k].ItemArray[j].ToString() + "' )";myCmd = new SqlCommand(sqlinsert, conn);myCmd.ExecuteNonQuery();//写入SQL数据库 }sdr.Close();sdr.Dispose();//if (sqlinsert1 == "")//{// continue;//}//if (rows > 0)//{// for (; rows > 0; rows--)// {// if(ds.Tables[0].Rows[k+1].ItemArray[rows - 1].ToString() == "")// {// check = false;// break;// }// }//}//if (check == false)//{// check = true;// continue;//}if (ds.Tables[0].Rows[k +1].ItemArray[0].ToString() == ""){break;}}}da.Dispose();}conn.Close();conn.Dispose();}//释放Excel相关对象workbook.Close(null, null, null);xlApp.Quit();if (workbook != null)System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);workbook = null;}if (xlApp != null){System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp = null;xlApp.Quit();}KillExcelProcess();return null;}///<summary>1///把DataTable导出到EXCEL///</summary>///<param name="reportName">Excel表头名称</param>///<param name="dt">数据源表</param>///<param name="saveFileName">Excel全路径文件名</param>///<param name="ber">代表是打印还是导出</param>///<param name="Size">代表打印页面的大小</param>///<returns>导出是否成功</returns>public bool ExportExcelTable(string reportName, DataTable dt, string saveFileName, int ber, string Size){if (dt == null){_ReturnStatus = -1;_ReturnMessage = "数据集为空！";return false;}bool fileSaved = false;beforeTime = DateTime.Now;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();afterTime = DateTime.Now;if (xlApp == null)_ReturnStatus = -1;_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";return false;}Microsoft.Office.Interop.Excel.Workbooks workbooks =xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook =workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorkshe et);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1worksheet.Columns.EntireColumn.AutoFit();if (Size == "A3"){worksheet.PageSetup.PaperSize =Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA3;}if (Size == "A4"){worksheet.PageSetup.PaperSize =Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;}#region设置页面显示样式worksheet.PageSetup.LeftMargin = 2 / 0035;worksheet.PageSetup.RightMargin = 2 / 0035;worksheet.PageSetup.CenterHorizontally = true;//worksheet.PageSetup.CenterVertically = true;#endregion = reportName;worksheet.Cells.Font.Size = 10;Microsoft.Office.Interop.Excel.Range range;long totalCount = dt.Rows.Count;long rowRead = 0;float percent = 0;worksheet.Cells[1, 1] = reportName;range =(Microsoft.Office.Interop.Excel.Range)worksheet.get_Range(worksheet.Cell s[1, 1], worksheet.Cells[1, dt.Columns.Count]);range.Merge(0); //单元格合并动作range.HorizontalAlignment =Microsoft.Office.Interop.Excel.XlHAlign.xlHAlignCenter;range.VerticalAlignment =Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Size = 28;((Microsoft.Office.Interop.Excel.Range)worksheet.Cells[1, 1]).Font.Bold = true;//写入字段for (int i = 0; i < dt.Columns.Count; i++){worksheet.Cells[2, i + 1] = dt.Columns[i].ColumnName;range =(Microsoft.Office.Interop.Excel.Range)worksheet.Cells[2, i + 1];//range.Interior.ColorIndex = 15;range.WrapText = true;range.Font.Bold = true;range.Columns.ColumnWidth = 60;}//写入数值for (int r = 0; r < dt.Rows.Count; r++){for (int i = 0; i < dt.Columns.Count; i++){if (dt.Columns[i].ColumnName == "序号" ||dt.Columns[i].ColumnName == "编号"){worksheet.Cells[r + 3, i + 1] = "'" +dt.Rows[r][i].ToString();}else if (dt.Columns[i].ColumnName == "身份证号"){worksheet.Cells[r + 3, i + 1] = "'" +dt.Rows[r][i].ToString();}else{int id = int.Parse(dt.Rows[r][0].ToString());if (id == 0){worksheet.Cells[r + 3, 1] = "合计";}worksheet.Cells[r + 3, i + 1] =dt.Rows[r][i].ToString();}}rowRead++;percent = ((float)(100 * rowRead)) / totalCount;}range = worksheet.get_Range(worksheet.Cells[2, 1],worksheet.Cells[dt.Rows.Count + 2, dt.Columns.Count]);range.BorderAround(Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuo us, Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin,Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic, null);if (dt.Rows.Count > 0){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHori zontal].ColorIndex =Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHori zontal].LineStyle =Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideHori zontal].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}if (dt.Columns.Count > 1){range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVert ical].ColorIndex =Microsoft.Office.Interop.Excel.XlColorIndex.xlColorIndexAutomatic;range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVert ical].LineStyle =Microsoft.Office.Interop.Excel.XlLineStyle.xlContinuous;range.Borders[Microsoft.Office.Interop.Excel.XlBordersIndex.xlInsideVert ical].Weight = Microsoft.Office.Interop.Excel.XlBorderWeight.xlThin;}range.HorizontalAlignment =Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;range.VerticalAlignment =Microsoft.Office.Interop.Excel.XlVAlign.xlVAlignCenter;range.WrapText = true;range.EntireColumn.AutoFit();range.EntireRow.AutoFit();//保存文件if (saveFileName != ""){try{workbook.Saved = true;workbook.SaveCopyAs(saveFileName);fileSaved = true;}catch (Exception ex){fileSaved = false;_ReturnStatus = -1;_ReturnMessage = "导出文件时出错,文件可能正被打开！\n" + ex.Message;}}else{fileSaved = false;}//ber等于1就打印,否则导出if (ber == 1){xlApp.ActiveWindow.Zoom = 100;xlApp.Visible = true;worksheet.PrintPreview(0);workbook.Saved = true;////客户端下载string SaveFileName =HttpUtility.UrlEncode(System.Text.UTF8Encoding.UTF8.GetBytes("点击打开即可打印文档.xls"));FileInfo DownLoadFile = new FileInfo(saveFileName);System.Web.HttpContext.Current.Response.Clear();System.Web.HttpContext.Current.Response.ClearHeaders();System.Web.HttpContext.Current.Response.Buffer = false;System.Web.HttpContext.Current.Response.AppendHeader("Content-Dispositio n", "attachment; filename=" + SaveFileName);System.Web.HttpContext.Current.Response.ContentType = "applicaton/excel";System.Web.HttpContext.Current.Response.WriteFile(DownLoadFile.FullName) ;new ExportExcel().DeleteFileName(saveFileName);System.Web.HttpContext.Current.Response.Flush();System.Web.HttpContext.Current.Response.End();}#region释放Excel对应的对象if (range != null){System.Runtime.InteropServices.Marshal.ReleaseComObject(range);range = null;}if (worksheet != null){System.Runtime.InteropServices.Marshal.ReleaseComObject(worksheet);worksheet = null;}if (workbook != null){System.Runtime.InteropServices.Marshal.ReleaseComObject(workbook);workbook = null;}if (workbooks != null){System.Runtime.InteropServices.Marshal.ReleaseComObject(workbooks);workbooks = null;}xlApp.Application.Workbooks.Close();xlApp.Quit();if (xlApp != null){System.Runtime.InteropServices.Marshal.ReleaseComObject(xlApp);xlApp = null;}KillExcelProcess();#endregionreturn fileSaved;}///<summary>///把DataTable导出到EXCEL(只限于林权流转导出)///</summary>///<param name="reportName">Excel表头名称</param>///<param name="dt">数据源表</param>///<param name="saveFileName">Excel全路径文件名</param>///<param name="ber">代表是打印还是导出</param>///<param name="Size">代表打印页面的大小</param>///<returns>导出是否成功</returns>public bool ExportExcelEvaluation(string reportName, DataTable dt, string saveFileName, int ber, string Size){if (dt == null){_ReturnStatus = -1;_ReturnMessage = "数据集为空！";return false;}bool fileSaved = false;beforeTime = DateTime.Now;Microsoft.Office.Interop.Excel.Application xlApp = new Microsoft.Office.Interop.Excel.ApplicationClass();afterTime = DateTime.Now;if (xlApp == null){_ReturnStatus = -1;_ReturnMessage = "无法创建Excel对象，可能您的计算机未安装Excel";return false;}Microsoft.Office.Interop.Excel.Workbooks workbooks =xlApp.Workbooks;Microsoft.Office.Interop.Excel.Workbook workbook =workbooks.Add(Microsoft.Office.Interop.Excel.XlWBATemplate.xlWBATWorkshe et);Microsoft.Office.Interop.Excel.Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)workbook.Worksheets[1];//取得sheet1worksheet.Columns.EntireColumn.AutoFit();if (Size == "A3"){worksheet.PageSetup.PaperSize =Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA3;}if (Size == "A4"){worksheet.PageSetup.PaperSize =Microsoft.Office.Interop.Excel.XlPaperSize.xlPaperA4;}。