关于NPOI
NPOI是POI项目的.NET版本，是由@Tony Qu(http://tonyqus.cnblogs.com/)等大侠基于POI开发的，可以从http://npoi.codeplex.com/下载到它的最新版本。它不使用Office COM组件(Microsoft.Office.Interop.XXX.dll)，不需要安装Microsoft Office，支持对Office 97-2003的文件格式，功能比较强大。更详细的说明请看官方网站。
它的以下一些特性让我相当喜欢：
- 支持对标准的Excel读写
- 支持对流(Stream)的读写 (而Jet OLEDB和Office COM都只能针对文件)
- 支持大部分Office COM组件的常用功能
- 性能优异
- 使用简单，易上手
使用NPOI导入
需要的引用：
using System.IO;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
需要注意的是，sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1，这里可能存在BUG：当没有数据或只有一行数据时sheet.LastRowNum为0，PhysicalNumberOfRows 表现正常。
这里读取流中的Excel来创建Workbook对象，并转换成DataTable：
#region 使用NPOI导入标准Excel
//需要注意的是，sheet.LastRowNum = sheet.PhysicalNumberOfRows - 1，这里可能存在BUG：当没有数据或只有一行数据时sheet.LastRowNum为0，PhysicalNumberOfRows 表现正常。
//这里读取流中的Excel来创建Workbook对象，并转换成DataTable
/// <param name="excelFile">Excel的物理路径</param>
public static DataTable RenderFromExcel(string excelFile)
{
using (FileStream stream = System.IO.File.OpenRead(excelFile))
{
return RenderFromExcel(stream);
}
}
public static DataTable RenderFromExcel(Stream excelFileStream)
{ IWorkbook workbook = WorkbookFactory.Create(excelFileStream);
ISheet sheet = workbook.GetSheetAt();
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow();//第一行为标题行
int cellCount = headerRow.LastCellNum;//LastCellNum = PhysicalNumberOfCells
int rowCount = sheet.LastRowNum;//LastRowNum = PhysicalNumberOfRows - 1
//handling header.
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).ToString());
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + ); i <= rowCount; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric)
{
// 判断单元格的值是否为日期，避免日期的年份变两位
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
dataRow[j] = row.GetCell(j).DateCellValue;
else
dataRow[j] = row.GetCell(j).NumericCellValue;
}
else
dataRow[j] = row.GetCell(j);
}
else
{
dataRow[j] = "";
}
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
#endregion
控制器读取DataTable中的值：
string excelfilePath=this.Server.MapPath(filePath);
DataTable dt = EIHelper.RenderFromExcel(excelfilePath); ///学号，性别，姓名为 DataTable中的Columns值（Columns值是什么下面对应的就是什么）
foreach (DataRow row in dt.Rows)
{
var aa = row["学号"];
var bb = row["姓名"];
var cc = row["性别"];
}
使用NPOI导出
视图代码：
//导出Excel
function btn_derive() {
var KeyValue = GetJqGridRowValue("#gridTable", "id");
if (typeof (KeyValue) == "undefined") {
KeyValue = "";
}
SetDeriveExcel("#gridTable", "信息");
openDialog("/School/Students/ExportExcel?KeyValue=" + KeyValue, "ExportExcel", "学生导出", 620, 300, function (iframe) {
top.frames[iframe].AcceptClick();
});
}
@using System.Collections;
@{
ViewBag.Title = "ExportExcel";
Layout = "~/Views/Shared/_LayoutIndex.cshtml";
}
<script>
$(function () {
//getAjax("/Utility/GetDeriveExcelColumn", {}, function (data) {
// var JsonData = eval("(" + data + ")");
// $.each(JsonData, function (i) {
// var label = JsonData[i].label;
// var name = JsonData[i].name;
// var hidden = JsonData[i].hidden;
// if (!!label && hidden == false) {
// $("#AccessView").append("<li title='" + label + "'><a id='" + name + "'><img src=\"../../Content/Images/Icon16/tag_blue.png\">" + label + "</a><i></i></li>");
// }
// });
//});
$("#AccessView li").click(function () {
if (!$(this).find('a').hasClass('disabled')) {
if (!!$(this).hasClass("selected")) {
$(this).removeClass("selected");
} else {
$(this).addClass("selected");
}
}
});
//自定义复选框 全选/反选
$("#CheckView").click(function () {
if (!!$(this).hasClass("checkAllOff")) {
$(this).attr('title', '反选');
$(this).text('反选');
$(this).attr('class', 'checkAllOn');
$('#AccessView li').addClass('selected');
} else {
$(this).attr('title', '全选');
$(this).text('全选');
$(this).attr('class', 'checkAllOff');
$('#AccessView li').removeClass('selected');
}
})
$("#CheckView").trigger("click");
})
//确认导出
function AcceptClick() {
var KeyValue = $("#RowValue").val();
var SelectedField = ""; $('#AccessView .selected a').each(function () { SelectedField += $(this).attr('lable') + ":" + $(this).attr('id') + ","; });
Loading(true, "正在处理要导出数据...");
window.setTimeout(function () {
location.href = "/School/Students/GetExportExcel?ExportField=" + escape(SelectedField) + "&KeyValue=" + KeyValue;
Loading(false);
}, 200);
}
</script>
<div class="note-prompt" style="margin: 1px;">
温馨提示：选中复选框即可导出相应字段，取消选中则回收相应字段。
</div>
<div class="border" style="margin: 1px;">
<div class="btnbartitle">
<div style="float: left">
系统视图 <span class="ModuleName"></span>
</div>
<div style="float: right">
<label id="CheckView" class="checkAllOff" title="全选">全选</label>
</div>
</div>
<div style="height: 225px; overflow: auto;">
<ul id="AccessView" class="sys_spec_text">
@foreach (DictionaryEntry item in (ViewBag.FieldsValues as Hashtable))
{
<li title="@item.Value">
<a id="@item.Key" lable="@item.Value"><img src="/Content/Images/Icon16/tag_blue.png">@item.Value</a><i></i></li>
}
</ul>
<input id="RowValue" type="hidden" value="@ViewBag.RowValue" />
</div>
</div>
控制器代码：
/// <summary>
/// 导出Excel窗口，供选择要导出的列
/// </summary>
/// <returns></returns>
public ActionResult ExportExcel(string KeyValue)
{
////根据实体类获取表的所有列
//Hashtable ht = HashtableHelper.GetModelToHashtable(new Students());
//ht.Remove("Password");
//ht.Remove("Portrait");
//ht.Remove("Token");
//ht.Remove("AccountStatus");
//ht.Remove("LoginStatus");
//ht.Remove("GraduateProvinceId");
//ht.Remove("GraduateCityId");
//ht.Remove("Family1Title");
//ht.Remove("Family1Name");
//ht.Remove("Family1Age");
//ht.Remove("Family1Politic");
//ht.Remove("Family1Company");
//ht.Remove("Family1Job");
//ht.Remove("Family1CompanyAddress");
//ht.Remove("Family1Postcode");
//ht.Remove("Family1TelephoneNO");
//ht.Remove("Family1RestDay");
//ht.Remove("Family2Title");
//ht.Remove("Family2Name");
//ht.Remove("Family2Age");
//ht.Remove("Family2Politic");
//ht.Remove("Family2Company");
//ht.Remove("Family2Job");
//ht.Remove("Family2CompanyAddress");
//ht.Remove("Family2Postcode");
//ht.Remove("Family2TelephoneNO");
//ht.Remove("Family2RestDay");
//ht.Remove("Family3Title");
//ht.Remove("Family3Name");
//ht.Remove("Family3Age");
//ht.Remove("Family3Politic");
//ht.Remove("Family3Company");
//ht.Remove("Family3Job");
//ht.Remove("Family3CompanyAddress");
//ht.Remove("Family3Postcode");
//ht.Remove("Family3TelephoneNO");
//ht.Remove("Family3RestDay");
//ht.Remove("CreateUserId");
//ht.Remove("ModifyUserId");
//ArrayList akeys = new ArrayList(ht.Keys);
//ViewBag.Fields = akeys;
////获取选择的行
//ViewBag.RowValue = KeyValue; Hashtable ht = new Hashtable();
ht.Add("Name", "姓名");
ht.Add("Gender", "性别");
ht.Add("SchoolName", "学校");
ht.Add("ClassStudentCode", "学号");
ht.Add("Telephone", "手机号");
ht.Add("Email", "邮箱");
ArrayList akeys = new ArrayList(ht.Keys);
ArrayList avalues = new ArrayList(ht.Values);
//ViewBag.Fields = akeys;
ViewBag.FieldsValues = ht;
//获取选择的行
ViewBag.RowValue = KeyValue;
return View();
} /// <summary>
/// 获取导出的Excel文件
/// </summary>
/// <param name="ExportField">要导出的列</param>
/// <param name="KeyValue">要导出的数据行（自行选择的）</param>
/// <returns></returns>
public ActionResult GetExportExcel(string ExportField, string KeyValue)
{
try
{
string[] dataColumn = StringHelper.GetStrArray(StringHelper.DelLastComma(ExportField));
string xlsName = DateTime.Now.ToShortDateString() + "学生列表.xls";
ExportImportHelper.ExportXlsByWeb(studentsbll.GetTable(KeyValue), xlsName, dataColumn, "Sheet1");
return Json(new { Success = true, Code = "", Message = "导出成功！" }, JsonRequestBehavior.AllowGet);
}
catch (Exception ex)
{
return Json(new { Success = false, Code = "-1", Message = "导出失败！" + ex.Message }, JsonRequestBehavior.AllowGet);
}
}
逻辑层Gettable方法：
/// <summary>
/// 查询所包含的关键字的值的数据
/// </summary>
/// <param name="KeyValue">形如："abc,ddd,222,af"的数据</param>
/// <returns></returns>
public DataTable GetTable(string KeyValue)
{
StringBuilder strSql = new StringBuilder();
strSql.Append(@"Select*From Students Where 1=1");
if (!string.IsNullOrEmpty(KeyValue))
{
//string userid = StringHelper.GetSingleMark(KeyValue); //userid太长传递，或者效率太低，又加个自动增加的Int Id
strSql.Append(@" And Id in (" + KeyValue + ")");
}
return Repository().FindTableBySql(strSql.ToString());
}
使用NPOI导出PDF文件，导出Docx文件，导出Xls文件（方法整理）
using System;
using System.Text;
using System.Data;
using NPOI.HSSF.UserModel;
using System.IO;
using NPOI.XWPF.UserModel;
using NPOI.XSSF.UserModel;
using iTextSharp.text;
using iTextSharp.text.pdf;
using NPOI.SS.UserModel;
using System.Web;
using NPOI.SS.Util; namespace Dw.Utilities
{
public class ExportImportHelper
{
#region 导出PDF文件
/// <summary>
/// 导出PDF文件
/// </summary>
/// <param name="localFilePath">文件保存路径</param>
/// <param name="dtSource">数据源</param>
/// <param name="HorV">页面横竖（为空表示竖，有非空值为横）</param>
public static void ExportPDF(string localFilePath, DataTable dtSource, string HorV)
{
iTextSharp.text.io.StreamUtil.AddToResourceSearch("iTextAsian.dll");
iTextSharp.text.io.StreamUtil.AddToResourceSearch("iTextAsianCmaps.dll"); BaseFont bf;
string basepath = System.Web.HttpContext.Current.Server.MapPath("\\Themes\\fonts\\STSONG.TTF");
try
{
bf = BaseFont.CreateFont(basepath, BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
}
catch
{
bf = BaseFont.CreateFont("C:\\WINDOWS\\Fonts\\STSONG.TTF", BaseFont.IDENTITY_H, BaseFont.NOT_EMBEDDED);
}
Font font = new Font(bf);
iTextSharp.text.Document pdf;
if (string.IsNullOrEmpty(HorV))
pdf = new iTextSharp.text.Document();
else
pdf = new iTextSharp.text.Document(PageSize.A4.Rotate()); PdfPTable table = new PdfPTable(dtSource.Columns.Count);
table.HorizontalAlignment = Element.ALIGN_CENTER;
PdfPCell cell;
for (int i = ; i < dtSource.Rows.Count + ; i++)
{
for (int j = ; j < dtSource.Columns.Count; j++)
{
if (i == )
cell = new PdfPCell(new Phrase(dtSource.Columns[j].ColumnName, font));
else
cell = new PdfPCell(new Phrase(dtSource.Rows[i - ][j].ToString(), font));
table.AddCell(cell);
}
}
using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
PdfWriter.GetInstance(pdf, fs);
pdf.Open();
pdf.Add(table);
pdf.Close();
}
}
#endregion #region DataTable导出Docx
/// <summary>
/// 导出Docx
/// </summary>
/// <param name="localFilePath">文件保存路径</param>
/// <param name="dtSource">数据源</param>
public static void ExportDocx(string localFilePath, DataTable dtSource)
{ XWPFDocument doc = new XWPFDocument(); XWPFTable table = doc.CreateTable(dtSource.Rows.Count + , dtSource.Columns.Count); for (int i = ; i < dtSource.Rows.Count + ; i++)
{
for (int j = ; j < dtSource.Columns.Count; j++)
{
if (i == )
{
table.GetRow(i).GetCell(j).SetText(dtSource.Columns[j].ColumnName);
}
else
{
table.GetRow(i).GetCell(j).SetText(dtSource.Rows[i - ][j].ToString());
}
}
} using (FileStream fs = new FileStream(localFilePath, FileMode.Create, FileAccess.Write))
{
doc.Write(fs);
}
}
#endregion #region DataTable导出Xls
/// <summary>
/// 由DataTable导出Xls，用于Web系统。james.wang 2015-12-31夜
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="fileName">指定Excel工作表名称</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
public static void ExportXlsByWeb(DataTable sourceTable, string fileName, string[] dataColumn, string sheetName)
{
MemoryStream ms = StreamFunction(sourceTable, dataColumn, sheetName) as MemoryStream;
if (HttpContext.Current.Request.UserAgent.ToLower().Contains("msie"))
{
fileName = HttpUtility.UrlEncode(fileName);
}
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
private static Stream StreamFunction(DataTable dtSource, string[] dataColumn, string sheetName)
{
IWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow();
//格式化日期样式
var dateStyle = workbook.CreateCellStyle();
dateStyle.Alignment = HorizontalAlignment.Left;
dateStyle.VerticalAlignment = VerticalAlignment.Center;
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding().GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = ; i < dtSource.Rows.Count; i++)
{
for (int j = ; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding().GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
} int rowIndex = ;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充列头，样式
if (rowIndex == || rowIndex == )
{
if (rowIndex != )
{
sheet = (XSSFSheet)workbook.CreateSheet();
}
#region 列头及样式
HSSFCellStyle headStyle = (HSSFCellStyle)workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
HSSFFont font = (HSSFFont)workbook.CreateFont();
font.FontHeightInPoints = ;
font.Boldweight = ;
headStyle.SetFont(font); int k = ;
foreach (DataColumn column in dtSource.Columns)
{
//只导出需要的列列头
foreach (string item in dataColumn)
{
string[] stritem = item.Split(':');
if (stritem[].ToLower() == column.ColumnName.ToLower())
{
headerRow.CreateCell(k).SetCellValue(stritem[]);
headerRow.GetCell(k).CellStyle = headStyle;
//设置列宽,因为Excel单元格宽度不能超过255个字符，所以这里要判断一下，不然会出错
var colWidth = (arrColWidth[column.Ordinal] + ) * ;
if (colWidth >= )
colWidth = ;
sheet.SetColumnWidth(k, colWidth);
k++;
}
}
}
#endregion
rowIndex = ;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
var style = workbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center; //垂直居中
int g = ;
foreach (DataColumn column in dtSource.Columns)
{
//只导出需要的列值
foreach (string item in dataColumn)
{
string[] stritem = item.Split(':');
if (stritem[].ToLower() == column.ColumnName.ToLower())
{
var newCell = dataRow.CreateCell(g);
string drValue = row[column].ToString(); switch (column.DataType.ToString())
{
case "System.String"://字符串类型
//接入图片
string[] picArr = drValue.Split('.');
string picType = (picArr[picArr.Length - ]).ToLower();
if (picType == "jpg" || picType == "png" || picType == "gif")
{ string picpath = HttpContext.Current.Server.MapPath(drValue);
if (File.Exists(picpath))
{
dataRow.HeightInPoints = ; //设置行高度
sheet.SetColumnWidth(g, *); //设置宽度
byte[] bytes = System.IO.File.ReadAllBytes(picpath);
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
// Create the drawing patriarch. This is the top level container for all shapes.
var getsheet = workbook.GetSheet(sheetName);
var patriarch = getsheet.CreateDrawingPatriarch();
//add a picture
var anchor = new HSSFClientAnchor(, , , , g, rowIndex, g, rowIndex);
var pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(1.0); }
}
else
{
newCell.SetCellValue(drValue);
newCell.CellStyle = style; } break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = ;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = ;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
g++;
} }
}
#endregion
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = ;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}
/// <summary>
/// WinForm使用DataTable导出到Excel文件 Excel 2003
/// </summary>
/// <param name="sourceDs">源DataTable</param>
/// <param name="fileName">路径</param>
/// <param name="sheetName">指定Excel工作表名称(多个用,号隔开)</param>
/// <returns></returns>
public static void ExportXlsByForm(DataTable sourceDs, string fileName, string[] dataColumn, string sheetName)
{
using (MemoryStream ms = StreamFunction(sourceDs, dataColumn, sheetName) as MemoryStream)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, , data.Length);
fs.Flush();
}
}
}
#endregion #region DataTable导出Excel2007
/// <summary>
/// 由DataTable导出Excel2007
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
private static Stream ExportDataTableToExcel2007(DataTable sourceTable, string sheetName)
{
IWorkbook workbook = new XSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet(sheetName);
IRow headerRow = sheet.CreateRow();
// handling header.
foreach (DataColumn column in sourceTable.Columns)
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
// handling value.
int rowIndex = ;
foreach (DataRow row in sourceTable.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
//ms.Position = 0;
sheet = null;
headerRow = null;
workbook = null;
return ms;
}
/// <summary>
/// DataTable导出到Excel文件 Excel 2007
/// </summary>
/// <param name="sourceDs">源DataTable</param>
/// <param name="fileName">路径</param>
/// <param name="sheetName">指定Excel工作表名称(多个用,号隔开)</param>
/// <returns></returns>
public static void ExportDataTableToExcel2007(DataTable sourceDs, string fileName, string sheetName)
{
using (MemoryStream ms = ExportDataTableToExcel2007(sourceDs, sheetName) as MemoryStream)
{
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, , data.Length);
fs.Flush();
}
}
}
/// <summary>
/// web由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="fileName">指定Excel工作表名称</param>
/// <param name="sheetName">工作表名称</param>
/// <returns>Excel工作表</returns>
public static void ExportByWebDataTableToExcel2007(DataTable sourceTable, string fileName, string sheetName)
{
MemoryStream ms = ExportDataTableToExcel2007(sourceTable, sheetName) as MemoryStream;
if (HttpContext.Current.Request.UserAgent.ToLower().Contains("msie"))
{
fileName = HttpUtility.UrlEncode(fileName);
}
HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
HttpContext.Current.Response.End();
ms.Close();
ms = null;
}
#endregion #region 导出带有模板的Xls (可以一个Excel里有多个sheet表)
private static void ToCreateCell(string FieldName,string columnName,HSSFWorkbook workbook, HSSFSheet sheet,HSSFCellStyle cellstyle,string colItem, string cellValue,string dataType)
{
if (FieldName.ToLower() == columnName.ToLower())
{
var strspe = colItem.Split(':');
int firstRow = int.Parse(strspe[]);
int firstCol = int.Parse(strspe[]);
HSSFCell cell = (HSSFCell)sheet.GetRow(firstRow).CreateCell(firstCol);
//插入图片
string[] picArr = cellValue.Split('.');
string picType = (picArr[picArr.Length - ]).ToLower();
if (dataType == "System.String" && (picType == "jpg" || picType == "png" || picType == "gif"))
{
string picpath = HttpContext.Current.Server.MapPath(cellValue);
if (File.Exists(picpath))
{
//sheet.CreateRow(firstRow).HeightInPoints = 40; //设置行高度
//sheet.SetColumnWidth(firstCol, 7 * 256); //设置宽度
byte[] bytes = File.ReadAllBytes(picpath);
int pictureIdx = workbook.AddPicture(bytes, NPOI.SS.UserModel.PictureType.JPEG);
var patriarch = sheet.CreateDrawingPatriarch();
var anchor = new HSSFClientAnchor(, , , , firstCol, firstRow, int.Parse(strspe[]), int.Parse(strspe[]));
var pict = patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(1.0); }
}
else
{
cell.SetCellValue(cellValue.Replace("§", " / "));
cell.CellStyle = cellstyle;
cell.CellStyle.Alignment = HorizontalAlignment.Left;
cell.CellStyle.VerticalAlignment = VerticalAlignment.Center;
cell.CellStyle.BorderBottom = BorderStyle.Thin;
} } }
/// <summary>
/// 导出带有模板的Xls(可以一个Excel里有多个sheet表) james2016-1-2 8:17
/// </summary>
/// <param name="templateFilePath"></param>
/// <param name="exportFilePath"></param>
/// <param name="keyValue"></param>
public static void ExportTempleteXls(string templateFilePath,string exportFilePath,DataTable sourceTable, string[] columnData)
{
HSSFWorkbook workbook=new HSSFWorkbook();
using(FileStream file=new FileStream(templateFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(file);
file.Close();
}
//获取指定的Sheet工作表名
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt();
HSSFCellStyle cellstyle = (HSSFCellStyle)workbook.CreateCellStyle();
///////////////////////////////////////////////////////
foreach (DataRow row in sourceTable.Rows)
{
foreach (DataColumn column in sourceTable.Columns)
{
foreach(var item in columnData)
{
var strspe = item.Split(':');
ToCreateCell(strspe[], column.ColumnName, workbook, sheet, cellstyle, item, row[column].ToString(), column.DataType.ToString());
}
}
}
///////////////////////////////////////////////////////
//创建文件
FileStream files = new FileStream(exportFilePath, FileMode.Create);
workbook.Write(files);
files.Close();
FileInfo filet = new FileInfo(exportFilePath);
HttpContext.Current.Response.Clear();
HttpContext.Current.Response.Charset = "GB2312";
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8;
// 添加头信息，为"文件下载/另存为"对话框指定默认文件名
HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + HttpContext.Current.Server.UrlEncode("55.xls"));
//HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
// 添加头信息，指定文件大小，让浏览器能够显示下载进度
HttpContext.Current.Response.AddHeader("Content-Length", filet.Length.ToString());
// 指定返回的是一个不能被客户端读取的流，必须被下载
HttpContext.Current.Response.ContentType = "application/ms-excel";
// 把文件流发送到客户端
HttpContext.Current.Response.WriteFile(filet.FullName);
// 停止页面的执行
HttpContext.Current.Response.End();
}
#endregion #region Excel导入DataTable
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(Stream excelFileStream, string sheetName, int headerRowIndex)
{
IWorkbook workbook = WorkbookFactory.Create(excelFileStream);
ISheet sheet = workbook.GetSheet(sheetName);
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric)
{
// 判断单元格的值是否为日期，避免日期的年份变两位
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
dataRow[j] = row.GetCell(j).DateCellValue;
else
dataRow[j] = row.GetCell(j).NumericCellValue;
}
else
dataRow[j] = row.GetCell(j);
}
else
{
dataRow[j] = "";
}
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表名称</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(string excelFilePath, string sheetName, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataTableFromExcel(stream, sheetName, headerRowIndex);
}
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="sheetName">Excel工作表索引</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(Stream excelFileStream, int sheetIndex, int headerRowIndex)
{
IWorkbook workbook = WorkbookFactory.Create(excelFileStream);
ISheet sheet = workbook.GetSheetAt(sheetIndex);
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i + ;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null || row.GetCell() == null || row.GetCell().ToString().Trim() == "")
{
// 如果遇到第一个空行，则不再继续向后读取
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric)
{
// 判断单元格的值是否为日期，避免日期的年份变两位
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
dataRow[j] = row.GetCell(j).DateCellValue;
else
dataRow[j] = row.GetCell(j).NumericCellValue;
}
else
dataRow[j] = row.GetCell(j);
}
else
{
dataRow[j] = "";
}
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 由Excel导入DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="sheetName">Excel工作表索引</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataTable</returns>
public static DataTable ImportDataTableFromExcel(string excelFilePath, int sheetIndex, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataTableFromExcel(stream, sheetIndex, headerRowIndex);
}
}
#endregion #region Excel导入DataSet
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFileStream">Excel文件流</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(Stream excelFileStream, int headerRowIndex)
{
DataSet ds = new DataSet();
IWorkbook workbook = WorkbookFactory.Create(excelFileStream);
for (int a = , b = workbook.NumberOfSheets; a < b; a++)
{
ISheet sheet = workbook.GetSheetAt(a);
DataTable table = new DataTable();
IRow headerRow = sheet.GetRow(headerRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
if (headerRow.GetCell(i) == null || headerRow.GetCell(i).StringCellValue.Trim() == "")
{
// 如果遇到第一个空列，则不再继续向后读取
cellCount = i + ;
break;
}
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = (sheet.FirstRowNum + ); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
if (row == null || row.GetCell() == null || row.GetCell().ToString().Trim() == "")
{
// 如果遇到第一个空行，则不再继续向后读取
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric)
{
// 判断单元格的值是否为日期，避免日期的年份变两位
if (DateUtil.IsCellDateFormatted(row.GetCell(j)))
dataRow[j] = row.GetCell(j).DateCellValue;
else
dataRow[j] = row.GetCell(j).NumericCellValue;
}
else
dataRow[j] = row.GetCell(j);
}
else
{
dataRow[j] = "";
}
}
table.Rows.Add(dataRow);
}
ds.Tables.Add(table);
}
excelFileStream.Close();
workbook = null;
return ds;
}
/// <summary>
/// 由Excel导入DataSet，如果有多个工作表，则导入多个DataTable
/// </summary>
/// <param name="excelFilePath">Excel文件路径，为物理路径。</param>
/// <param name="headerRowIndex">Excel表头行索引</param>
/// <returns>DataSet</returns>
public static DataSet ImportDataSetFromExcel(string excelFilePath, int headerRowIndex)
{
using (FileStream stream = System.IO.File.OpenRead(excelFilePath))
{
return ImportDataSetFromExcel(stream, headerRowIndex);
}
}
#endregion #region 导入补充
// 另外为导入补充一点知识：
// 1、将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
//代码 /// <summary>
/// 将Excel的列索引转换为列名，列索引从0开始，列名从A开始。如第0列为A，第1列为B...
/// </summary>
/// <param name="index">列索引</param>
/// <returns>列名，如第0列为A，第1列为B...</returns>
public static string ConvertColumnIndexToColumnName(int index)
{
index = index + ;
int system = ;
char[] digArray = new char[];
int i = ;
while (index > )
{
int mod = index % system;
if (mod == ) mod = system;
digArray[i++] = (char)(mod - + 'A');
index = (index - ) / ;
}
StringBuilder sb = new StringBuilder(i);
for (int j = i - ; j >= ; j--)
{
sb.Append(digArray[j]);
}
return sb.ToString();
} // 2、当从Excel获取年月日时，会存在一定的问题，应该在一下代码中，可以想到存在的问题，所以我们可以写个方法封装一下：
/// <summary>
/// 转化日期
/// </summary>
/// <param name="date">日期</param>
/// <returns></returns>
public static DateTime ConvertDate(string date)
{
DateTime dt = new DateTime();
string[] time = date.Split('-');
int year = Convert.ToInt32(time[]);
int month = Convert.ToInt32(time[]);
int day = Convert.ToInt32(time[]);
string years = Convert.ToString(year);
string months = Convert.ToString(month);
string days = Convert.ToString(day);
if (months.Length == )
{
dt = Convert.ToDateTime(date);
}
else
{
string rq = "";
if (years.Length == )
{
years = "" + years;
}
if (months.Length == )
{
months = "" + months;
}
if (days.Length == )
{
days = "" + days;
}
rq = "" + years + "-" + months + "-" + days;
dt = Convert.ToDateTime(rq);
}
return dt;
}
#endregion }
}