ExcelHelper

前段时间写了几个关于导入导出的功能，现在空闲下来了，把主要代码块贴出来。

感觉没有什么好讲的，相信很多地方都能看到类似的源码。

只是用习惯了，会比较方便，所以贴出来，也方便以后查看。

以后遇到这样可以重复利用的代码块，会多整理的。(#^.^#)

/// <summary>
/// 导入excel生成DataTable
/// </summary>
/// <param name="inputStream">Excel文件流</param>
/// <param name="extension">后缀名</param>
/// <returns></returns>
public static DataTable BuildTable(Stream inputStream, string extension)
{
	DataTable dt = new DataTable();

	try
	{
		IWorkbook workbook = null;
		ISheet sheet = null;
		if (Regex.IsMatch(extension, ".*xls$"))
		{
			workbook = new HSSFWorkbook(inputStream);
			sheet = workbook.GetSheetAt(0);
		}
		else
		{
			workbook = new XSSFWorkbook(inputStream);
			sheet = workbook.GetSheetAt(0);
		}

		IRow headerRow = sheet.GetRow(0);
		int cellCount = headerRow.LastCellNum;

		for (int j = 0; j < cellCount; j++)
		{
			ICell cell = headerRow.GetCell(j);
			dt.Columns.Add(cell.ToString());
		}

		for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
		{
			IRow row = sheet.GetRow(i);
			DataRow dataRow = dt.NewRow();

			for (int j = row.FirstCellNum; j < cellCount; j++)
			{
				var cell = row.GetCell(j);
				if (cell != null)
				{
					//用于转化为日期格式
					if (cell.CellType == CellType.Numeric && DateUtil.IsCellDateFormatted(cell))
					{
						dataRow[j] = cell.DateCellValue.ToString();
					}
					else
					{
						dataRow[j] = cell.ToString();
					}
				}
			}

			dt.Rows.Add(dataRow);
		}
	}
	catch (Exception ex)
	{
		// ignored
	}
	return dt;
}

/// <summary>
/// Web导出
/// </summary>
/// <param name="workbook"></param>
/// <param name="strFileName"></param>
public static void ExportByWeb(HSSFWorkbook workbook, string strFileName)
{
	HttpContext curContext = HttpContext.Current;

	// 设置编码和附件格式
	curContext.Response.ContentType = "application/vnd.ms-excel";
	curContext.Response.ContentEncoding = Encoding.UTF8;
	curContext.Response.Charset = "";
	curContext.Response.AppendHeader("Content-Disposition",
		"attachment;filename=" + HttpUtility.UrlEncode(strFileName + ".xls", Encoding.UTF8));

	//获取流
	MemoryStream ms = CovertToStream(workbook);
	//输出
	curContext.Response.BinaryWrite(ms.GetBuffer());
	curContext.Response.End();
}

/// <summary>
/// 转化流
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
private static MemoryStream CovertToStream(HSSFWorkbook workbook)
{
	using (MemoryStream ms = new MemoryStream())
	{
		workbook.Write(ms);
		ms.Flush();
		ms.Position = 0;

		//sheet.Dispose()
		//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
		return ms;
	}
}

/// <summary>
/// 转化为Excel工作本
/// </summary>
/// <param name="dtSource"></param>
/// <param name="strHeaderText"></param>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <returns></returns>
public static HSSFWorkbook DateTaleCovertToWorkBook(DataTable dtSource, string strHeaderText, HSSFWorkbook workbook, ISheet sheet)
{
	#region 右击文件 属性信息
	{
		DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
		dsi.Company = "NPOI";
		workbook.DocumentSummaryInformation = dsi;

		SummaryInformation si = 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

	ICellStyle dateStyle = workbook.CreateCellStyle();
	IDataFormat 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(936).GetBytes(item.ColumnName.ToString()).Length;
	}
	for (int i = 0; i < dtSource.Rows.Count; i++)
	{
		for (int j = 0; j < dtSource.Columns.Count; j++)
		{
			int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
			if (intTemp > arrColWidth[j])
			{
				arrColWidth[j] = intTemp;
			}
		}
	}
	int rowIndex = 0;
	foreach (DataRow row in dtSource.Rows)
	{
		#region 新建表，填充表头，填充列头，样式
		if (rowIndex == 65535 || rowIndex == 0)
		{
			if (rowIndex != 0)
			{
				sheet = workbook.CreateSheet();
			}

			#region 列头及样式
			{
				IRow headerRow = sheet.CreateRow(0);
				ICellStyle headStyle = workbook.CreateCellStyle();
				headStyle.Alignment = HorizontalAlignment.Left;
				headStyle.WrapText = true;

				IFont font = workbook.CreateFont();
				font.FontHeightInPoints = 10;
				//font.Boldweight = 700;
				headStyle.SetFont(font);
				foreach (DataColumn column in dtSource.Columns)
				{
					headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
					headerRow.GetCell(column.Ordinal).CellStyle = headStyle;

					//设置列宽
					sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
				}
				//headerRow.Dispose();
			}
			#endregion

			rowIndex = 1;
		}
		#endregion


		#region 填充内容
		IRow dataRow = sheet.CreateRow(rowIndex);
		foreach (DataColumn column in dtSource.Columns)
		{
			ICell newCell = dataRow.CreateCell(column.Ordinal);

			string drValue = row[column].ToString();

			switch (column.DataType.ToString())
			{
				case "System.String"://字符串类型
					newCell.SetCellValue(drValue);
					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 = 0;
					int.TryParse(drValue, out intV);
					newCell.SetCellValue(intV);
					break;
				case "System.Decimal"://浮点型
				case "System.Double":
					double doubV = 0;
					double.TryParse(drValue, out doubV);
					newCell.SetCellValue(doubV);
					break;
				case "System.DBNull"://空值处理
					newCell.SetCellValue("");
					break;
				default:
					newCell.SetCellValue("");
					break;
			}

		}
		#endregion

		rowIndex++;
	}

	return workbook;
}