asp.net的com方式导出excel/excel导入

最近做个项目，需要导出excel，要主从表一起导出的，就大致写了一个方法，可能格式方面还是会进行修改，先贴出来方便以后查看

 

项目引用微软excel的com的dll即可

 

/// <summary> /// 功能：导出文件（通过数据源导出,可主从表导出） /// </summary> /// <param name="strTitle">文件名字</param> /// <param name="mainDT">主表数据源</param> /// <param name="dtParams">从表数据源集合</param> public static void Export(string strTitle, System.Data.DataTable mainDT, params System.Data.DataTable[] dtParams) { GC.Collect(); Application oApp = new Application(); Workbooks oBooks = oApp.Workbooks; Workbook oBook = oBooks.Add(true); Worksheet oSheet = (Worksheet)oBook.ActiveSheet; int _rowIndex = 4;//行索引 int _cellIndex = 1;//列索引 int _maxCellIndex = 1;//最大列索引 int _mainRowIndex = 4;//主表行索引 //绘制标题 oSheet.Cells[2, 2] = strTitle; //绘制主表数据 foreach (DataColumn col in mainDT.Columns) { _cellIndex++; //设置一行两列 if (_cellIndex > 17) { _rowIndex++; _cellIndex = 2; } oSheet.Cells[_rowIndex, _cellIndex] = col.ColumnName+":"; oSheet.get_Range(oSheet.Cells[_rowIndex, _cellIndex], oSheet.Cells[_rowIndex, _cellIndex + 2]).Select(); oSheet.get_Range(oSheet.Cells[_rowIndex, _cellIndex], oSheet.Cells[_rowIndex, _cellIndex + 2]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; _cellIndex = _cellIndex + 3; if (col.DataType == System.Type.GetType("System.DateTime"))// 日期格式 { oSheet.Cells[_rowIndex, _cellIndex] =Convert.ToDateTime(mainDT.Rows[0][col.ColumnName].ToString()).ToString("yyyy-MM-dd"); } else { oSheet.Cells[_rowIndex, _cellIndex] = mainDT.Rows[0][col.ColumnName].ToString(); } oSheet.get_Range(oSheet.Cells[_rowIndex, _cellIndex], oSheet.Cells[_rowIndex, _cellIndex + 4]).Select(); oSheet.get_Range(oSheet.Cells[_rowIndex, _cellIndex], oSheet.Cells[_rowIndex, _cellIndex + 4]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; _cellIndex = _cellIndex + 4; } _mainRowIndex = _rowIndex; _rowIndex = _rowIndex + 5;//空四行 //绘制从表数据 if (dtParams.Length > 0) { for (int i = 0; i < dtParams.Length; i++) { //设置表名 oSheet.Cells[_rowIndex, 2] = (dtParams[i] as System.Data.DataTable).TableName; oSheet.get_Range(oSheet.Cells[_rowIndex, 2], oSheet.Cells[_rowIndex, 4]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; _rowIndex++; SetExcelCell(oSheet, dtParams[i], ref _rowIndex, ref _maxCellIndex); _rowIndex = _rowIndex + 3; } } //设置样式 _rowIndex = _rowIndex + 1; //设置标题 oSheet.get_Range(oSheet.Cells[2, 2], oSheet.Cells[2, 2]).Font.Bold = true; oSheet.get_Range(oSheet.Cells[2, 2], oSheet.Cells[2, 2]).Font.Size = 20; oSheet.get_Range(oSheet.Cells[2, 2], oSheet.Cells[2, _maxCellIndex]).Select(); oSheet.get_Range(oSheet.Cells[2, 2], oSheet.Cells[2, _maxCellIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; //设置表头 //设置左边框加粗 oSheet.get_Range(oSheet.Cells[4, 2], oSheet.Cells[_mainRowIndex, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick; //设置下边框加粗 oSheet.get_Range(oSheet.Cells[_mainRowIndex, 2], oSheet.Cells[_mainRowIndex, 17]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick; //设置右边框加粗 oSheet.get_Range(oSheet.Cells[4, 17], oSheet.Cells[_mainRowIndex, 17]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick; //设置上边框加粗 oSheet.get_Range(oSheet.Cells[4, 2], oSheet.Cells[4, 17]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick; //设置表格为最适应宽度 oSheet.get_Range(oSheet.Cells[4, 2], oSheet.Cells[_rowIndex, _maxCellIndex]).Select(); oSheet.get_Range(oSheet.Cells[4, 2], oSheet.Cells[_rowIndex, _maxCellIndex]).Columns.AutoFit(); //oSheet.get_Range(oSheet.Cells[4, 2], oSheet.Cells[_rowIndex, _maxCellIndex]).Borders.LineStyle = 1; oApp.Visible = true; oBook.SaveCopyAs(System.Web.HttpContext.Current.Server.MapPath(".") +"//"+ strTitle + ".xls"); //清空 NAR(oSheet); oBook.Close(false, null, null); NAR(oBook); NAR(oBooks); oApp.Quit(); NAR(oApp); GC.Collect(); string path = System.Web.HttpContext.Current.Server.MapPath(strTitle + ".xls"); System.IO.FileInfo file = new System.IO.FileInfo(path); System.Web.HttpContext.Current.Response.Clear(); System.Web.HttpContext.Current.Response.Charset = "GB2312"; System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; // 添加头信息，为"文件下载/另存为"对话框指定默认文件名 System.Web.HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + System.Web.HttpContext.Current.Server.UrlEncode(file.Name)); // 添加头信息，指定文件大小，让浏览器能够显示下载进度 System.Web.HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString()); // 指定返回的是一个不能被客户端读取的流，必须被下载 System.Web.HttpContext.Current.Response.ContentType = "application/ms-excel"; // 把文件流发送到客户端 System.Web.HttpContext.Current.Response.WriteFile(file.FullName); System.Web.HttpContext.Current.Response.End(); } /// <summary> /// 功能：绘制数据 /// </summary> /// <param name="oSheet">工作表</param> /// <param name="dt">数据源</param> /// <param name="_rowIndex">行索引</param> /// <param name="_maxCellIndex">最大列索引</param> private static void SetExcelCell(Worksheet oSheet, System.Data.DataTable dt, ref int _rowIndex, ref int _maxCellIndex) { //开始行索引 int _startrowIndex = _rowIndex; //结束列索引 int _endcellIndex = 2; int _cellIndex = 1; _rowIndex++; //绘制表标题 foreach (DataColumn col in dt.Columns) { _cellIndex++; oSheet.Cells[_rowIndex, _cellIndex] = col.ColumnName; oSheet.get_Range(oSheet.Cells[_rowIndex, _cellIndex], oSheet.Cells[_rowIndex, _cellIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; } //绘制数据行 foreach (DataRow row in dt.Rows) { _rowIndex++; _cellIndex = 1; foreach (DataColumn col in dt.Columns) { _cellIndex++; if (col.DataType == System.Type.GetType("System.DateTime"))// 日期格式 { oSheet.Cells[_rowIndex, _cellIndex] = Convert.ToDateTime(row[col.ColumnName].ToString()).ToString("yyyy-MM-dd"); } else { oSheet.Cells[_rowIndex, _cellIndex] = row[col.ColumnName].ToString(); } oSheet.get_Range(oSheet.Cells[_rowIndex, _cellIndex], oSheet.Cells[_rowIndex, _cellIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; } } //赋值 if (_cellIndex > _maxCellIndex) { _maxCellIndex = _cellIndex; } //设置最大列 _endcellIndex = _cellIndex; //设置左边框加粗 oSheet.get_Range(oSheet.Cells[_startrowIndex, 2], oSheet.Cells[_rowIndex, 2]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick; //设置下边框加粗 oSheet.get_Range(oSheet.Cells[_rowIndex, 2], oSheet.Cells[_rowIndex, _endcellIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick; //设置右边框加粗 oSheet.get_Range(oSheet.Cells[_startrowIndex, _endcellIndex], oSheet.Cells[_rowIndex, _endcellIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick; //设置上边框加粗 oSheet.get_Range(oSheet.Cells[_startrowIndex, 2], oSheet.Cells[_startrowIndex, _endcellIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick; } /// <summary> /// 功能：清空 /// </summary> /// <param name="o">对象</param> private static void NAR(object o) { try { System.Runtime.InteropServices.Marshal.ReleaseComObject(o); } catch { } finally { o = null; } }

 

 

有了导出，自然就需要用到导入

导入就比较简单，把excel当做数据源，查询，遍历即可

但是前提需要先把exce文件上传到服务器，

然后在遍历excel数据，将数据添加到sql中，

最后删除excel文件。

 

1.上传excel文件

public void UpLoadFile() { if (FileUpload1.HasFile) { string strurl = Server.MapPath("file//") + FileUpload1.PostedFile.FileName; if (File.Exists(strurl)) { File.Delete(strurl); } FileUpload1.PostedFile.SaveAs(strurl); } }

2.遍历excel，插入到数据源中

public void InputExcel(IList<Student> list) { if (FileUpload1.HasFile) { string fileExt = Path.GetExtension(FileUpload1.FileName); if (fileExt == ".xls") { try { string strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath("file//") + FileUpload1.PostedFile.FileName + ";Extended Properties=Excel 8.0;"; OleDbConnection conn = new OleDbConnection(strCon); conn.Open(); OleDbCommand commd = new OleDbCommand("SELECT * FROM [frist$]", conn); OleDbDataAdapter adapter = new OleDbDataAdapter(commd); DataSet ds = new DataSet(); adapter.Fill(ds, "execltable"); DataTable dt = ds.Tables["execltable"]; conn.Close(); foreach (DataRow dr in dt.Rows) { Student st = new Student(); //存在移除 if (CheckExist(list, dr["姓名"].ToString(), out st)) { if (st != null) { list.Remove(st); } else { throw new Exception("错误了"); } } if (st == null) st = new Student(); st.Name = dr["姓名"].ToString(); st.Sex = dr["性别"].ToString(); st.Age = Convert.ToInt32(dr["年龄"].ToString()); st.Birthday = Convert.ToDateTime(dr["生日"].ToString()); list.Add(st); } } catch (Exception ex) { Response.Write("<center>错误:" + ex.Message + "!</center>"); } } } } private bool CheckExist(IList<Student> list, string name, out Student st) { foreach (Student s in list) { if (s.Name.Equals(name)) { st = s; return true; } } st = null; return false; }

 

3.删除服务器上的excel文件

public void DeleteFile() { string strurl = Server.MapPath("file//") + FileUpload1.PostedFile.FileName; if (File.Exists(strurl)) { File.Delete(strurl); } }

 

导入代码就是在页面后置类写的，很多地方也没做什么验证，如要写到工具类中，很多地方要重新编写