1、#region Excel導入DataTable兼容2003-2012(請細心查看注釋)
/// <summary>
/// 讀取Excel文件到DataSet中
/// 注釋1:2012導出如報錯“ System.InvalidOperationException: 未在本地計算機上注冊“microsoft.ACE.oledb.12.0”提供程序。”解決:下載2007 Office system 驅動程序：數據連接組件安裝http://download.microsoft.com/download/7/0/3/703ffbcb-dc0c-4e19-b0da-1463960fdcdb/AccessDatabaseEngine.exe
/// </summary>
/// <param name="filePath">文件路徑</param>
/// <returns></returns>
public static DataSet ToDataTable(string filePath)
{
string connStr = "";
string fileType = System.IO.Path.GetExtension(filePath);
if (string.IsNullOrEmpty(fileType)) return null;
if (fileType == ".xls")
connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
else
connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";
string sql_F = "Select * FROM [{0}]";
//string sql_F = "Select * FROM [K_rt$A:K]";
OleDbConnection conn = null;
OleDbDataAdapter da = null;
DataTable dtSheetName = null;
DataSet ds = new DataSet();
try
{
// 初始化連接，並打開
conn = new OleDbConnection(connStr);
conn.Open();
// 獲取數據源的表定義元數據
string SheetName = "";
dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });
// 初始化適配器
da = new OleDbDataAdapter();
for (int i = 0; i < dtSheetName.Rows.Count; i++)
{
SheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
if (SheetName.Contains("$") && !SheetName.Replace("'", "").EndsWith("$"))
{
continue;
}
da.SelectCommand = new OleDbCommand(String.Format("SELECT * FROM [" + SheetName + "A:IU]", SheetName), conn);
DataSet dsItem = new DataSet();
da.Fill(dsItem, SheetName);
ds.Tables.Add(dsItem.Tables[0].Copy());
}
}
catch (Exception ex)
{
}
finally
{
// 關閉連接
if (conn.State == ConnectionState.Open)
{
conn.Close();
da.Dispose();
conn.Dispose();
}
}
return ds;
}
#endregion
2、#region DataTable導出Excel（導出格式2003.xls）注釋:需要引用NPOI
/// <summary>
/// DataTable導出Excel
/// </summary>
/// <param name="dt">DataTable</param>
public void private_export(DataTable dt)
{
NPOI.HSSF.UserModel.HSSFWorkbook book = new NPOI.HSSF.UserModel.HSSFWorkbook();
NPOI.SS.UserModel.ISheet sheet1 = book.CreateSheet("數據");
NPOI.SS.UserModel.IRow row1 = sheet1.CreateRow(0);
//列名
row1.CreateCell(0).SetCellValue("序號");
row1.CreateCell(1).SetCellValue("機型");
row1.CreateCell(2).SetCellValue("價格");
row1.CreateCell(3).SetCellValue("本月回收次數");
//循環DataTable中的字段
for (int i = 0; i < dt.Rows.Count; i++)
{
NPOI.SS.UserModel.IRow rowtemp = sheet1.CreateRow(i + 1);
rowtemp.CreateCell(0).SetCellValue(dt.Rows[i]["ID"].ToString());
rowtemp.CreateCell(1).SetCellValue(dt.Rows[i]["Model"].ToString());
rowtemp.CreateCell(2).SetCellValue(dt.Rows[i]["Price"].ToString());
rowtemp.CreateCell(3).SetCellValue(dt.Rows[i]["Count"].ToString());
}
for (int columnNum = 0; columnNum < sheet1.LastRowNum; columnNum++)
{
int columnWidth = sheet1.GetColumnWidth(columnNum) / 256;
for (int rowNum = 1; rowNum <= sheet1.LastRowNum; rowNum++)
{
IRow currentRow;
//當前行未被使用過
if (sheet1.GetRow(rowNum) == null)
{
currentRow = sheet1.CreateRow(rowNum);
}
else
{
currentRow = sheet1.GetRow(rowNum);
}
if (currentRow.GetCell(columnNum) != null)
{
ICell currentCell = currentRow.GetCell(columnNum);
int length = Encoding.Default.GetBytes(currentCell.ToString()).Length;
if (columnWidth < length)
{
columnWidth = length;
}
}
//格式
//ICellStyle style = book.CreateCellStyle();
//style.Alignment = HorizontalAlignment.Center;
//style.VerticalAlignment = VerticalAlignment.Center;
//sheet1.GetRow(columnNum).GetCell(rowNum).CellStyle = style;
}
if (columnWidth > 200)
{
sheet1.SetColumnWidth(columnNum, columnWidth * 156);
}
if (columnWidth < 200)
{
sheet1.SetColumnWidth(columnNum, columnWidth * 256);
}
}
string path = Server.MapPath(DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls");
using (FileStream fs = new FileStream(path, FileMode.Create))
{
book.Write(fs);
}
DownLoadFile(path);
}
/// <summary>
/// 導出後下載Excel文件
/// </summary>
/// <param name="fileURL">文件的路徑</param>
public void DownLoadFile(string fileURL)
{
//string fileURL = this.Server.MapPath("你要下載的文件路徑");//文件路徑，可用相對路徑
FileInfo fileInfo = new FileInfo(fileURL);
Response.Clear();
Response.AddHeader("content-disposition", "attachment;filename=" + Server.UrlEncode(fileInfo.Name.ToString()));
//文件名
Response.AddHeader("content-length", fileInfo.Length.ToString());
//文件大小
//lbtnPriceFiltrate.Enabled=
Response.ContentType = "application/octet-stream";
Response.ContentEncoding = System.Text.Encoding.Default; Response.WriteFile(fileURL);
}
#endregion
