C#操作excel
//C#读取Excel
private void ReadExcel(){
OleDbCommand cmd = null;
OleDbConnection conn = null;
string fileName = hidFileName.Text + ".xls";
string filePath = Path.Combine(Server.MapPath("Excell/ImportExcelTempFolder"), fileName);
try
{
String connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
string message = "";
int count = 0;
using (conn = new OleDbConnection(connString))
{
if (conn.State == ConnectionState.Closed)
conn.Open();
cmd = conn.CreateCommand();
string strSql = "SELECT [老师], [课程],[年级],[班级],[合作老师] FROM [授课安排$]";
cmd.CommandText = strSql;
using (OleDbDataReader dr = cmd.ExecuteReader(CommandBehavior.CloseConnection))
{
int i = 0;
while (dr.Read())
{
i++;
......
}
}
}
}
catch (Exception ex)
{
if (File.Exists(filePath))
{
File.Delete(filePath);
}
throw ex;
}
finally
{
if (conn != null && conn.State == ConnectionState.Open)
conn.Close();
}
}
//C#导出为Excel
//这里我写了一个通用类，专门用来操作Excel
using System;
using System.IO;
using System.Data;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Text;
using System.Globalization;
using System.Collections;
using System.Data.OleDb;
namespace Com.DRPENG.SDXY.UI.Common
{
public class ExcelHelper
{
static object obj = new object();
#region Fields
string _fileName;
DataTable _dataSource;
string[] _titles = null;
string[] _fields = null;
int _maxRecords = 1000;
#endregion
#region Properties
/// <summary>
/// 限制输出到 Excel 的最大记录数。超出则抛出异常
/// </summary>
public int MaxRecords
{
set { _maxRecords = value; }
get { return _maxRecords; }
}
/// <summary>
/// 输出到浏览器的 Excel 文件名
/// </summary>
public string FileName
{
set { _fileName = value; }
get { return _fileName; }
}
#endregion
#region .ctor
/// <summary>
/// 构造函数
/// </summary>
/// <param name="titles">要输出到 Excel 的列标题的数组</param>
/// <param name="fields">要输出到 Excel 的字段名称数组</param>
/// <param name="dataSource">数据源</param>
public ExcelHelper(string[] titles, string[] fields, DataTable dataSource)
: this(titles, dataSource)
{
if (fields == null || fields.Length == 0)
throw new ArgumentNullException("fields");
if (titles.Length != fields.Length)
throw new ArgumentException("titles.Length != fields.Length", "fields");
_fields = fields;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="titles">要输出到 Excel 的列标题的数组</param>
/// <param name="dataSource">数据源</param>
public ExcelHelper(string[] titles, DataTable dataSource)
: this(dataSource)
{
if (titles == null || titles.Length == 0)
throw new ArgumentNullException("titles");
//if (titles.Length != dataSource.Columns.Count)
// throw new ArgumentException("titles.Length != dataSource.Columns.Count", "dataSource");
_titles = titles;
}
/// <summary>
/// 构造函数
/// </summary>
/// <param name="dataSource">数据源</param>
public ExcelHelper(DataTable dataSource)
{
if (dataSource == null)
throw new ArgumentNullException("dataSource");
// maybe more checks needed here (IEnumerable, IList, IListSource, ) ???
// 很难判断，先简单的使用 DataTable
_dataSource = dataSource;
}
public ExcelHelper() { }
#endregion
#region public Methods
/// <summary>
/// 导出到 Excel 并提示下载
/// </summary>
/// <param name="dg">DataGrid</param>
public void Export(DataGrid dg)
{
if (dg == null)
throw new ArgumentNullException("dg");
if (dg.AllowPaging || dg.PageCount > 1)
throw new ArgumentException("paged DataGrid can't be exported.", "dg");
// 添加标题样式
dg.HeaderStyle.Font.Bold = true;
dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
RenderExcel(dg);
}
/// <summary>
/// 导出到 Excel 并提示下载
/// </summary>
public void Export()
{
if (_dataSource == null)
throw new Exception("数据源尚未初始化");
if (_fields == null && _titles != null && _titles.Length != _dataSource.Columns.Count)
throw new Exception("_titles.Length != _dataSource.Columns.Count");
if (_dataSource.Rows.Count > _maxRecords)
throw new Exception("导出数据条数超过限制。请设置 MaxRecords 属性以定义导出的最多记录数。");
DataGrid dg = new DataGrid();
dg.DataSource = _dataSource;
if (_titles == null)
{
dg.AutoGenerateColumns = true;
}
else
{
dg.AutoGenerateColumns = false;
int cnt = _titles.Length;
System.Web.UI.WebControls.BoundColumn col;
if (_fields == null)
{
for (int i = 0; i < cnt; i++)
{
col = new System.Web.UI.WebControls.BoundColumn();
col.HeaderText = _titles[i];
col.DataField = _dataSource.Columns[i].ColumnName;
dg.Columns.Add(col);
}
}
else
{
for (int i = 0; i < cnt; i++)
{
col = new System.Web.UI.WebControls.BoundColumn();
col.HeaderText = _titles[i];
col.DataField = _fields[i];
dg.Columns.Add(col);
}
}
}
// 添加标题样式
dg.HeaderStyle.Font.Bold = true;
dg.HeaderStyle.BackColor = System.Drawing.Color.LightGray;
dg.ItemDataBound += new DataGridItemEventHandler(DataGridItemDataBound);
dg.DataBind();
RenderExcel(dg);
}
#endregion
#region private Methods
private void RenderExcel(Control c)
{
// 确保有一个合法的输出文件名
if (_fileName == null || _fileName == string.Empty || !(_fileName.ToLower().EndsWith(".xls")))
_fileName = GetRandomFileName();
HttpResponse response = HttpContext.Current.Response;
response.Charset = "GB2312";
response.ContentEncoding = Encoding.GetEncoding("GB2312");
response.ContentType = "application/ms-excel/msword";
response.AppendHeader("Content-Disposition", "attachment;filename=" +
HttpUtility.UrlEncode(_fileName));
CultureInfo cult = new CultureInfo("zh-CN", true);
StringWriter sw = new StringWriter(cult);
HtmlTextWriter writer = new HtmlTextWriter(sw);
writer.WriteLine("<meta http-equiv=\"Content-Type\" content=\"text/html;charset=GB2312\">");
DataGrid dg = c as DataGrid;
if (dg != null)
{
dg.RenderControl(writer);
}
//else
//{
// ASPxGrid xgrid = c as ASPxGrid;
// if (xgrid != null)
// xgrid.RenderControl(writer);
// else
// throw new ArgumentException("only supports DataGrid or ASPxGrid.", "c");
//}
c.Dispose();
response.Write(sw.ToString());
response.End();
}
/// <summary>
/// 得到一个随意的文件名
/// </summary>
/// <returns></returns>
private string GetRandomFileName()
{
Random rnd = new Random((int)(DateTime.Now.Ticks));
string s = rnd.Next(Int32.MaxValue).ToString();
return DateTime.Now.ToShortDateString() + "_" + s + ".xls";
}
private void DataGridItemDataBound(object sender, System.Web.UI.WebControls.DataGridItemEventArgs e)
{
if (e.Item.ItemType == ListItemType.Item || e.Item.ItemType == ListItemType.AlternatingItem)
{
e.Item.Attributes.Add("style", "vnd.ms-excel.numberformat:@");
//e.Item.Cells[3].Attributes.Add("style", "vnd.ms-excel.numberformat:￥#,###.00");
}
}
#endregion
#region Transfer
private void OutputExcel()
{
/* //1. 导出一个不分页的 DataGrid 到 Excel.
ExcelHelper helper = new ExcelHelper();
//设置文件名(可省。省略则自动生成一个随机的文件名)
helper.FileName = "xxx.xls";
helper.Export(new DataGrid());
//2. 导出分页的 DataGrid，需要同时指定他的数据源(DataTable)
DataTable dt = new DataTable();
ExcelHelper helper = new ExcelHelper(dt);
// 最大导出条数(可省)
helper.MaxRecords = 2000;
helper.Export();
// 3. 如果要指定列标题，这样调用：
DataTable dt = new DataTable();
ExcelHelper helper = new ExcelHelper(
new string[] { "列标题1", "列标题2", },
dt);
helper.Export();
// 4. 如果还要指定字段名称， 这样调用（因为输出的字段名称不一定要和 DataTable 里字段名称的次序相同。
DataTable dt = new DataTable();
ExcelHelper helper = new ExcelHelper(
new string[] { "代码", "部门名称", "详细描述", },
new string[] { "Code", "Name", "Description", },
this.GetData());
helper.Export();*/
}
#endregion
#region 从DataSet导入Excel [快速导入]
/// <summary>
/// 从DataSet导入Excel [快速导入]
/// </summary>
/// <param name="filePath">Excel文件路径及文件名</param>
/// <param name="ds">数据集</param>
/// <returns>成功true，失败false</returns>
public static void ImportExcel(string filePath, DataSet ds)
{
try
{
//将要生成的Excel文件
StreamWriter writer = new StreamWriter(filePath, false);
writer.WriteLine("<?xml version=\"1.0\"?>");
writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
//Excel工作薄开始
writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40//">");
writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer.WriteLine(" <Author>Mrluo735</Author>");
writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
writer.WriteLine(" <Company>Mrluo735</Company>");
writer.WriteLine(" <Version>11.6408</Version>");
writer.WriteLine(" </DocumentProperties>");
writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
writer.WriteLine(" <WindowTopX>480</WindowTopX>");
writer.WriteLine(" <WindowTopY>15</WindowTopY>");
writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
writer.WriteLine(" </ExcelWorkbook>");
//Excel工作薄结束
//工作薄样式
writer.WriteLine("<Styles>");
writer.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
writer.WriteLine(" <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
writer.WriteLine(" <Borders>");
writer.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/> ");
writer.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" </Borders>");
writer.WriteLine(string.Format("<Font ss:FontName=\"{0}\" x:CharSet=\"{1}\" ss:Size=\"{2}\"/>", "宋体", 134, 12));
writer.WriteLine(" <Interior/>");
writer.WriteLine(" <Protection/>");
writer.WriteLine("</Style>");
writer.WriteLine(" <Style ss:ID=\"s21\">");
writer.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
writer.WriteLine(" </Style>");
writer.WriteLine("<Style ss:ID=\"BoldColumn\">");
writer.WriteLine(" <Font ss:FontName=\"宋体\" ss:Bold=\"1\"/>");
writer.WriteLine(" <Borders>");
writer.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" </Borders>");
writer.WriteLine("</Style>");
//文本样式
writer.WriteLine("<Style ss:ID=\"StringLiteral\">");
writer.WriteLine(" <NumberFormat ss:Format=\"@\"/>");
writer.WriteLine("</Style>");
//浮点型样式
writer.WriteLine("<Style ss:ID=\"Decimal\">");
writer.WriteLine(" <NumberFormat ss:Format=\"0.00\"/>");
writer.WriteLine("</Style>");
//整型样式
writer.WriteLine("<Style ss:ID=\"Integer\">");
writer.WriteLine(" <NumberFormat ss:Format=\"0\"/>");
writer.WriteLine("</Style>");
//日期样式
writer.WriteLine("<Style ss:ID=\"DateLiteral\">");
writer.WriteLine(" <NumberFormat ss:Format=\"mm/dd/yyyy;@\"/>");
writer.WriteLine("</Style>");
writer.WriteLine(" </Styles>");
for (int i = 0; i < ds.Tables.Count; i++)
{
int rows = ds.Tables[i].Rows.Count + 1;
int cols = ds.Tables[i].Columns.Count;
//第i个工作表
writer.WriteLine(string.Format("<Worksheet ss:Name=\"{0}\">", ds.Tables[i].TableName));
writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
writer.WriteLine(" x:FullRows=\"1\">");
//ExpandedColumnCount：代表Excel文档中的列数
//ExpandedRowCount：代表Excel文档中的行数
//指定每一列的宽度
for (int c = 0; c < ds.Tables[i].Columns.Count; c++)
{
writer.WriteLine(string.Format("<Column ss:Index=\"{0}\" ss:AutoFitWidth=\"{1}\" ss:Width=\"{2}\"/> ", c + 1, 1, 80));
}
//生成标题
writer.WriteLine(string.Format("<Row ss:AutoFitHeight=\"{0}\" ss:Height=\"{1}\">", 0, 28.5));
foreach (DataColumn eachCloumn in ds.Tables[i].Columns)
{
writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
writer.Write(eachCloumn.ColumnName.ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
//生成数据记录
foreach (DataRow eachRow in ds.Tables[i].Rows)
{
writer.WriteLine("<Row ss:AutoFitHeight=\"0\">");
for (int currentRow = 0; currentRow != cols; currentRow++)
{
object[] getValue = ExcelContent(eachRow[currentRow]);
writer.Write(string.Format("<Cell ss:StyleID=\"{0}\"><Data ss:Type=\"{1}\">", getValue[0], getValue[1]));
writer.Write(getValue[2]);
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
}
writer.WriteLine("</Table>");
writer.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine("<Selected/>");
writer.WriteLine("<Panes>");
writer.WriteLine("<Pane>");
writer.WriteLine(" <Number>3</Number>");
writer.WriteLine(" <ActiveRow>1</ActiveRow>");
writer.WriteLine("</Pane>");
writer.WriteLine("</Panes>");
writer.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine("</WorksheetOptions>");
writer.WriteLine("</Worksheet>");
}
writer.WriteLine("</Workbook>");
writer.Close();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 从DataTable导入Excel [快速导入]
/// <summary>
/// 从DataTable导入Excel [快速导入]
/// </summary>
/// <param name="filePath">Excel文件路径及文件名</param>
/// <param name="ds">数据集</param>
/// <returns>成功true，失败false</returns>
public static void ImportExcel(string filePath, DataTable dt)
{
try
{
//将要生成的Excel文件
StreamWriter writer = new StreamWriter(filePath, false);
writer.WriteLine("<?xml version=\"1.0\"?>");
writer.WriteLine("<?mso-application progid=\"Excel.Sheet\"?>");
//Excel工作薄开始
writer.WriteLine("<Workbook xmlns=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.WriteLine(" xmlns:o=\"urn:schemas-microsoft-com:office:office\"");
writer.WriteLine(" xmlns:x=\"urn:schemas-microsoft-com:office:excel\"");
writer.WriteLine(" xmlns:ss=\"urn:schemas-microsoft-com:office:spreadsheet\"");
writer.WriteLine(" xmlns:html=\"http://www.w3.org/TR/REC-html40//">");
writer.WriteLine(" <DocumentProperties xmlns=\"urn:schemas-microsoft-com:office:office\">");
writer.WriteLine(" <Author>Mrluo735</Author>");
writer.WriteLine(string.Format(" <Created>{0}T{1}Z</Created>", DateTime.Now.ToString("yyyy-mm-dd"), DateTime.Now.ToString("HH:MM:SS")));
writer.WriteLine(" <Company>Mrluo735</Company>");
writer.WriteLine(" <Version>11.6408</Version>");
writer.WriteLine(" </DocumentProperties>");
writer.WriteLine(" <ExcelWorkbook xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine(" <WindowHeight>8955</WindowHeight>");
writer.WriteLine(" <WindowWidth>11355</WindowWidth>");
writer.WriteLine(" <WindowTopX>480</WindowTopX>");
writer.WriteLine(" <WindowTopY>15</WindowTopY>");
writer.WriteLine(" <ProtectStructure>False</ProtectStructure>");
writer.WriteLine(" <ProtectWindows>False</ProtectWindows>");
writer.WriteLine(" </ExcelWorkbook>");
//Excel工作薄结束
//工作薄样式
writer.WriteLine("<Styles>");
writer.WriteLine("<Style ss:ID=\"Default\" ss:Name=\"Normal\">");
writer.WriteLine(" <Alignment ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
writer.WriteLine(" <Borders>");
writer.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/> ");
writer.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" </Borders>");
writer.WriteLine(string.Format("<Font ss:FontName=\"{0}\" x:CharSet=\"{1}\" ss:Size=\"{2}\"/>", "宋体", 134, 12));
writer.WriteLine(" <Interior/>");
writer.WriteLine(" <Protection/>");
writer.WriteLine("</Style>");
writer.WriteLine(" <Style ss:ID=\"s21\">");
writer.WriteLine(" <Alignment ss:Horizontal=\"Center\" ss:Vertical=\"Center\" ss:WrapText=\"1\"/>");
writer.WriteLine(" </Style>");
writer.WriteLine("<Style ss:ID=\"BoldColumn\">");
writer.WriteLine(" <Font ss:FontName=\"宋体\" ss:Bold=\"1\"/>");
writer.WriteLine(" <Borders>");
writer.WriteLine(" <Border ss:Position=\"Bottom\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Left\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Right\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" <Border ss:Position=\"Top\" ss:LineStyle=\"Continuous\" ss:Weight=\"1\"/>");
writer.WriteLine(" </Borders>");
writer.WriteLine("</Style>");
//文本样式
writer.WriteLine("<Style ss:ID=\"StringLiteral\">");
writer.WriteLine(" <NumberFormat ss:Format=\"@\"/>");
writer.WriteLine("</Style>");
//浮点型样式
writer.WriteLine("<Style ss:ID=\"Decimal\">");
writer.WriteLine(" <NumberFormat ss:Format=\"0.00\"/>");
writer.WriteLine("</Style>");
//整型样式
writer.WriteLine("<Style ss:ID=\"Integer\">");
writer.WriteLine(" <NumberFormat ss:Format=\"0\"/>");
writer.WriteLine("</Style>");
//日期样式
writer.WriteLine("<Style ss:ID=\"DateLiteral\">");
writer.WriteLine(" <NumberFormat ss:Format=\"yyyy/mm/dd;@\"/>");
writer.WriteLine("</Style>");
writer.WriteLine(" </Styles>");
int rows = dt.Rows.Count + 1;
int cols = dt.Columns.Count;
//第i个工作表
writer.WriteLine(string.Format("<Worksheet ss:Name=\"{0}\">", dt.TableName));
writer.WriteLine(string.Format(" <Table ss:ExpandedColumnCount=\"{0}\" ss:ExpandedRowCount=\"{1}\" x:FullColumns=\"1\"", cols.ToString(), rows.ToString()));
writer.WriteLine(" x:FullRows=\"1\">");
//ExpandedColumnCount：代表Excel文档中的列数
//ExpandedRowCount：代表Excel文档中的行数
//指定每一列的宽度
for (int c = 0; c < dt.Columns.Count; c++)
{
writer.WriteLine(string.Format("<Column ss:Index=\"{0}\" ss:AutoFitWidth=\"{1}\" ss:Width=\"{2}\"/> ", c + 1, 1, 80));
}
//生成标题
writer.WriteLine(string.Format("<Row ss:AutoFitHeight=\"{0}\" ss:Height=\"{1}\">", 0, 28.5));
foreach (DataColumn eachCloumn in dt.Columns)
{
writer.Write("<Cell ss:StyleID=\"s21\"><Data ss:Type=\"String\">");
writer.Write(eachCloumn.ColumnName.ToString());
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
//生成数据记录
foreach (DataRow eachRow in dt.Rows)
{
writer.WriteLine("<Row ss:AutoFitHeight=\"0\">");
for (int currentRow = 0; currentRow != cols; currentRow++)
{
object[] getValue = ExcelContent(eachRow[currentRow]);
writer.Write(string.Format("<Cell ss:StyleID=\"{0}\"><Data ss:Type=\"{1}\">", getValue[0], getValue[1]));
writer.Write(getValue[2]);
writer.WriteLine("</Data></Cell>");
}
writer.WriteLine("</Row>");
}
writer.WriteLine("</Table>");
writer.WriteLine("<WorksheetOptions xmlns=\"urn:schemas-microsoft-com:office:excel\">");
writer.WriteLine("<Selected/>");
writer.WriteLine("<Panes>");
writer.WriteLine("<Pane>");
writer.WriteLine(" <Number>3</Number>");
writer.WriteLine(" <ActiveRow>1</ActiveRow>");
writer.WriteLine("</Pane>");
writer.WriteLine("</Panes>");
writer.WriteLine("<ProtectObjects>False</ProtectObjects>");
writer.WriteLine("<ProtectScenarios>False</ProtectScenarios>");
writer.WriteLine("</WorksheetOptions>");
writer.WriteLine("</Worksheet>");
writer.WriteLine("</Workbook>");
writer.Close();
}
catch (Exception ex)
{
throw ex;
}
}
#endregion
#region 根据C#值把它转换成Excel值 [ExcelContent(...)]
/// <summary>
/// 根据C#值把它转换成Excel值 [ExcelContent(...)]
/// </summary>
/// <param name="Value">值</param>
/// <returns>Excel样式，数据类型，文本</returns>
private static object[] ExcelContent(object Value)
{
object[] strValue = new object[3];
System.Type rowType = Value.GetType();
switch (rowType.ToString())
{
case "System.String":
case "System.Guid":
string XMLstring = Value.ToString();
XMLstring = XMLstring.Trim();
XMLstring = XMLstring.Replace("&", "&");
XMLstring = XMLstring.Replace(">", ">");
XMLstring = XMLstring.Replace("<", "<");
strValue[0] = "StringLiteral";
strValue[1] = "String";
strValue[2] = XMLstring;
break;
case "System.DateTime":
DateTime XMLDate = (DateTime)Value;
string XMLDatetoString = ""; //Excel Converted Date
//把日期时间转化为：“yyyy-MM-ddTHH:mm:ss”这种Excel中的格式
XMLDatetoString = XMLDate.ToString(System.Globalization.DateTimeFormatInfo.CurrentInfo.SortableDateTimePattern);
strValue[0] = "DateLiteral";
strValue[1] = "DateTime";
if (XMLDate < Convert.ToDateTime("1900-1-1"))
{
strValue[0] = "StringLiteral";
strValue[1] = "String";
XMLDatetoString = string.Empty;
}
strValue[2] = XMLDatetoString;
break;
case "System.Boolean":
strValue[0] = "StringLiteral";
strValue[1] = "String";
strValue[2] = Value.ToString();
break;
case "System.Int16":
case "System.Int32":
case "System.Int64":
case "System.Byte":
strValue[0] = "Integer";
strValue[1] = "Number";
strValue[2] = Value.ToString();
break;
case "System.Byte[]":
strValue[0] = "StringLiteral";
strValue[1] = "String";
strValue[2] = (byte[])Value;
break;
case "System.Decimal":
case "System.Double":
strValue[0] = "Decimal";
strValue[1] = "Number";
strValue[2] = Value.ToString();
break;
case "System.DBNull":
strValue[0] = "StringLiteral";
strValue[1] = "String";
strValue[2] = "";
break;
default:
throw (new Exception(rowType.ToString() + " not handled."));
}
return strValue;
}
#endregion
/// <summary>
/// 写入Excel文档
/// </summary>
/// <param name="Path">文件名称</param>
/// <param name="tableName">表名</param>
public static void SavetoExcel(string soursePath, DataTable dt, string tableName)
{
try
{
lock (obj)
{
string newPath = HttpContext.Current.Server.MapPath("./Excel/Temp");
if (MoveFile(soursePath, newPath))
{
newPath = newPath + "\\" + Path.GetFileName(soursePath);
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + newPath + ";" + "Extended Properties=Excel 8.0;";
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbCommand cmd = new OleDbCommand();
cmd.Connection = conn;
//Excel表的列数
int columns = dt.Columns.Count;
foreach (DataRow dr in dt.Rows)
{
StringBuilder strbSql = new StringBuilder("INSERT INTO [" + tableName + "$] VALUES(");
for (int i = 0; i < columns; i++)
{
strbSql.Append("'" + dr[i] + "',");
}
string sql = strbSql.ToString().Substring(0, strbSql.ToString().LastIndexOf(','));
sql += ")";
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
DownloadFile(newPath);
}
if (File.Exists(newPath))
{
File.Delete(newPath);
}
}
}
catch (System.Data.OleDb.OleDbException ex)
{
throw new ApplicationException("写入Excel发生错误：" + ex.Message);
}
}
#region 复制文件
/// <summary>
/// 复制文件
/// </summary>
/// <param name="Path"></param>
private static bool MoveFile(string soursePath,string newPath)
{
try
{
if (!Directory.Exists(newPath))
{
Directory.CreateDirectory(newPath);
}
if (File.Exists(soursePath))
{
File.Copy(soursePath, newPath+"http://www.cnblogs.com/zhangzt/admin/file://"+Path.GetFileName(soursePath/), true);
}
return true;
}catch(System.IO.FileNotFoundException ex)
{
throw new ApplicationException("复制文件发生错误：" + ex.Message);
}
}
#endregion
#region 提示下载
/// <summary>
/// 提示下载
/// </summary>
/// <param name="filePath">文件路径</param>
/// <param name="fileName">文件名称</param>
private static void DownloadFile(string filePath)
{
HttpResponse response = HttpContext.Current.Response;
response.ClearHeaders();
response.Clear();
response.Expires = 0;
response.Buffer = true;
response.AddHeader("Accept-Language", "zh-tw");
string name = System.IO.Path.GetFileName(filePath);
System.IO.FileStream files = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.Read);
byte[] byteFile = null;
if (files.Length == 0)
{
byteFile = new byte[1];
}
else
{
byteFile = new byte[files.Length];
}
files.Read(byteFile, 0, (int)byteFile.Length);
files.Close();
response.AddHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(name, System.Text.Encoding.UTF8));
response.ContentType = "application/octet-stream;charset=gbk";
response.BinaryWrite(byteFile);
response.End();
}
#endregion
}
}
