Excel操作类代码,Asp.Net代码库,德仔网

代码语言
代码分类
【Asp.Net】 Excel操作类
作者:handsometone1982 / 发布于2015/1/19/ 547

	// Copyright (C) 2013 fanyong All rights reserved.
	// Created Date: 2013-04-10
	// Modified Date 2013-04-11
	// File: ExcelHelper.cs
	// Author: fanyong@gmail.com
	// Version: V1.0
	// Description: Excel helper

	using System;
	using System.Collections.Generic;
	using System.Text;
	using System.Data;
	using System.Data.OleDb;
	using System.IO;
	using Microsoft.Office.Interop.Excel;

	namespace BookFilter
	{
	 /// <summary>
	 /// Excel Helper v0.1
	 /// </summary>
	 public class ExcelHelper
	 {
	 public ExcelHelper()
	 {
	 //
	 }

	 public DataSet Excel2DataSet(string path)
	 {
	 DataSet ds = new DataSet();
	 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";";
	 try
	 {
	 using (OleDbConnection conn = new OleDbConnection(strConn))
	 {
	 conn.Open();
	 //返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
	 DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });

	 //包含excel中表名的字符串数组
	 string[] strTableNames = new string[dtSheetName.Rows.Count];
	 for (int k = 0; k < dtSheetName.Rows.Count; k++)
	 {
	 strTableNames[k] = dtSheetName.Rows[k]["TABLE_NAME"].ToString();
	 }

	 OleDbDataAdapter myCommand = null;
	 DataTable dt = new DataTable();

	 //从指定的表明查询数据,可先把所有表明列出来供用户选择
	 string strExcel = "select * from [" + strTableNames[0] + "]";
	 myCommand = new OleDbDataAdapter(strExcel, strConn);
	 myCommand.Fill(ds, "table1");
	 conn.Close();
	 }
	 }
	 catch (Exception ex)
	 {
	 throw ex;
	 //RedirectErrorPage("导出excel数据到dataset出错，请联系管理员" + ex);
	 }
	 return ds;
	 }

	 /// <summary>
	 /// 通过工作表名 获取数据
	 /// </summary>
	 /// <param name="name"></param>
	 /// <returns></returns>
	 public System.Data.DataTable GetContentBySheetName(OleDbConnection conn, string name)
	 {
	 System.Data.DataTable dt = new System.Data.DataTable();
	 OleDbDataAdapter myCommand = null;
	 string strExcel = "select * from [" + name + "]";

	 using (myCommand = new OleDbDataAdapter(strExcel, conn))
	 {
	 myCommand.Fill(dt);
	 return dt;
	 }

	 }

	 /// <summary>
	 /// 加载excel文件到dataset中
	 /// </summary>
	 /// <param name="path">Excel文件路径</param>
	 /// <returns>DataSet</returns>
	 public DataSet Excel2DataSetWithSheet(string path)
	 {
	 DataSet dataSet = new DataSet();
	 string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + path + ";" + "Extended Properties=\"Excel 8.0;IMEX=1\";";
	 try
	 {
	 System.Data.DataTable dt = new System.Data.DataTable();

	 using (OleDbConnection conn = new OleDbConnection(strConn))
	 {
	 if (conn.State == ConnectionState.Closed)
	 {
	 conn.Open();
	 }
	 // 可以过滤隐藏sheet
	 List<string> tableNames = GetSheetNameFromExcel(conn);

	 string[] strArr = new string[tableNames.Count];

	 tableNames.CopyTo(strArr);

	 List<string> sheets = new List<string>();
	 sheets.AddRange(strArr);

	
	 // 以sheetName新建文件夹
	 for (int i = 0; i < sheets.Count; i++)
	 {
	 //处理sheetName的#号 和 去掉末尾的$ like this:www#16k#net#cn$
	 string curSheetName = sheets[i];
	 curSheetName = curSheetName.Replace('#', '.');
	 curSheetName = curSheetName.TrimEnd('$');

	 //拼接创建的目录
	 int last = path.LastIndexOf('\\');
	 string dir = path.Substring(0, last);
	 string dirPath = dir + "\\" + curSheetName;

	 if (!Directory.Exists(dirPath))
	 {
	 Directory.CreateDirectory(dirPath);
	 }
	 }

	
	 if (null != tableNames && tableNames.Count > 0)
	 {
	 foreach (string strTable in tableNames)
	 {
	 //获取sheet页的内容
	 dt = GetContentBySheetName(conn, strTable);
	 dt.TableName = strTable;
	 dataSet.Tables.Add(dt);
	 }
	 }
	 }
	 }
	 catch (Exception ex)
	 {
	 // Excel转化成DataSet异常
	 throw ex;
	 }
	 return dataSet;
	 }

	 /// <summary>
	 /// 获取Excel 中的工作表
	 /// </summary>
	 /// <returns></returns>
	 public List<string> GetSheetNameFromExcel(OleDbConnection conn)
	 {
	 System.Data.DataTable dtSheetName = null;
	 try
	 {
	 dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "Table" });
	 List<string> strTableNames = new List<string>();
	 for (int i = 0; i < dtSheetName.Rows.Count; i++)
	 {
	 string s = dtSheetName.Rows[i]["TABLE_NAME"].ToString();

	 //过滤一下没用的表，Excel 默认生成的隐藏文件
	 if (!s.Contains("_FilterDatabase") && s.LastIndexOf('_') + 1 != s.Length)
	 {
	 strTableNames.Add(s);
	 }
	 }
	 return strTableNames;
	 }
	 catch (Exception ex)
	 {
	 throw ex;
	 }
	 }

	 /// <summary>
	 /// 将List转化成DataSet
	 /// </summary>
	 /// <typeparam name="T">泛型</typeparam>
	 /// <param name="list">list</param>
	 /// <returns>dataset</returns>
	 private DataSet ListToDataSet<T>(List<T> list)
	 {
	 //list is nothing or has nothing, return nothing (or add exception handling)
	 if (list == null || list.Count == 0) { return null; }

	 //get the type of the first obj in the list
	 Type obj = list[0].GetType();

	 //now grab all properties
	 System.Reflection.PropertyInfo[] properties = obj.GetProperties();

	 //make sure the obj has properties, return nothing (or add exception handling)
	 if (properties.Length == 0) { return null; }

	 //it does so create the dataset and table
	 DataSet dataSet = new DataSet();
	 DataTable dataTable = new DataTable();

	 //now build the columns from the properties
	 System.Data.DataColumn[] columns = new DataColumn[properties.Length];
	 for (int i = 0; i < properties.Length; i++)
	 {
	 columns[i] = new DataColumn(properties[i].Name, properties[i].PropertyType);
	 }

	 //add columns to table
	 dataTable.Columns.AddRange(columns);

	 //now add the list values to the table
	 foreach (var item in list)
	 {
	 //create a new row from table
	 var dataRow = dataTable.NewRow();

	 //now we have to iterate thru each property of the item and retrieve it's value for the corresponding row's cell
	 var itemProperties = item.GetType().GetProperties();

	 for (int i = 0; i < itemProperties.Length; i++)
	 {
	 dataRow[i] = itemProperties[i].GetValue(item, null);
	 }

	 //now add the populated row to the table
	 dataTable.Rows.Add(dataRow);
	 }

	 //add table to dataset
	 dataSet.Tables.Add(dataTable);

	 //return dataset
	 return dataSet;
	 }

	 /// <summary>
	 /// 下载execl文件，适用于web项目
	 /// </summary>
	 private void DownloadExcel()
	 {
	 //Response.Clear();
	 //Response.ClearHeaders();
	 //Response.Buffer = true;
	 //Response.AddHeader("Accept-Language", "zh-cn");
	 //// UrlEncode防止文件名出现乱码
	 //string fileName = HttpUtility.UrlEncode(this.txtPayMonth.Text + this.ddlDataSourceExport.SelectedItem + "result.csv");
	 //Response.AddHeader("content-disposition", "attachment; filename=" + fileName);
	 //Response.ContentType = "application/octet-stream";
	 ////ExportToExcel(ds); // 把DataSet导出成Excel.csv格式
	 //authorPay.ExportToExcel(ds);
	 //Response.Flush();
	 }

	 /// <summary>
	 /// 把DataSet导出到Excel中并且可以分sheet
	 /// </summary>
	 /// <param name="dataSet">要导出的数据来源</param>
	 /// <param name="fileName">导出的Excel名称</param>
	 public void DataSetToLocalExcel(DataSet dataSet, string fileName)
	 {
	 string outputPath = string.Empty;

	 bool deleteOldFile = true;
	 if (deleteOldFile)
	 {
	 if (System.IO.File.Exists(outputPath)) { System.IO.File.Delete(outputPath); }
	 }

	 // Create the Excel Application object
	 Application excelApp = new Application();

	 // Create a new Excel Workbook
	 Workbook excelWorkbook = excelApp.Workbooks.Add(Type.Missing);

	 int sheetIndex = 0;

	 // 遍历每张 DataTable
	 foreach (System.Data.DataTable dt in dataSet.Tables)
	 {
	 // Create a new Sheet
	 Worksheet excelSheet = (Worksheet)excelWorkbook.Sheets.Add(
	 excelWorkbook.Sheets.get_Item(++sheetIndex),
	 Type.Missing, 1, XlSheetType.xlWorksheet);
	 excelSheet.Name = dt.TableName;

	 //初始化Sheet中的变量
	 int rowIndex = 1;
	 int colIndex = 1;
	
	 //列出标题
	 foreach (DataColumn col in dt.Columns)
	 {
	 //LogHelper.Info(" 调用Excel组件，col.ColumnName：" + col.ColumnName );
	 excelApp.Cells[1, colIndex] = col.ColumnName;
	 excelSheet.get_Range(excelApp.Cells[1, colIndex], excelApp.Cells[1, colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//设置标题格式为居中对齐
	 colIndex++;
	 }

	 // Mark the first row as BOLD
	 ((Range)excelSheet.Rows[1, Type.Missing]).Font.Bold = true;

	 //列出行
	 for (int i = 0; i < dt.Rows.Count; i++)
	 {
	 DataRow row = dt.Rows[i];

	 //新起一行，当前单元格移至行首
	 rowIndex++;
	 colIndex = 1;

	 foreach (DataColumn col in dt.Columns)
	 {
	 if (col.DataType == System.Type.GetType("System.String"))
	 {
	 excelApp.Cells[rowIndex, colIndex] = "'" + row[col.ColumnName].ToString();
	 }
	 else
	 {
	 excelApp.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
	 }
	 colIndex++;
	 }

	 // 设置边框
	 // 计算最后一列的字母标识
	 string finalColLetter = string.Empty;
	 string colCharset = "ABCDEFGHIJKLMNOPQRSTUVWXYZ";
	 int colCharsetLen = colCharset.Length;

	 if (dt.Columns.Count > colCharsetLen)
	 {
	 finalColLetter = colCharset.Substring(
	 (dt.Columns.Count - 1) / colCharsetLen - 1, 1);
	 }

	 finalColLetter += colCharset.Substring(
	 (dt.Columns.Count - 1) % colCharsetLen, 1);

	 // 列出范围：标识成 A1:F9 这样的
	 string excelRange = string.Format("A1:{0}{1}",
	 finalColLetter, (dt.Rows.Count + 1));

	 //使用最佳宽度,设置样式
	 Range allDataWithTitleRange = excelSheet.get_Range(excelRange, Type.Missing);
	 allDataWithTitleRange.Select();
	 allDataWithTitleRange.Columns.AutoFit();
	 allDataWithTitleRange.Borders.LineStyle = 1;//将导出Excel加上边框
	 }
	 }
	
	 excelApp.Application.DisplayAlerts = false;
	 // 保存Excel并且关闭excelWorkbook对象
	 excelWorkbook.SaveAs(outputPath, XlFileFormat.xlWorkbookNormal, Type.Missing,
	 Type.Missing, Type.Missing, Type.Missing, XlSaveAsAccessMode.xlExclusive,
	 Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
	
	 excelWorkbook.Close(true, Type.Missing, Type.Missing);
	 excelWorkbook = null;

	 // Release the Application object
	 excelApp.Quit();
	 excelApp = null;

	 // 回收未引用对象
	 GC.Collect();
	 GC.WaitForPendingFinalizers();
	 }

	 }
	}
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　Excel　
同语言下
可能有用的
handsometone1982贡献的其它代码(7)