using System;
using System.IO;
using System.Text;
using System.Data;
using System.Security;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.HtmlControls;
using System.Data.OleDb;
using System.Data.SqlClient;
using System.Collections;
using System.Collections.Generic;
using Excel = Microsoft.Office.Interop.Excel;
namespace CommonFunctions
{
public class ExcelHelper
{
/// <summary>
/// 自定义要导出的字段的集合
/// </summary>
public Dictionary<string, string> Fileds
{
get;
set;
}
#region " [Import Excel]"
/// <summary>
/// 获得Excel里的Sheet集合
/// </summary>
/// <param name="filepath"></param>
/// <returns></returns>
private static ArrayList GetExcelSheetName(string filepath)
{
ArrayList al = new ArrayList();
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
conn.Open();
DataTable sheetNames = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
conn.Close();
foreach (DataRow dr in sheetNames.Rows)
{
al.Add(dr[2]);
}
}
catch (Exception ex) { }
return al;
}
/// <summary>
/// 读取指定Excel里指定Sheet里的数据
/// </summary>
/// <param name="filepath"></param>
/// <param name="sheetname"></param>
/// <returns></returns>
private static DataSet GetDataByExcelSheet(string filepath, string sheetname)
{
string strConn;
DataSet ds = new DataSet();
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filepath + ";Extended Properties=Excel 8.0;";
OleDbConnection conn = new OleDbConnection(strConn);
try
{
OleDbDataAdapter oada = new OleDbDataAdapter("select * from [" + sheetname + "]", strConn);
oada.Fill(ds);
}
catch (Exception ex) { }
return ds;
}
/// <summary>
/// 从Excel导入,支持 .xls和.xlsx这2种格式
/// </summary>
/// <param name="fileName"></param>
/// <returns></returns>
public static DataSet ImportFromExcel(string fileName)
{
FileInfo file = new FileInfo(fileName);
if (!file.Exists)
{
throw new Exception("文件不存在");
}
string extension = file.Extension;
string strConn = "";
switch (extension)
{
case ".xls":
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + "; Extended Properties='Excel 8.0;HDR=YES;IMEX=1;'";
break;
case ".xlsx":
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1;'";
break;
default:
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName + ";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1;'";
break;
}
OleDbConnection oleConn = new OleDbConnection(strConn);
oleConn.Open();
//返回Excel的架构，包括各个sheet表的名称,类型，创建时间和修改时间等
DataTable dtSheetName = oleConn.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();
}
DataSet result = new DataSet();
DataTable dt;
try
{
foreach (var table in strTableNames)
{
dt = new DataTable();
string strSql = string.Format("select * FROM [{0}]", table);
OleDbCommand oleCom = new OleDbCommand(strSql, oleConn);
using (OleDbDataReader rdr = oleCom.ExecuteReader())
{
dt.Load(rdr);
}
result.Tables.Add(dt);
}
}
catch (Exception ex)
{
//记录错误日志
}
finally
{
oleConn.Close();
}
return result;
}
#endregion
#region " [Export Excel]"
/// <summary>
/// 使用Office组件直接操作Excel (Excel2003/2007通用)
/// </summary>
/// <param name="fileName"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static bool ExportToExcel(string fileName, DataTable dt)
{
int rowIndex = 1; //Excel的行
int colIndex = 0; //Excel的列
bool isSuccess = false;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
object ms = Type.Missing;
Microsoft.Office.Interop.Excel.Workbook wk = excel.Workbooks.Add(ms);
Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//添加Excel的表头
foreach (DataColumn column in dt.Columns)
{
colIndex++;
ws.Cells[1, colIndex] = column.ColumnName;
}
//填充Excel的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
rowIndex++; //从第2行开始写入数据
colIndex = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
colIndex++;
ws.Cells[rowIndex, colIndex] = dt.Rows[i][j].ToString();
}
}
try
{
wk.SaveAs(fileName, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
isSuccess = true;
}
catch (Exception)
{
isSuccess = false;
}
excel.Quit();
return isSuccess;
}
/// <summary>
/// 使用Office组件直接操作Excel,导出自定义的字段 (Excel2003/2007通用)
/// </summary>
/// <param name="fileName"></param>
/// <param name="fileds"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static bool ExportToExcel(string strFileName, string newFileName, Dictionary<string, string> fileds, DataTable dt)
{
File.Copy(strFileName, newFileName, true);
System.Threading.Thread.Sleep(100);
GC.Collect();
int rowIndex = 1; //Excel的行
int colIndex = 0; //Excel的列
bool isSuccess = false;
Excel.Application excel = new Excel.ApplicationClass();
excel.Visible = false;
object ms = Type.Missing;
Excel._Workbook wk = excel.Workbooks.Open(newFileName, 0, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);
Excel.Worksheet ws = wk.Worksheets[1] as Excel.Worksheet;
//添加Excel的表头
foreach (var item in fileds.Keys)
{
colIndex++;
ws.Cells[1, colIndex] = item;
}
//-------------------------------------------------------------------------------------------
//Excel.Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 2]);
//range.NumberFormatLocal = "@"; //设置列为 文本格式
//range.Columns.AutoFit(); // 设置列宽为自动适应
//-------------------------------------------------------------------------------------------
//填充Excel的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
rowIndex++; //从第2行开始写入数据
colIndex = 0;
foreach (var filed in fileds.Values)
{
colIndex++;
//-------------------------------------------------------------------------------------------
//加 ' 设置单元格类型为 文本格式,防止出现 因为类型问题出现取不到列的数据
//ws.Cells[rowIndex, colIndex] ="'"+ dt.Rows[i][filed].ToString();
//-------------------------------------------------------------------------------------------
ws.Cells[rowIndex, colIndex] = dt.Rows[i][filed].ToString();
}
}
int ColCount = dt.Columns.Count;
Excel.Range headRange = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, ColCount]); //选取单元格
headRange.Font.Bold = true;//加粗显示
headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直居中
headRange.NumberFormatLocal = "@";
headRange.EntireColumn.AutoFit();//自动调整列宽
try
{
//wk.SaveAs(fileName, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
wk.Save();
isSuccess = true;
}
catch (Exception exp)
{
isSuccess = false;
}
wk.Close(false, null, null);
excel.Quit();
System.Runtime.InteropServices.Marshal.ReleaseComObject(ws);
System.Runtime.InteropServices.Marshal.ReleaseComObject(wk);
System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
ws = null;
wk = null;
excel = null;
GC.Collect();
return isSuccess;
}
/// <summary>
/// 使用Office组件直接操作Excel,导出自定义的字段 (Excel2003/2007通用)
/// </summary>
/// <param name="fileName"></param>
/// <param name="fileds"></param>
/// <param name="dt"></param>
/// <returns></returns>
public static bool ExportToExcel(string fileName, Dictionary<string, string> fileds, DataTable dt)
{
return ExportToExcel(fileName, fileds, dt, true);
}
/// 功能：使用Office组件直接操作Excel,导出自定义的字段 (Excel2003/2007通用)
/// </summary>
/// <param name="fileName">保存导出文件的全名</param>
/// <param name="fileds">自定义的表头字段</param>
/// <param name="dt">要导出的数据</param>
/// <param name="isAutoFit">是否自适应单元格的宽度</param>
/// <returns></returns>
public static bool ExportToExcel(string fileName, Dictionary<string, string> fileds, DataTable dt,bool isAutoFit)
{
int rowIndex = 1; //Excel的行
int colIndex = 0; //Excel的列
bool isSuccess = false;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Visible = false;
object ms = Type.Missing;
Microsoft.Office.Interop.Excel.Workbook wk = excel.Workbooks.Add(ms);
Microsoft.Office.Interop.Excel.Worksheet ws = wk.Worksheets[1] as Microsoft.Office.Interop.Excel.Worksheet;
//添加Excel的表头
foreach (var item in fileds.Keys)
{
colIndex++;
ws.Cells[1, colIndex] = item;
}
//填充Excel的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
rowIndex++; //从第2行开始写入数据
colIndex = 0;
foreach (var filed in fileds.Values)
{
colIndex++;
ws.Cells[rowIndex, colIndex] = dt.Rows[i][filed].ToString();
}
}
int ColCount = dt.Columns.Count;
Excel.Range headRange = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, ColCount]); //选取单元格
headRange.Font.Bold = true;//加粗显示
headRange.HorizontalAlignment = Excel.XlHAlign.xlHAlignCenter; //水平居中
headRange.VerticalAlignment = Excel.XlVAlign.xlVAlignCenter; //垂直居中
headRange.NumberFormatLocal = "@";
if (isAutoFit)
{
headRange.EntireColumn.AutoFit();//设置全部自动调整列宽
}
else
{
//仅设置第一列宽度自适应
Excel.Range range = ws.get_Range(ws.Cells[1, 1], ws.Cells[1, 2]);
range.Columns.AutoFit();
}
try
{
wk.SaveAs(fileName, ms, ms, ms, ms, ms, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
isSuccess = true;
}
catch (Exception)
{
isSuccess = false;
}
excel.Quit();//关闭excel对象
excel = null;
return isSuccess;
}
/// <summary>
/// 使用文件流直接输出Excel文件 (仅适用于Excel2003,Excel 2007会报格式化错误警告)
/// </summary>
/// <param name="strFileName"></param>
/// <param name="dt"></param>
public static void ExportToExcel(DataTable dt, string strFileName)
{
//清除Response缓存内容
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Buffer = true;
//确定字符的编码格式
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(strFileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
//HttpContext.Current.Response.Charset = "gb2312";
//HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8");
GridView dv = new GridView();
dv.DataSource = dt;
dv.DataBind();
try
{
dv.EnableViewState = false;
}
catch (Exception ex)
{ }
StringWriter stringWriter = new StringWriter();
HtmlTextWriter htmlTextWriter = new HtmlTextWriter(stringWriter);
dv.RenderControl(htmlTextWriter);
//消除乱码特别设定，非常规方法
string strExcel = "";
strExcel += htmlTextWriter.InnerWriter.ToString();
HttpContext.Current.Response.Write(strExcel);
HttpContext.Current.Response.End();
}
/// <summary>
/// 导出为CSV文件 (Excel2003/2007通用)
/// </summary>
/// <param name="FileName"></param>
/// <param name="dt"></param>
public static void ExportToCsv(string FileName, DataTable dt)
{
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("content-disposition", string.Format("attachment;filename={0}.csv", FileName));
HttpContext.Current.Response.ContentType = "application/ms-excel";
HttpContext.Current.Response.Charset = "UTF-8";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
try
{
StringBuilder sb = new StringBuilder();
//添加表头
foreach (DataColumn column in dt.Columns)
{
sb.AppendFormat("{0},", column.ColumnName.Replace(",", ""));
}
sb.Remove(sb.Length - 1, 1); //去掉最后一个","
sb.Append("\n");
//填充数据
for (int i = 0; i < dt.Rows.Count; i++)
{
for (int j = 0; j < dt.Columns.Count; j++)
{
sb.AppendFormat("{0},", dt.Rows[i][j].ToString().Replace(",", ""));
}
sb.Remove(sb.Length - 1, 1); //去掉最后一个","
sb.Append("\n");
}
HttpContext.Current.Response.Write(sb.ToString());
HttpContext.Current.Response.Flush();
}
catch (Exception ex)
{
HttpContext.Current.Response.Write(ex.Message);
}
finally
{
}
HttpContext.Current.Response.End();
}
#endregion
}
}
1150
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
