Excel导入和导出

最新推荐文章于 2024-09-14 08:25:16 发布

liushengmz

最新推荐文章于 2024-09-14 08:25:16 发布

阅读量602

点赞数

分类专栏： ASP.NET 问题文章标签： excel string object properties null application

本文链接：https://blog.csdn.net/liushengmz/article/details/4051387

版权

ASP.NET 问题专栏收录该内容

5 篇文章 0 订阅

订阅专栏

这是一个Excel的导入和导出，

为了自己的理解方便，

已经把说明写到代码的注释里面去了,

记得使用这几个函数之前一定要引用

以下几个文件哦

using System.Data.SqlClient; using System.IO; using System.Text; using System.Data.OleDb;

好了，接下来就是主题了，再次使用的时候就不要忘了。。。

#region Excel的导出 /// <summary> /// 定义导出Excel的函数 /// </summary> /// <param name="FileType"></param> /// <param name="FileName"></param> private void ExcelExport() { #region 定义导出Excel的函数 string FileType = "application/vnd.ms-excel"; string FileName = "CostCenterRecords.xls"; Response.Clear(); Response.Buffer = true; //--------- Response.Charset = "GB2312"; Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312"); //---------上面两句比较重要，这两个是设置输出的字符流的编码，一定要保持字符的输出一致，否则导出中文的时候会乱码 Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(FileName, Encoding.UTF8).ToString()); //---------上面这一句中，有人说当FileName为中文时，导出的Excel文件也会乱码，其实也是上面三个编码不一致所导致的，所以使用的时候一定要编码一致 Response.ContentType = FileType; this.EnableViewState = false; StringWriter tw = new StringWriter(); HtmlTextWriter hw = new HtmlTextWriter(tw); GridView1.RenderControl(hw); Response.Write(tw.ToString()); Response.End(); #endregion } /// <summary> /// 此方法必重写，否则会出错 /// </summary> /// <param name="control"></param> public override void VerifyRenderingInServerForm(Control control) { } #endregion #region Excel的导入 protected void ExcelImport() { //和普通的文件上传没什么区别，只是要注意一下类型就OK了 //还要注意一下的是读取Excel要先把它传上服务器再读取Excel里面的数据，读完后再删除掉Excel文件 } /// <summary> /// 以Excel为数据源获取数据集 /// </summary> /// <returns></returns> private DataTable createDataSource() { #region 以Excel为数据源获取数据集 string strCon; DataTable ds = new DataTable(); strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("~/CostCenter/C_AddRecord/ExcelFile/" + excelfilename) + ";Extended Properties=Excel 8.0;"; //关于这个strCon有点要说明一下的，就是想要导入Excel2007的文件的话，可以通过指定 //strCon这个东西来连接Excel文件的 //读取Excel2007的连接字符串是这样滴："Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Server.MapPath("~/CostCenter/C_AddRecord/ExcelFile/" + excelfilename) + ";Extended Properties='Excel 12.0;'"; OleDbConnection con = new OleDbConnection(strCon); try { con.Open(); OleDbDataAdapter da = new OleDbDataAdapter("select * from [CostCenterRecord$]", con); da.Fill(ds); da.Dispose(); con.Close(); } catch { con.Close(); } File.Delete(Server.MapPath("~/CostCenter/C_AddRecord/ExcelFile/") + excelfilename); //用完之后要删除掉文件，这里的Server.MapPath的用法也要清楚哦。。否则也可能会删除不了文件滴。。。 return ds;//好了，能够将Excel文件转成一个DataTable了，那还有什么不可以做的呢？操作DataTable应该是很容易的事吧？ #endregion } #endregion

感觉那些代码插入到这里后就变得很乱了呢？

没事。。。能用就行。。。。

最近在做这个东西的时候再次发现了一些东西：

就是在导出的时候打开会有很大的一个问题，

那就是导出的文件不是标准的Excel文件，在打

开的时候会提示你非安全文件是否要打开，

原因是用上面这种方法导出的Excel是Html文件来的，

所以呢，得用真正的Excel文件去写才行，

在网上找了找，还真的找到了一个文件，

稍微修改了一下，可以进行下导出。

原理不难，但需要先创建Excel文件到服务器

再下载，感觉不是很好。。。。

这里是它的文件和使用方法：

using System.Reflection; using Microsoft.Office.Interop.Excel; using ExcelApp = Microsoft.Office.Interop.Excel.Application; using System; using System.Collections.Generic; using System.Linq; using System.Web; namespace Something { public class ExcelClass { public ExcelClass() { // //TODO: 在此处添加构造函数逻辑 // this.m_objExcel = new ExcelApp(); } private object miss = Missing.Value; //忽略的参数OLENULL private Application m_objExcel;//Excel应用程序实例 private Workbooks m_objBooks;//工作表集合 private Workbook m_objBook;//当前操作的工作表 private Worksheet sheet;//当前操作的表格 /// <summary> /// 构建ExcelClass类 /// </summary> /// <param name="objExcel">ExcelApp</param> public ExcelClass(ExcelApp objExcel) { this.m_objExcel = objExcel; } /// <summary> /// 列标号 /// </summary> private string AList = "ABCDEFGHIJKLMNOPQRSTUVWXYZ"; /// <summary> /// 获取描述区域的字符 /// </summary> /// <param name="x"></param> /// <param name="y"></param> /// <returns></returns> 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; } public void setCloseValue(int y1, int x1, int y2, int x2, string text) { Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2));// 获取多个单元格 range.Merge(miss); // 合并单元格 range.Columns.AutoFit(); // 设置列宽为自动适应 range.HorizontalAlignment = XlHAlign.xlHAlignCenter;//设置单元格水平居中 range.Font.Bold = true; range.Font.Size = 14; range.set_Value(miss, text); } /// <summary> /// 给单元格赋值1 /// </summary> /// <param name="x">行号</param> /// <param name="y">列号</param> /// <param name="align">对齐（CENTER、LEFT、RIGHT）</param> /// <param name="text">值</param> public void setValue(int y, int x, string align, string text) { Range range = sheet.get_Range(this.GetAix(x, y), miss); range.set_Value(miss, text); if (align.ToUpper() == "CENTER") { range.HorizontalAlignment = XlHAlign.xlHAlignCenter; } if (align.ToUpper() == "LEFT") { range.HorizontalAlignment = XlHAlign.xlHAlignLeft; } if (align.ToUpper() == "RIGHT") { range.HorizontalAlignment = XlHAlign.xlHAlignRight; } } /// <summary> /// 给单元格赋值2 /// </summary> /// <param name="x">行号</param> /// <param name="y">列号</param> /// <param name="text">值</param> public void setValue(int y, int x, string text) { Range range = sheet.get_Range(this.GetAix(x, y), miss); //单元格设置 range.NumberFormatLocal = "@"; // 设置单元格格式 range.RowHeight = 20; // 设置行高 range.ColumnWidth = 20; // 设置列宽 range.Borders.LineStyle = 1; // 设置单元格边框 range.Font.Bold = false; // 加粗字体 range.Font.Size = 11; // 设置字体大小 range.Font.Color = System.Drawing.Color.Black.ToArgb(); // 设置字体颜色 range.Interior.Color = System.Drawing.Color.White.ToArgb(); // 设置单元格背景色 range.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中 range.VerticalAlignment = XlVAlign.xlVAlignCenter;// 设置单元格垂直居中 range.Columns.AutoFit(); // 设置列宽为自动适应 //range.Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;// 设置单元格左边框加粗 //range.Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;// 设置单元格右边框加粗页面设置 //sheet.PageSetup.PaperSize = XlPaperSize.xlPaperA4; // 设置页面大小为A4 //sheet.PageSetup.Orientation = XlPageOrientation.xlPortrait; // 设置垂直版面 //sheet.PageSetup.HeaderMargin = 0.0; // 设置页眉边距 //sheet.PageSetup.FooterMargin = 0.0; // 设置页脚边距 //sheet.PageSetup.LeftMargin = excel.InchesToPoints(0.354330708661417); // 设置左边距 //sheet.PageSetup.RightMargin = excel.InchesToPoints(0.354330708661417);// 设置右边距 //sheet.PageSetup.TopMargin = excel.InchesToPoints(0.393700787401575); // 设置上边距 //sheet.PageSetup.BottomMargin = excel.InchesToPoints(0.393700787401575);// 设置下边距 //sheet.PageSetup.CenterHorizontally = true; //单元格的值 range.set_Value(miss, text); } /// <summary> /// 给单元格赋值3 /// </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 y, int x, string text, System.Drawing.Font font, System.Drawing.Color color) { this.setValue(x, y, text); 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; } /// <summary> /// 赋值给Excel,Moon 2009-5-16 加，因在批量增加行时excel会出错 /// </summary> /// <param name="intable">要插入的数据DataTable</param> public void setValue(System.Data.DataTable intable) { int Rows = intable.Rows.Count; int Colum = intable.Columns.Count; string[,] datas = new string[Rows + 1, Colum]; for (int i = 0; i < Colum; i++) { datas[0, i] = intable.Columns[i].ColumnName;//设置表头 } for (int i = 0; i < Colum; i++) { for (int j = 0; j < Rows; j++) { datas[j + 1, i] = intable.Rows[j][i].ToString();//将数据放到数组中 } } Range fchR = sheet.get_Range(sheet.Cells[1, 1], sheet.Cells[Rows + 1, Colum]);//这样做的目的是防止在每行插入时，excel获取行号失败 fchR.Value2 = datas;//将数组赋值给excel fchR.NumberFormatLocal = "@"; // 设置单元格格式 fchR.RowHeight = 20; // 设置行高 fchR.ColumnWidth = 20; // 设置列宽 fchR.Borders.LineStyle = 1; // 设置单元格边框 fchR.Font.Bold = false; // 加粗字体 fchR.Font.Size = 11; // 设置字体大小 fchR.Font.Color = System.Drawing.Color.Black.ToArgb(); // 设置字体颜色 fchR.Interior.Color = System.Drawing.Color.White.ToArgb(); // 设置单元格背景色 fchR.HorizontalAlignment = XlHAlign.xlHAlignCenter;// 设置单元格水平居中 fchR.VerticalAlignment = XlVAlign.xlVAlignCenter;// 设置单元格垂直居中 fchR.Columns.AutoFit(); // 设置列宽为自动适应 } /// <summary> /// 插入新行 /// </summary> /// <param name="y">模板行号</param> public void insertRow(int y) { Range range = sheet.get_Range(GetAix(1, y), GetAix(25, y)); range.Copy(miss); range.Insert(Microsoft.Office.Interop.Excel.XlDirection.xlDown, miss); range.get_Range(GetAix(1, y), GetAix(25, y)); range.Select(); sheet.Paste(miss, miss); } /// <summary> /// 把剪切内容粘贴到当前区域 /// </summary> public void past() { string s = "a,b,c,d,e,f,g"; sheet.Paste(sheet.get_Range(this.GetAix(10, 10), miss), s); } /// <summary> /// 设置边框 /// </summary> /// <param name="x1"></param> /// <param name="y1"></param> /// <param name="x2"></param> /// <param name="y2"></param> /// <param name="Width"></param> public void setBorder(int x1, int y1, int x2, int y2, int Width) { Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); range.Borders.Weight = Width; } public void mergeCell(int x1, int y1, int x2, int y2) { Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); range.Merge(true); } public Range getRange(int x1, int y1, int x2, int y2) { Range range = sheet.get_Range(this.GetAix(x1, y1), this.GetAix(x2, y2)); return range; } public Worksheet CurrentSheet { get { return sheet; } set { this.sheet = value; } } public Workbooks CurrentWorkBooks { get { return this.m_objBooks; } set { this.m_objBooks = value; } } public Workbook CurrentWorkBook { get { return this.m_objBook; } set { this.m_objBook = value; } } /// <summary> /// 打开Excel文件 /// </summary> /// <param name="filename">路径</param> public void OpenExcelFile(string filename) { UserControl(false); m_objExcel.Workbooks.Open(filename, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss, miss); m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = m_objExcel.ActiveWorkbook; sheet = (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; } public void CreateExceFile() { UserControl(false); m_objBooks = (Workbooks)m_objExcel.Workbooks; m_objBook = (Workbook)(m_objBooks.Add(miss)); sheet = (Worksheet)m_objBook.ActiveSheet; } public string SaveAs(string FileName) { m_objBook.SaveAs(FileName, Microsoft.Office.Interop.Excel.XlFileFormat.xlExcel8, miss, miss, miss, miss, XlSaveAsAccessMode.xlNoChange, XlSaveConflictResolution.xlLocalSessionChanges, miss, miss, miss, miss); return m_objBook.FullName; } public void ReleaseExcel() { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objExcel); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBooks); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)m_objBook); System.Runtime.InteropServices.Marshal.ReleaseComObject((object)sheet); m_objExcel = null; m_objBooks = null; m_objBook = null; sheet = null; GC.Collect(); } public bool KillAllExcelApp() { try { if (m_objExcel != null) // isRunning是判断xlApp是怎么启动的flag. { m_objExcel.Quit(); System.Runtime.InteropServices.Marshal.ReleaseComObject(m_objExcel); //释放COM组件，其实就是将其引用计数减1 //System.Diagnostics.Process theProc; foreach (System.Diagnostics.Process theProc in System.Diagnostics.Process.GetProcessesByName("EXCEL")) { //先关闭图形窗口。如果关闭失败...有的时候在状态里看不到图形窗口的excel了， //但是在进程里仍然有Microsoft.Office.Interop.Excel.EXE的进程存在，那么就需要杀掉它:p if (theProc.CloseMainWindow() == false) { theProc.Kill(); } } m_objExcel = null; return true; } } catch { return false; } return true; } } }

使用方法如下：

string sql ="select * from w_www"; SqlConnection conn = new SqlConnection("server=.,database=eH;uid=user;pwd=123"); conn.Open(); SqlDataAdapter sda = new SqlDataAdapter(sql, conn); System.Data.DataTable dt = new System.Data.DataTable(); sda.Fill(dt); ExcelClass ff = new ExcelClass(); ff.CreateExceFile(); ff.setValue(dt); ff.CurrentSheet.Name = "我爱你"; string savePath = Server.MapPath(@"~/FileTemp/") + ff.CurrentWorkBook.Name; string filename = ff.SaveAs(savePath); //关闭进程 ff.KillAllExcelApp(); Response.Redirect(@"~/FileTemp/" + filename.Split('//')[filename.Split('//').Length - 1]);

得引用一下Interop.Microsoft.Office.Core这个dll