Excel操作-优快云博客

usingSystem;
usingSystem.Collections.Generic;
usingSystem.Data;
usingSystem.IO;
usingSystem.Text;
usingSystem.Web;
usingNPOI;
usingNPOI.HPSF;
usingNPOI.HSSF;
usingNPOI.HSSF.UserModel;
usingNPOI.HSSF.Util;
usingNPOI.POIFS;
usingNPOI.Util;
usingNPOI.SS.UserModel;
usingNPOI.SS.Util;
namespaceXXXX.Common
{
///<summary>
///Excel操作
///</summary>
publicclassExcelHelper
{
///<summary>
///DataTable导出到Excel的MemoryStream
///</summary>
///<paramname="dtSource">源DataTable</param>
///<paramname="strHeaderText">表头文本</param>
publicstaticMemoryStreamGetExcelStream(DataTabledtSource,stringstrHeaderText)
{
HSSFWorkbookworkbook=newHSSFWorkbook();
ISheetsheet=workbook.CreateSheet();
#region右击文件属性信息
{
DocumentSummaryInformationdsi=PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company="";
workbook.DocumentSummaryInformation=dsi;
SummaryInformationsi=PropertySetFactory.CreateSummaryInformation();
si.Author="";//填加xls文件作者信息
si.ApplicationName="";//填加xls文件创建程序信息
si.LastAuthor="";//填加xls文件最后保存者信息
si.Comments="";//填加xls文件作者信息
si.Title="";//填加xls文件标题信息
si.Subject="";//填加文件主题信息
si.CreateDateTime=DateTime.Now;
workbook.SummaryInformation=si;
}
#endregion
ICellStyledateStyle=workbook.CreateCellStyle();
IDataFormatformat=workbook.CreateDataFormat();
dateStyle.DataFormat=format.GetFormat("yyyy-mm-dd");
//取得列宽
int[]arrColWidth=newint[dtSource.Columns.Count];
foreach(DataColumnitemindtSource.Columns)
{
arrColWidth[item.Ordinal]=Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for(inti=0;i<dtSource.Rows.Count;i++)
{
for(intj=0;j<dtSource.Columns.Count;j++)
{
intintTemp=Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if(intTemp>arrColWidth[j])
{
arrColWidth[j]=intTemp;
}
}
}
introwIndex=0;
foreach(DataRowrowindtSource.Rows)
{
#region新建表，填充表头，填充列头，样式
if(rowIndex==65535||rowIndex==0)
{
if(rowIndex!=0)
{
sheet=workbook.CreateSheet();
}
#region表头及样式
if(!string.IsNullOrEmpty(strHeaderText))
{
IRowheaderRow=sheet.CreateRow(0);
headerRow.HeightInPoints=25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyleheadStyle=workbook.CreateCellStyle();
headStyle.Alignment=HorizontalAlignment.CENTER;
IFontfont=workbook.CreateFont();
font.FontHeightInPoints=20;
font.Boldweight=700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle=headStyle;
sheet.AddMergedRegion(newCellRangeAddress(0,0,0,dtSource.Columns.Count-1));
}
#endregion
#region列头及样式
{
IRowheaderRow=sheet.CreateRow(1);
ICellStyleheadStyle=workbook.CreateCellStyle();
headStyle.Alignment=HorizontalAlignment.CENTER;
IFontfont=workbook.CreateFont();
font.FontHeightInPoints=10;
font.Boldweight=700;
headStyle.SetFont(font);
foreach(DataColumncolumnindtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle=headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal,(arrColWidth[column.Ordinal]+1)*256);
}
}
#endregion
rowIndex=2;
}
#endregion
#region填充内容
IRowdataRow=sheet.CreateRow(rowIndex);
foreach(DataColumncolumnindtSource.Columns)
{
ICellnewCell=dataRow.CreateCell(column.Ordinal);
stringdrValue=row[column].ToString();
switch(column.DataType.ToString())
{
case"System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case"System.DateTime"://日期类型
DateTimedateV;
DateTime.TryParse(drValue,outdateV);
newCell.SetCellValue(dateV);
newCell.CellStyle=dateStyle;//格式化显示
break;
case"System.Boolean"://布尔型
boolboolV=false;
bool.TryParse(drValue,outboolV);
newCell.SetCellValue(boolV);
break;
case"System.Int16"://整型
case"System.Int32":
case"System.Int64":
case"System.Byte":
intintV=0;
int.TryParse(drValue,outintV);
newCell.SetCellValue(intV);
break;
case"System.Decimal"://浮点型
case"System.Double":
doubledoubV=0;
double.TryParse(drValue,outdoubV);
newCell.SetCellValue(doubV);
break;
case"System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using(MemoryStreamms=newMemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position=0;
workbook.Dispose();
returnms;
}
}
///<summary>
///导出Excel
///</summary>
///<paramname="dtSource">源DataTable</param>
///<paramname="strHeaderText">表头文本</param>
///<paramname="strFileName">文件名</param>
publicstaticvoidExport(DataTabledtSource,stringstrHeaderText,stringstrFileName)
{
HttpContextcurContext=HttpContext.Current;
//设置编码和附件格式
curContext.Response.ContentType="application/vnd.ms-excel";
curContext.Response.ContentEncoding=Encoding.UTF8;
curContext.Response.Charset="UTF-8";
if(curContext.Request.ServerVariables["HTTP_USER_AGENT"].IndexOf("Firefox")>-1)
{
curContext.Response.AddHeader("Content-Disposition","attachment;FileName="+strFileName);
}
else
{
curContext.Response.AppendHeader("Content-Disposition","attachment;filename="+HttpUtility.UrlEncode(strFileName,Encoding.UTF8));
}
curContext.Response.BinaryWrite(GetExcelStream(dtSource,strHeaderText).GetBuffer());
curContext.Response.End();
}
///<summary>
///读取excel
///默认第一行为标头
///</summary>
///<paramname="strFileName">excel文档路径</param>
///<returns></returns>
publicstaticDataTableImport(stringstrFileName)
{
DataTabledt=newDataTable();
HSSFWorkbookhssfworkbook;
using(FileStreamfile=newFileStream(strFileName,FileMode.Open,FileAccess.Read))
{
hssfworkbook=newHSSFWorkbook(file);
}
ISheetsheet=hssfworkbook.GetSheetAt(0);
System.Collections.IEnumeratorrows=sheet.GetRowEnumerator();
IRowheaderRow=sheet.GetRow(0);
intcellCount=headerRow.LastCellNum;
for(intj=0;j<cellCount;j++)
{
ICellcell=headerRow.GetCell(j);
dt.Columns.Add(cell.ToString());
}
for(inti=(sheet.FirstRowNum+1);i<=sheet.LastRowNum;i++)
{
IRowrow=sheet.GetRow(i);
DataRowdataRow=dt.NewRow();
for(intj=row.FirstCellNum;j<cellCount;j++)
{
if(row.GetCell(j)!=null)
dataRow[j]=row.GetCell(j).ToString();
}
dt.Rows.Add(dataRow);
}
returndt;
}
}
}