关于Excel导入导出

Excel的导出导入的多种方法

    1. 导入
          a.) 使用微软的OLEDB方式

public void ExcelToDB(string path) { DataSet ds = new DataSet(); string connectionString = string.Format("Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties=/"Excel 8.0;HDR=Yes;IMEX=1/"", path); OleDbConnection connection = new OleDbConnection(connectionString); OleDbDataAdapter adapter = new OleDbDataAdapter("select * from [Sheet1$]", connection); adapter.Fill(ds);

b.) DCOM方式

private Excel.ApplicationClass ExcelFile; public void ExcelToDB() { PreExitExcel(); this.ExcelFile = new Excel.ApplicationClass(); this.ExcelFile.Visible = false; Excel.Workbook Book; System.Threading.Thread.CurrentThread.CurrentCulture = new System.Globalization.CultureInfo("en-US"); Book = (Excel.Workbook)this.ExcelFile.Workbooks._Open(path, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value); Excel.Worksheet FirstSheet = (Excel.Worksheet)Book.Sheets[1]; string Name = FirstSheet.get_Range("A1", Missing.Value).Value2.ToString().Trim(); string Sex = FirstSheet.get_Range("A2", Missing.Value).Value2.ToString().Trim(); Book.Close(false, false, false); PreExitExcel(); //省略保存数据到数据库 } /** <summary> /// 预关闭未退出的Excel进程 /// </summary> public void PreExitExcel() { System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses(); foreach (System.Diagnostics.Process thisprocess in allProcess) { string processName = thisprocess.ProcessName; if (processName.ToLower() == "excel") { try { thisprocess.Kill(); } catch (Exception e) { } } } }

  2. 导出
          a.)用gridview导出到Excel

/** <summary> /// 导出到Excel /// </summary> /// <param name="FileName"></param> public bool ToExcel(string FileName) { try { BindViewer(); GvFy.AllowPaging = false; Response.Clear(); Response.Buffer = true; HttpContext.Current.Response.Charset = "GB2312"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8"); HttpContext.Current.Response.ContentType = "application/ms-excel"; Response.Cache.SetCacheability(HttpCacheability.NoCache); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=/"" + System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8) + ".xls/""); //HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename="+ FileName + ".xls"); GvFy.Page.EnableViewState = false; System.IO.StringWriter tw = new System.IO.StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); Table2.RenderControl(hw); Response.Output.Write(tw.ToString()); Response.Flush(); Response.End(); } catch(Exception ex) { ShowMessageBox("报表没数据，不能够导出！！！"); return false; } SsfService.Instance.InsertLog(Dafang.Ssf.Entities.LogType.四分线损汇总, "线损分压统计表: 导出原始表" + ddlDataCycle.SelectedValue); return true; }

  并且需要override一下VerifyRenderingInServerForm方法(这一点非常重要，否则在点击按钮后会报错)，代码如下

public override void VerifyRenderingInServerForm(Control control) { // Confirms that an HtmlForm control is rendered for }

最好在页面文件加上EnableEventValidation = "false"

<%@ Page Language="C#" EnableEventValidation="false" AutoEventWireup="true" CodeFile="Default.aspx.cs" Inherits="_Default" %>

 

我们有时候需要用这中方法导出的Excel文件在导入到数据库中，就会有问题了，将会提示"外部表不是预期的格式"。出现这种错误那就是我们的Excel文件不是标准的Excel格式。我们先来看看怎么判断一个Excel文件是不是标准的格式。方法是：用记事本打开你的excel文件，看看显示是否为乱码。若是html代码，则表示你的excel文件格式不是标准的excel格式，才会提示“外部表不是预期的格式”的错误；若是乱码，我这边测试是不会提示这个错误的，可以成功导入。那么怎么才能导出标准的Excel格式的文件呢？

          b.)DCOM

这种方法就解决了上面出现的问题。唯一要注意的是权限的设置。服务器端会存在c#是否有权限使用excel com组件的问题。我先贴出一个操作Excel 的类库

/// <summary> /// Excel处理类 /// </summary> public class ExcelHelper { public ExcelHelper() { // // TODO: Add constructor logic here // } //private Microsoft.Office.Interop.Excel. Excel; private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; public string GetAix(int x, int y) { char[] AChars = AList.ToCharArray(); if (x >= 26) { return ""; } string s = ""; s = s + AChars[x - 1].ToString(); s = s + y.ToString(); return s; } /** <summary> /// 为指定单元格赋值 /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <param name="align">居中，居左，居右对齐</param> /// <param name="text">值</param> public void setValue(int x, int y, string align, string text) { Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); if (align.ToUpper() == "CENTER") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; } if (align.ToUpper() == "LEFT") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignLeft; } if (align.ToUpper() == "RIGHT") { range.HorizontalAlignment = Excel.XlHAlign.xlHAlignRight; } //设置单元格为最适应宽度 sheet.get_Range(this.GetAix(x, y), miss).Select(); sheet.get_Range(this.GetAix(x, y), miss).Columns.AutoFit(); } /** <summary> /// 为指定单元格赋值 /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <param name="text">值</param> public void setValue(int x, int y, string text) { Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); } /** <summary> /// 为指定单元格赋值 /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <param name="text"></param> /// <param name="font"></param> /// <param name="color"></param> public void setValue(int x, int y, string text, System.Drawing.Font font, System.Drawing.Color color) { this.setValue(x, y, text); Excel.Range range = sheet.get_Range(this.GetAix(x, y), miss); range.Font.Size = font.Size; range.Font.Bold = font.Bold; range.Font.Color = color; range.Font.Name = font.Name; range.Font.Italic = font.Italic; range.Font.Underline = font.Underline; } public void insertRow(int y) { Excel.Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y)); range.Copy(miss); range.Insert(Excel.XlDirection.xlDown, miss); range.get_Range(GetAix(1, y), GetAix(25, y)); range.Select(); sheet.Paste(miss, miss); } public void past() { string s = "a,b,c,d,e,f,g"; sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s); } public void setBorder(int x1, int y1, int x2, int y2, int Width) { Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); range.Borders.Weight = Width; } /** <summary> /// 合并单元格 /// </summary> /// <param name="x1"></param> /// <param name="y1"></param> /// <param name="x2"></param> /// <param name="y2"></param> public void mergeCell(int x1, int y1, int x2, int y2) { // Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).MergeCells = true; //range.Merge(true); // range.MergeCells } /** <summary> /// 获取单元格区域 /// </summary> /// <param name="x1"></param> /// <param name="y1"></param> /// <param name="x2"></param> /// <param name="y2"></param> /// <returns></returns> public Excel.Range getRange(int x1, int y1, int x2, int y2) { Excel.Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); return range; } /** <summary> /// 绘制边框 /// </summary> /// <param name="x1"></param> /// <param name="y1"></param> /// <param name="x2"></param> /// <param name="y2"></param> public void DrawBorder(int x1, int y1, int x2, int y2) { sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders.LineStyle = 1; sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeLeft].Weight = Excel.XlBorderWeight.xlMedium;//设置左边线加粗 sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeTop].Weight = Excel.XlBorderWeight.xlMedium;//设置上边线加粗 sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeRight].Weight = Excel.XlBorderWeight.xlMedium;//设置右边线加粗 sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).Borders[Excel.XlBordersIndex.xlEdgeBottom].Weight = Excel.XlBorderWeight.xlMedium;//设置下边线加粗 //sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)).BorderAround(Excel.XlLineStyle.xlContinuous, Excel.XlBorderWeight.xlMedium, Excel.XlColorIndex.xlColorIndexAutomatic, null); } public Missing miss = Missing.Value; //忽略的参数OLENULL public static Missing MissValue = Missing.Value; private Excel.Application m_objExcel;//Excel应用程序实例 private Excel.Workbooks m_objBooks;//工作表集合 private Excel.Workbook m_objBook;//当前操作的工作表 private Excel.Worksheet sheet;//当前操作的表格 public Excel.Worksheet CurrentSheet { get { return sheet; } set { this.sheet = value; } } public Excel.Workbooks CurrentWorkBooks { get { return this.m_objBooks; } set { this.m_objBooks = value; } } public Excel.Workbook CurrentWorkBook { get { return this.m_objBook; } set { this.m_objBook = value; } } /** <summary> /// 打开excel文件 /// </summary> /// <param name="filename"></param> public void OpenExcelFile(string filename) { m_objExcel = new Excel.Application(); UserControl(false); m_objExcel.Workbooks.Open( filename, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = m_objExcel.ActiveWorkbook; sheet = (Excel.Worksheet)m_objBook.ActiveSheet; } public void UserControl(bool usercontrol) { if (m_objExcel == null) { return; } m_objExcel.UserControl = usercontrol; m_objExcel.DisplayAlerts = usercontrol; m_objExcel.Visible = usercontrol; } /** <summary> /// 创建excle模板 /// </summary> public void CreateExceFile() { m_objExcel = new Excel.Application(); UserControl(false); m_objBooks = (Excel.Workbooks)m_objExcel.Workbooks; m_objBook = (Excel.Workbook)(m_objBooks.Add(miss)); sheet = (Excel.Worksheet)m_objBook.ActiveSheet; } /** <summary> /// 保存excel /// </summary> /// <param name="FileName">文件名</param> public void SaveAs(string FileName) { m_objBook.SaveAs(FileName, miss, miss, miss, miss, miss, Excel.XlSaveAsAccessMode.xlNoChange, Excel.XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss); m_objBook.Close(false, miss, miss); } /** <summary> /// 回收资源 /// </summary> public void ReleaseExcel() { if (m_objExcel != null) { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject(sheet); } GC.Collect(); } /** <summary> /// 预关闭未退出的Excel进程 /// </summary> public void PreExitExcel() { System.Diagnostics.Process[] allProcess = System.Diagnostics.Process.GetProcesses(); foreach (System.Diagnostics.Process thisprocess in allProcess) { string processName = thisprocess.ProcessName; if (processName.ToLower() == "excel") { try { thisprocess.Kill(); } catch (Exception e) { } } } } }

对类库的调用

private void toExcel() { ExcelHelper excle = new ExcelHelper(); //处理Excel进程 excle.PreExitExcel(); //创建Excel文件 excle.CreateExceFile(); //合并A1到N1区域的单元格 excle.mergeCell(1, 1, 14, 1); //为单元格赋值 excle.setValue(1, 1, "center", "年 月 （××单位）线损分压统计表"); excle.mergeCell(1, 2, 2, 3); excle.setValue(1, 2,"center", "电压等级"); excle.mergeCell(3, 2, 3, 3); excle.setValue(3, 2, "center", "输入电量（万kWh）"); excle.mergeCell(4, 2, 4, 3); excle.setValue(4, 2, "center", "输出电量（万kWh）"); excle.mergeCell(5, 2, 8, 2); excle.setValue(5, 2, "center", "损失电量（万kWh）"); excle.setValue(5, 3, "center", "合计"); excle.setValue(6, 3, "center", "变损"); excle.setValue(7, 3, "center", "线损"); excle.setValue(8, 3, "center", "变压器损耗所占比例（%）"); excle.mergeCell(9, 2, 9, 3); excle.setValue(9, 2, "center", "各电压等级损耗所占比例（%）"); excle.mergeCell(10, 2, 10, 3); excle.setValue(10, 2, "center", "线损率（%）"); excle.mergeCell(11, 2, 11, 3); excle.setValue(11, 2, "center", "同比"); excle.mergeCell(12, 2, 12, 3); excle.setValue(12, 2, "center", "无损电量（万kWh）"); excle.mergeCell(13, 2, 13, 3); excle.setValue(13, 2, "center", "有损线损率（%）"); excle.mergeCell(14, 2, 14, 3); excle.setValue(14, 2, "center", "同比"); List<Fydydj> fydydjs = SsfService.Instance.GetFydydjs(); int i=4; foreach (Fydydj fy in fydydjs) { excle.mergeCell(1, i, 1, i+1); excle.setValue(1, i,"center", fy.FydydjX); excle.setValue(2, i,"center", "当月"); excle.setValue(2, i+1,"center", "累计"); i += 2; } //为工作区设置样式 excle.DrawBorder(1,1,14,i-1); excle.SaveAs(Server.MapPath("..") + "//Templates//" + "线损分压统计表.xls"); excle.ReleaseExcel(); }

Dcom方式导入Excel的权限配置：1，先查看计算机管理-本地用户和组-用户（查看是否有ASPNET用户，没有则添加）
2，找到项目文件夹-属性-安全-添加-ASPNET-（添加读取和写入权限）
3，找到Microsoft.NET/Framework/v1.0.3705/CONFIG下的machine.config文件，将processModel节中的username的值设为SYSTEM（2.0以上的跳过这条）
4，在Windows的运行框中输入dcomcnfg，打开Com管理。
Microsoft Excel应用程序-属性-常规（身份验证级别：无）；
标识-启动用户；
安全-（启动和缴活权限-自定义-编辑-添加（Everyone：授所有权限））
安全-（访问权限-自定义-编辑-添加（Everyone：授所有权限））
安全-（配置权限-自定义-编辑-添加（分别对ASPNET，Users,Power Users等用户：授所有权限（或者访问、运行和配置权限）））
最后重起计算机