using System.Collections.Generic;
using System.Data;
using System.IO;
using System.Linq;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel; public class ExcelHelper
{
public class x2003
{
#region Excel2003
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xls)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLS(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook(fs);
ISheet sheet = hssfworkbook.GetSheetAt(); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = ; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLS(header.GetCell(i) as HSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + ; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLS(sheet.GetRow(i).GetCell(j) as HSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
} /// <summary>
/// 将DataTable数据导出到Excel文件中(xls)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLS(DataTable dt, string file)
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Test"); //表头
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //数据
for (int i = ; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
} //转为字节数组
MemoryStream stream = new MemoryStream();
hssfworkbook.Write(stream);
var buf = stream.ToArray(); //保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
} /// <summary>
/// 获取单元格类型(xls)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLS(HSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
} public class x2007
{
#region Excel2007
/// <summary>
/// 将Excel文件中的数据读出到DataTable中(xlsx)
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
public static DataTable ExcelToTableForXLSX(string file)
{
DataTable dt = new DataTable();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook(fs);
ISheet sheet = xssfworkbook.GetSheetAt(); //表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = ; i < header.LastCellNum; i++)
{
object obj = GetValueTypeForXLSX(header.GetCell(i) as XSSFCell);
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
//continue;
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + ; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
bool hasValue = false;
foreach (int j in columns)
{
dr[j] = GetValueTypeForXLSX(sheet.GetRow(i).GetCell(j) as XSSFCell);
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
} /// <summary>
/// 将DataTable数据导出到Excel文件中(xlsx)
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
public static void TableToExcelForXLSX(DataTable dt, string file)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
ISheet sheet = xssfworkbook.CreateSheet("Test"); //表头
IRow row = sheet.CreateRow();
for (int i = ; i < dt.Columns.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dt.Columns[i].ColumnName);
} //数据
for (int i = ; i < dt.Rows.Count; i++)
{
IRow row1 = sheet.CreateRow(i + );
for (int j = ; j < dt.Columns.Count; j++)
{
ICell cell = row1.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
} //转为字节数组
MemoryStream stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray(); //保存为Excel文件
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, , buf.Length);
fs.Flush();
}
} /// <summary>
/// 获取单元格类型(xlsx)
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueTypeForXLSX(XSSFCell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
return cell.NumericCellValue;
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
#endregion
} public static DataTable GetDataTable(string filepath)
{
var dt = new DataTable("xls");
if (filepath.Last()=='s')
{
dt = x2003.ExcelToTableForXLS(filepath);
}
else
{
dt = x2007.ExcelToTableForXLSX(filepath);
}
return dt;
}
}
using System;
using System.Collections.Generic;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Linq;
public partial class _Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{ }
protected void btn_read_03_click(object o, EventArgs e)
{
var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2003.xls"));
g1.DataSource = dt;
g1.DataBind();
}
protected void btn_read_07_click(object o, EventArgs e)
{
var dt = ExcelHelper.GetDataTable(Server.MapPath("~/xls_tmp/2007.xlsx"));
g1.DataSource = dt;
g1.DataBind();
}
protected void btn_import_03_click(object o, EventArgs e)
{
var name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next();
var path = Server.MapPath("~/xls_down/" + name + ".xls");
var dt = new System.Data.DataTable();
var Columns=Enumerable.Range(, ).Select(d => new DataColumn("a"+d.ToString(), typeof(string))).ToArray();
dt.Columns.AddRange(Columns);
for (int i = ; i < ; i++)
{
var id = Guid.NewGuid().ToString();
dt.Rows.Add(id, id, id, id, id, id, id, id, id, id);
}
ExcelHelper.x2003.TableToExcelForXLS(dt, path);
downloadfile(path);
}
protected void btn_import_07_click(object o, EventArgs e)
{
var name = DateTime.Now.ToString("yyyyMMddhhmmss") + new Random(DateTime.Now.Second).Next();
var path = Server.MapPath("~/xls_down/" + name + ".xlsx");
var dt = new System.Data.DataTable();
var Columns = Enumerable.Range(, ).Select(d => new DataColumn("a" + d.ToString(), typeof(string))).ToArray();
dt.Columns.AddRange(Columns);
for (int i = ; i < ; i++)
{
var id = Guid.NewGuid().ToString();
dt.Rows.Add(id, id, id, id, id, id, id, id, id, id);
}
ExcelHelper.x2007.TableToExcelForXLSX(dt, path);
downloadfile(path);
}
void downloadfile(string s_path)
{
System.IO.FileInfo file = new System.IO.FileInfo(s_path);
HttpContext.Current.Response.ContentType = "application/ms-download";
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.AddHeader("Content-Type", "application/octet-stream");
HttpContext.Current.Response.Charset = "utf-8";
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(file.Name, System.Text.Encoding.UTF8));
HttpContext.Current.Response.AddHeader("Content-Length", file.Length.ToString());
HttpContext.Current.Response.WriteFile(file.FullName);
HttpContext.Current.Response.Flush();
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.End();
}
}
如果要提高效能千萬切記，不要在內循環中亂用下面的設定，要不效能就是幾秒與幾分鐘的差異。
sheet.AutoSizeColumn(cellIndex, true);
ICellStyle style = workbook.CreateCellStyle();
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
cell.CellStyle = style;
NPOI 单元格格式设置 http://blog.csdn.net/lcnmdfx/article/details/38083887
NPOI自适应宽度不支持中文解决方案 http://blog.csdn.net/jerry_cool/article/details/7000085
http://download.csdn.net/detail/diaodiaop/7611721