利用NPOI组件将Data和GridView导出为Excel代码,CSharp代码库,德仔网

代码语言
代码分类
【CSharp】利用NPOI组件将Data和GridView导出为Excel
作者:Chingy / 发布于2014/12/30/ 631
代码针对性较强，核心部分为格式化表头和合并单元格的方法公司报表表头以“→”来分行，针对此符号进行开发 GridView、DataTable导出为Excel 2007，模块外部调用方法

	/// <summary>
	/// 将GridView中的数据导出为Excel2007文件并下载
	/// </summary>
	/// <param name="gv">需要处理的GridView</param>
	/// <param name="ExportWay">导出方式:0:以内存流导出；1:以文件流导出/param>
	public void OptimizaToExcel(GridView gv, int ExportWay)
	{
	 switch (ExportWay)
	 {
	 case 0: DownLoadIWorkbookWithMemoeyStream(GvToWorkBook(gv));
	 break;
	 case 1: DownLoadIWorkbookWithFileStream(GvToWorkBook(gv));
	 break;
	 default:
	 break;
	 }
	}
	
	/// <summary>
	/// 下载NPOI工作簿并记录日志
	/// </summary>
	/// <param name="dt"></param>
	/// <param name="ExportWay">导出方式</param>
	/// <param name="ExportWay">导出方式:0:以内存流导出；1:以文件流导出/param>
	/// <param name="flagFormat">是否需要格式化</param>
	public void OptimizaToExcel(DataTable dt, int ExportWay, bool flagFormat)
	{
	 switch (ExportWay)
	 {
	 case 0: DownLoadIWorkbookWithMemoeyStream(DtToWorkBook(dt, false));
	 break;
	 case 1: DownLoadIWorkbookWithFileStream(DtToWorkBook(dt, false));
	 break;
	 default:
	 break;
	 }
	}

	

	#region DataTable导出为Excel2007
	
	 /// <summary>
	 /// 将DataTable转为NPOI工作簿
	 /// </summary>
	 /// <param name="dt"></param>
	 /// <param name="flagFormat">是否需要格式化</param>
	 /// <returns></returns>
	 private IWorkbook DtToWorkBook(DataTable dt, bool flagFormat)
	 {
	 List<int> colTypeList = GetColumnsType(dt);
	
	 IWorkbook hssfworkbook = new XSSFWorkbook();
	 ISheet sheet = hssfworkbook.CreateSheet("sheet1");
	
	 ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
	 ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);
	 ICellStyle cellStyle = GetCellStyleCommon(sheet.Workbook);
	
	 int groups = AddSheetHeader(sheet, dt, cellStyle);//表头行数
	 //为表格创建足够多的行
	 for (int i = groups; i < groups + dt.Rows.Count; i++) { sheet.CreateRow(i); }
	 int maxColumnMerge = -1;// DataTableMergSimpleValueRow(sheet, dt, groups);//DataTable数据合并区列索引最大值
	 if (flagFormat)
	 {
	 maxColumnMerge = DataTableMergSimpleValueRow(sheet, dt, groups);//DataTable数据合并区列索引最大值
	 }
	 AddSheetBody(sheet, dt, cellStyle, colTypeList, groups, maxColumnMerge);
	
	 return hssfworkbook;
	 }
	
	 #region 将DataTable转为NPOI工作簿时添加表头
	
	 /// <summary>
	 /// 将DataTable转为NPOI工作簿时添加表头
	 /// </summary>
	 /// <param name="sheet"></param>
	 /// <param name="dt"></param>
	 /// <param name="headerCellStyle"></param>
	 /// <returns>表头行数</returns>
	 private int AddSheetHeader(ISheet sheet, DataTable dt, ICellStyle headerCellStyle)
	 {
	 int groups = 0;//表头分行
	 foreach (DataColumn c in dt.Columns)
	 {
	 int len = c.ColumnName.Split('&rarr;').Length;
	 if (groups < len) groups = len;
	 }
	 if (groups < 1) groups = 1;
	
	 //预先创建足够的Excel行
	 for (int i = 0; i < groups; i++) { sheet.CreateRow(i); }
	
	 /***************************************/
	 /***************添加表头****************/
	 /***************************************/
	
	 ICell cell;
	 string groupName = String.Empty;//每一个分组的标题
	 int groupStartIndex = 0;//每一个分组的起始位置,用于添加单元格合并区域
	 for (int i = 0; i < dt.Columns.Count; i++)//i代表列索引
	 {
	 //如果Caption没有设置，则返回 ColumnName 的值
	 string headCellStr = dt.Columns[i].ColumnName;
	
	 //当前列所在分组的标题，不存在分组时标题设为空字符
	 string groupNameNow = headCellStr.Substring(0, headCellStr.IndexOf('&rarr;') == -1 ? 0 : headCellStr.IndexOf('&rarr;'));
	 //开始创建单元格的行索引
	 int startRowIndex = !string.IsNullOrEmpty(groupName) && groupNameNow.Equals(groupName) ? 1 : 0;
	 //当前列的分组数
	 int titleGroups = headCellStr.IndexOf('&rarr;') != -1 ? headCellStr.Split('&rarr;').Length : 1;
	 for (int j = startRowIndex; j < titleGroups; j++)
	 {
	 cell = sheet.GetRow(j).CreateCell(i);
	 //获得子标题
	 string subTitle = headCellStr.IndexOf('&rarr;') != -1 ? headCellStr.Split('&rarr;')[j].ToString() : headCellStr;
	 cell.SetCellValue(subTitle);
	 cell.CellStyle = headerCellStyle;
	 }
	 if (titleGroups < groups)//当前列分组数小于最大分组数，为最后一列添加合并区域
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(titleGroups - 1, groups - 1, i, i));
	 }
	 //当前分组标题与前一分组标题不同，要开始新的分组
	 if (!groupName.Equals(groupNameNow))
	 {
	 if (i > 0 && groupStartIndex != i - 1)//剔除第一列出现新列头的情况
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, groupStartIndex, i - 1));
	 }
	 groupStartIndex = i;//重置合并区域开始位置索引
	 groupName = groupNameNow;
	 }
	 else if (string.IsNullOrEmpty(groupName))
	 {
	 groupStartIndex = i;//重置合并区域开始位置索引
	 //groupName = string.Empty;
	 }
	 if (i == dt.Columns.Count - 1 && groupStartIndex < i)
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, groupStartIndex, i));
	 }
	 }
	 return groups;
	 }
	
	 #endregion
	
	 #region 将DataTable转为NPOI工作簿时添加表数据
	 /// <summary>
	 /// 将DataTable转为NPOI工作簿时添加表数据
	 /// </summary>
	 /// <param name="sheet"></param>
	 /// <param name="dt"></param>
	 /// <param name="cellStyle"></param>
	 /// <param name="colTypeList">每一列的数据类型</param>
	 /// <param name="groups">表头行数（作为数据行第一行的索引）</param>
	 /// <param name="maxColumnMerge">Excel所有非表头区域的合并单元格的最大列索引，若不启用则传人-1</param>
	 private void AddSheetBody(ISheet sheet, DataTable dt, ICellStyle cellStyle, List<int> colTypeList, int groups, int maxColumnMerge)
	 {
	 IRow row;
	 ICell cell;
	 ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
	 ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);
	
	 for (int i = 0; i < dt.Rows.Count; i++)
	 {
	 row = sheet.GetRow(i + groups);
	 for (int j = 0; j < dt.Columns.Count; j++)
	 {
	 if (dt.Rows[i][j] == null || string.IsNullOrEmpty(dt.Rows[i][j].ToString()))
	 {
	 continue;
	 }
	 #region 当列索引小于最大合并列索引时判断单元格是否处于合并区域内
	
	 //当列索引小于最大合并列索引时判断单元格是否处于合并区域内
	 bool flagSkip = false;
	 if (j <= maxColumnMerge)
	 {
	 for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
	 {
	 NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
	 if (a.LastRow < groups)//剔除标题处的合并区域
	 {
	 continue;
	 }
	 //当前单元格是处于合并区域内且不为合并区域第一个单元格时，跳过此单元格
	 if (a.FirstRow < i + groups && a.LastRow > i + groups)
	 {
	 flagSkip = true;
	 //Debug.WriteLine("第" + i.ToString() + "行" + j.ToString() + "列被跳过");
	 break;
	 }
	 }
	 }
	 if (flagSkip) continue;
	 #endregion
	
	 //创建单元格
	 cell = row.CreateCell(j);
	 if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型
	 {
	 cell.SetCellValue(dt.Rows[i][j].ToString());
	 cell.CellStyle = cellStyle;
	 }
	 else
	 {
	 string cellText = dt.Rows[i][j].ToString();
	 try
	 {
	 switch (colTypeList[j])
	 {
	 case 1: cell.SetCellValue(int.Parse(cellText));//int类型
	 cell.CellStyle = cellStyle;
	 break;
	 case 2: cell.SetCellValue(double.Parse(cellText));//decimal数据类型
	 cell.CellStyle = cellStyleDecimal;
	 break;
	 case 3: cell.SetCellValue(DateTime.Parse(cellText));//日期类型
	 cell.CellStyle = cellStyleDateTime;
	 break;
	 default: cell.SetCellValue(cellText);
	 cell.CellStyle = cellStyle;
	 break;
	 }
	 }
	 catch
	 {
	 cell.SetCellValue("单元格导出失败");
	 MCSFramework.Common.LogWriter.FILE_PATH = GetAttachmentDirectory();
	 MCSFramework.Common.LogWriter.WriteLog("\r\n第j=" + j + "类发生错误，数据类型为" + colTypeList[j].ToString() + "，数值为" + cellText + "，报表GUID=" + Request.QueryString["Report"] != null ? Request.QueryString["Report"] : "无GUID值" + "\r\n");
	 }
	 }
	 }
	 }
	 }
	 #endregion
	
	 #region 根据DataTable行组中相邻行相同值添加合并单元格区域
	
	 /// <summary>
	 /// 将DataTable各列中相邻行值相同的单元格合并显示
	 /// </summary>
	 /// <param name="sheet"></param>
	 /// <param name="dt"></param>
	 /// <param name="groupCount">NPOI表头行数</param>
	 /// <returns>被合并的列的最大索引值</returns>
	 private int DataTableMergSimpleValueRow(ISheet sheet, DataTable dt, int groupCount)
	 {
	 if (dt.Rows.Count == 0 || dt.Columns.Count == 0) return 0;
	
	 int maxColumnMerge = 0;//被合并的列的最大索引值
	 for (int ColumnIndex = 0; ColumnIndex < dt.Columns.Count; ColumnIndex++)
	 {
	 #region 是否停止添加合并区域
	 bool flagSkip = true;
	
	 if (ColumnIndex == 0)//第一列始终需要合并
	 {
	 flagSkip = false;
	 }
	 else
	 {
	 for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
	 {
	 NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
	 if (a.LastRow < groupCount)//剔除标题处的合并区域
	 {
	 continue;
	 }
	 //当存在最大列包含上一列的合并区域时，当前列仍为可合并状态
	 if (a.LastColumn >= maxColumnMerge)
	 {
	 flagSkip = false;
	 break;
	 }
	 }
	 }
	 if (flagSkip)//当前列的前一列不包括任何合并行时，停止后续列的行合并
	 {
	 return maxColumnMerge - 1 >= 0 ? maxColumnMerge - 1 : 0;
	 }
	 #endregion
	
	 int rowspan = 0;
	 for (int i = dt.Rows.Count - 1; i >= 0; i--)
	 {
	 if (DataColumnIsSimple(sheet, dt, i, ColumnIndex, groupCount))
	 {
	 rowspan++;
	 }
	 else if (rowspan > 0)
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(groupCount + i, groupCount + i + rowspan, ColumnIndex, ColumnIndex));
	 rowspan = 0;
	 }
	 }
	 if (rowspan > 1)//行数超过一行时第一列完全一致
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(groupCount, groupCount + rowspan + 1, ColumnIndex, ColumnIndex));
	 }
	
	 maxColumnMerge = ColumnIndex;
	
	 }
	 if (maxColumnMerge == dt.Columns.Count - 1)
	 {
	 return maxColumnMerge;
	 }
	 return maxColumnMerge - 1;
	 }
	
	 /// <summary>
	 /// 判断当前列是否为简单列
	 /// </summary>
	 /// <param name="dt"></param>
	 /// <param name="row">最大行索引</param>
	 /// <param name="column">列序号</param>
	 /// <returns></returns>
	 private bool DataColumnIsSimple(ISheet sheet, DataTable dt, int row, int column, int groups)
	 {
	 if (dt.Rows.Count < row || dt.Columns.Count < column || string.IsNullOrEmpty(dt.Rows[row][column].ToString()) || row == 0) return false;
	
	 //for (int i = column; i >= 0; i--)
	 //{
	 // if (dt.Rows[row][column].ToString() != dt.Rows[row - 1][column].ToString()) return false;
	 //}
	 //bool flag = false;
	 if (dt.Rows[row][column].ToString() == dt.Rows[row - 1][column].ToString())
	 {
	 int numMergedRegions = sheet.NumMergedRegions;
	 if (numMergedRegions == 0 || column == 0) return true;
	 //如果前一列的同一行和上一行处于相同的合并区域内
	 for (int m = 0; m < numMergedRegions; m++)
	 {
	 NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
	 int preCol = column - 1 > 0 ? column - 1 : 0;
	 if (a.FirstColumn <= preCol && a.LastColumn >= preCol && a.FirstRow <= groups + row - 1 && a.LastRow >= row + groups)
	 {
	 return true;
	 }
	 }
	 }
	 return false;
	 }
	
	 #endregion
	
	 #endregion

	

	

	#region 将GridView转为Excel2007
	 /// <summary>
	 /// 将GridView转为NPOI工作簿
	 /// </summary>
	 /// <param name="gv">需要处理的GridView</param>
	 /// <returns></returns>
	 private IWorkbook GvToWorkBook(GridView gv)
	 {
	 List<int> colTypeList = GetColumnsType((DataTable)gv.DataSource);
	
	 IWorkbook hssfworkbook = new XSSFWorkbook();
	 ISheet sheet = hssfworkbook.CreateSheet("sheet1");
	
	 ICellStyle cellStyle = GetCellStyleCommon(hssfworkbook);
	
	 int colCount = 0;//记录GridView列数
	 //rowInex记录表头的行数
	 int rowIndex = AddSheetHeader(sheet, gv.HeaderRow, cellStyle, "</th></tr><tr>", out colCount);//添加表头
	 AddSheetBody(sheet, gv, cellStyle, colTypeList, colCount, rowIndex);
	
	 return hssfworkbook;
	 }
	
	 #region 添加表头
	 /// <summary>
	 /// 为Excel添加表头
	 /// </summary>
	 /// <param name="sheet"></param>
	 /// <param name="headerRow">GridView的HeaderRow属性</param>
	 /// <param name="headerCellStyle">表头格式</param>
	 /// <param name="flagNewLine">转行标志</param>
	 /// <param name="colCount">Excel表列数</param>
	 /// <returns>Excel表格行数</returns>
	 private int AddSheetHeader(ISheet sheet, GridViewRow headerRow, ICellStyle headerCellStyle, string flagNewLine, out int colCount)
	 {
	 //int
	 colCount = 0;//记录GridView列数
	 int rowInex = 0;//记录表头的行数
	
	 IRow row = sheet.CreateRow(0);
	 ICell cell;
	
	 int groupCount = 0;//记录分组数
	 int colIndex = 0;//记录列索引，并于结束表头遍历后记录总列数
	 for (int i = 0; i < headerRow.Cells.Count; i++)
	 {
	 if (rowInex != groupCount)//新增了标题行时重新创建
	 {
	 row = sheet.CreateRow(rowInex);
	 groupCount = rowInex;
	 }
	
	 #region 是否跳过当前单元格
	
	 for (int m = 0; m < sheet.NumMergedRegions; m++)//遍历所有合并区域
	 {
	 NPOI.SS.Util.CellRangeAddress a = sheet.GetMergedRegion(m);
	 //当前单元格是处于合并区域内
	 if (a.FirstColumn <= colIndex && a.LastColumn >= colIndex
	 && a.FirstRow <= rowInex && a.LastRow >= rowInex)
	 {
	 colIndex++;
	 m = 0;//重新遍历所有合并区域判断新单元格是否位于合并区域
	 }
	 }
	
	
	 #endregion
	
	 cell = row.CreateCell(colIndex);
	 cell.CellStyle = headerCellStyle;
	
	 TableCell tablecell = headerRow.Cells[i];
	
	 //跨列属性可能为添加了html属性colspan，也可能是由cell的ColumnSpan属性指定
	 int colSpan = 0;
	 int rowSpan = 0;
	
	 #region 获取跨行跨列属性值
	 //跨列
	 if (!string.IsNullOrEmpty(tablecell.Attributes["colspan"]))
	 {
	 colSpan = int.Parse(tablecell.Attributes["colspan"].ToString());
	 colSpan--;
	 }
	 if (tablecell.ColumnSpan > 1)
	 {
	 colSpan = tablecell.ColumnSpan;
	 colSpan--;
	 }
	
	 //跨行
	 if (!string.IsNullOrEmpty(tablecell.Attributes["rowSpan"]))
	 {
	 rowSpan = int.Parse(tablecell.Attributes["rowSpan"].ToString());
	 rowSpan--;
	 }
	 if (tablecell.RowSpan > 1)
	 {
	 rowSpan = tablecell.RowSpan;
	 rowSpan--;
	 }
	 #endregion
	
	 //添加excel合并区域
	 if (colSpan > 0 || rowSpan > 0)
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + rowSpan, colIndex, colIndex + colSpan));
	 colIndex += colSpan + 1;//重新设置列索引
	 }
	 else
	 {
	 colIndex++;
	 }
	 string strHeader = headerRow.Cells[i].Text;
	
	 if (strHeader.Contains(flagNewLine))//换行标记，当只存在一行标题时不存在</th></tr><tr>，此时colCount无法被赋值
	 {
	 rowInex++;
	 colCount = colIndex;
	 colIndex = 0;
	
	 strHeader = strHeader.Substring(0, strHeader.IndexOf("</th></tr><tr>"));
	 }
	 cell.SetCellValue(strHeader);
	 }
	 if (groupCount == 0)//只有一行标题时另外为colCount赋值
	 {
	 colCount = colIndex;
	 }
	 rowInex++;//表头结束后另起一行开始记录控件数据行索引
	
	 return rowInex;
	 }
	 #endregion
	
	 #region 添加表数据
	 /// <summary>
	 /// 为Excel添加数据
	 /// </summary>
	 /// <param name="sheet"></param>
	 /// <param name="gv"></param>
	 /// <param name="colTypeList">GridView每一列的数据类型</param>
	 /// <param name="colCount">GridView的总列数</param>
	 /// <param name="rowInex">添加Excel数据行的起始索引号</param>
	 /// <param name="cellStyle">表格基础格式</param>
	 /// <returns>Excel表格行数</returns>
	 private int AddSheetBody(ISheet sheet, GridView gv, ICellStyle cellStyle, List<int> colTypeList, int colCount, int rowInex)
	 {
	 IRow row;
	 ICell cell;
	 ICellStyle cellStyleDecimal = GetCellStyleDecimal(sheet.Workbook);
	 ICellStyle cellStyleDateTime = GetCellStyleDateTime(sheet.Workbook);
	
	 int rowCount = gv.Rows.Count;
	
	 for (int i = 0; i < rowCount; i++)
	 {
	 row = sheet.CreateRow(rowInex);
	
	 for (int j = 0; j < colCount; j++)
	 {
	 if (gv.Rows[i].Cells[j].Visible == false) continue;
	
	 string cellText = gv.Rows[i].Cells[j].Text.Trim();
	 cellText = cellText.Replace("&nbsp;", "");//替换空字符占位符
	 cellText = cellText.Replace("&gt;", ">");//替换 > 占位符
	
	 if (string.IsNullOrEmpty(cellText)) continue;//单元格为空跳过
	
	 cell = row.CreateCell(j);
	 if (colTypeList.Count == 0 || colTypeList.Count < j || colTypeList[j] <= 0)//无法获取到该列类型
	 {
	 cell.SetCellValue(cellText);
	 cell.CellStyle = cellStyle;
	 }
	 else
	 {
	 try
	 {
	 switch (colTypeList[j])
	 {
	 case 1: cell.SetCellValue(int.Parse(cellText));//int类型
	 cell.CellStyle = cellStyle;
	 break;
	 case 2: cell.SetCellValue(double.Parse(cellText));//decimal数据类型
	 cell.CellStyle = cellStyleDecimal;
	 break;
	 case 3: cell.SetCellValue(DateTime.Parse(cellText));//日期类型
	 cell.CellStyle = cellStyleDateTime;
	 break;
	 default: cell.SetCellValue(cellText);
	 cell.CellStyle = cellStyle;
	 break;
	 }
	 }
	 catch
	 {
	 cell.SetCellValue("单元格导出失败");
	 MCSFramework.Common.LogWriter.FILE_PATH = GetAttachmentDirectory();
	 MCSFramework.Common.LogWriter.WriteLog("\r\n第j=" + j + "类发生错误，数据类型为" + colTypeList[j].ToString() + "，数值为" + cellText + "，报表GUID=" + Request.QueryString["Report"] != null ? Request.QueryString["Report"] : "无GUID值" + "\r\n");
	 }
	 }
	
	 int MergeAcross = gv.Rows[i].Cells[j].ColumnSpan > 0 ? gv.Rows[i].Cells[j].ColumnSpan - 1 : 0;//跨列，即合并的列数
	
	 int MergeDown = gv.Rows[i].Cells[j].RowSpan > 0 ? gv.Rows[i].Cells[j].RowSpan - 1 : 0;//跨行，即合并的行数
	
	 if (MergeAcross > 0 || MergeDown > 0)//存在要合并的行
	 {
	 sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowInex, rowInex + MergeDown, j, j + MergeAcross));
	 j += MergeAcross;
	 }
	 }
	 rowInex++;
	 }
	 return rowInex;
	 }
	
	 #endregion
	
	 #endregion
	4. [代码]根据DataTable获取列类型
	?
	1
	2
	3
	4
	5
	6
	7
	8
	9
	10
	11
	12
	13
	14
	15
	16
	17
	18
	19
	20
	21
	#region 根据DataTable获取列类型
	 /// <summary>
	 /// 根据DataTable获取列类型
	 /// </summary>
	 /// <param name="gv"></param>
	 /// <returns>1：Int32；2：Decimal；3：DateTime；4：String</returns>
	 private List<int> GetColumnsType(DataTable tb)
	 {
	 List<int> colTypeList = new List<int>();
	 foreach (DataColumn col in tb.Columns)
	 {
	 int dataType = 0;
	 if (col.DataType.FullName == "System.Int32") dataType = 1;
	 else if (col.DataType.FullName == "System.Decimal") dataType = 2;
	 else if (col.DataType.FullName == "System.DateTime") dataType = 3;
	 else dataType = 4;
	 colTypeList.Add(dataType);
	 }
	 return colTypeList;
	 }
	 #endregion

	

	#region 获取NPOI单元格类型
	 /// <summary>
	 /// 单元格居中，作为单元格类型基础方法不在外部调用
	 /// </summary>
	 /// <param name="cellStyle"></param>
	 /// <returns></returns>
	 private ICellStyle CellStyleBasic(ICellStyle cellStyle)
	 {
	 cellStyle.Alignment = HorizontalAlignment.Center;
	 cellStyle.VerticalAlignment = VerticalAlignment.Center;
	 return cellStyle;
	 }
	 /// <summary>
	 /// 通用单元格格式
	 /// </summary>
	 /// <param name="hssfworkbook"></param>
	 /// <returns></returns>
	 private ICellStyle GetCellStyleCommon(IWorkbook hssfworkbook)
	 {
	 ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
	 CellStyleBasic(cellStyle);
	 return cellStyle;
	 }
	 private ICellStyle GetCellStyleDecimal(IWorkbook hssfworkbook)
	 {
	 ICellStyle cellStyleDecimal = hssfworkbook.CreateCellStyle();
	 CellStyleBasic(cellStyleDecimal);
	 cellStyleDecimal.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("0.000");
	 return cellStyleDecimal;
	 }
	 private ICellStyle GetCellStyleDateTime(IWorkbook hssfworkbook)
	 {
	 ICellStyle cellStyleDateTime = hssfworkbook.CreateCellStyle();
	 CellStyleBasic(cellStyleDateTime);
	 cellStyleDateTime.DataFormat = hssfworkbook.CreateDataFormat().GetFormat("yyyy/m/d h:mm:ss");
	 return cellStyleDateTime;
	 }
	 #endregion

	

	#region 获取附件文件夹路径
	 /// <summary>
	 /// 获取下载文件夹路径
	 /// </summary>
	 /// <returns></returns>
	 public string GetAttachmentDirectory()
	 {
	 string path = ConfigHelper.GetConfigString("AttachmentPath");
	 if (path.StartsWith("~")) path = Server.MapPath(path);
	 if (!path.EndsWith("\\")) path = path + "\\";
	 path += "ImportExcelSVM\\Download\\";
	 if (!Directory.Exists(path)) Directory.CreateDirectory(path);
	 return path;
	 }
	 #endregion

	#region Excel文件下载
	
	 /// <summary>
	 /// 对提供下载的附件名进行编码
	 /// </summary>
	 /// <param name="fileName"></param>
	 /// <returns></returns>
	 private string FileNameEncode(string fileName)
	 {
	 bool isFireFox = false;
	 if (Request.ServerVariables["http_user_agent"].ToLower().IndexOf("firefox") != -1)
	 {
	 isFireFox = true;
	 }
	 if (isFireFox == true)
	 {
	 //文件名前后加双引号
	 fileName = "\"" + fileName + "\"";
	 }
	 else
	 {
	 //非火狐浏览器对中文文件名进行HTML编码
	 fileName = HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8);
	 }
	 return fileName;
	 }
	
	 /// <summary>
	 /// 以内存流下载Excel文件下载，自动获取报表命名
	 /// </summary>
	 /// <param name="hssfworkbook"></param>
	 private void DownLoadIWorkbookWithMemoeyStream(IWorkbook hssfworkbook)
	 {
	 MemoryStream memory = new MemoryStream();
	
	 try
	 {
	 hssfworkbook.Write(memory);
	 }
	 catch (Exception e1)
	 {
	 MessageBox.Show(Page, "errorMessage=" + e1.Message + "\r\n导出出错，请用数据源方式导出");
	 return;
	 }
	
	 Guid reportGuid = Request.QueryString["Report"] != null ? new Guid(Request.QueryString["Report"]) : Guid.Empty;
	 string fileName = string.Format("{1}_{0:yyyy_MM_dd_HH_mm}.xlsx", System.DateTime.Now, new Rpt_ReportBLL(reportGuid).Model.Name);
	 this.DownLoadFile(memory, fileName);
	 }
	 /// <summary>
	 /// 以文件流下载Excel文件下载，自动获取报表命名
	 /// </summary>
	 /// <param name="hssfworkbook"></param>
	 private void DownLoadIWorkbookWithFileStream(IWorkbook hssfworkbook)
	 {
	 string filePath = GetAttachmentDirectory();
	 Guid reportGuid = Request.QueryString["Report"] != null ? new Guid(Request.QueryString["Report"]) : Guid.Empty;
	 string fileName = string.Format("{1}_{0:yyyy_MM_dd_HH_mm}.xlsx", System.DateTime.Now, new Rpt_ReportBLL(reportGuid).Model.Name);
	
	 try
	 {
	 FileStream fs = File.Create(filePath + fileName);
	 hssfworkbook.Write(fs);
	 }
	 catch (Exception e1)
	 {
	 MessageBox.Show(Page, "errorMessage=" + e1.Message + ";filePath=" + filePath + "\r\n导出出错，请用数据源方式导出");
	 return;
	 }
	
	 this.DownLoadFile(filePath + fileName, fileName);
	 }
	
	 /// <summary>
	 /// 文件下载
	 /// </summary>
	 /// <param name="stream">内存流</param>
	 /// <param name="fileName">下载文件名</param>
	 private void DownLoadFile(MemoryStream stream, string fileName)
	 {
	 fileName = FileNameEncode(fileName);
	 Response.Clear();
	 Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
	 Response.ContentType = "application/ms-excel";
	 byte[] bytes = stream.ToArray();
	 Response.BinaryWrite(bytes);//此方法额外占用二进制字节流的内存空间
	 //stream.WriteTo(Response.OutputStream);//通知浏览器下载文件
	 Response.Flush();
	 //Response.End();//End操作后不能进行后续打印操作，但不进行End操作会导致导出的xlsx文件信息不完整（单元格导出正常）
	 }
	
	 private void DownLoadFile(string filePath, string fileName)
	 {
	 fileName = FileNameEncode(fileName);
	 Response.Clear();
	 //Response.BufferOutput = true;
	 Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);
	 Response.ContentType = "application/ms-excel";
	 if (File.Exists(filePath))
	 {
	 Response.WriteFile(filePath);//通知浏览器下载文件
	 }
	 Response.Flush();
	 //File.Delete(filePath);
	 //Response.End();
	 }
	
	 #endregion
评论列表
本站所提供的代码，版权归原作者所有，若有侵犯作者版权，请与我们联系，我们将立即删除或修改。谢谢!
本站所有代码发布及提供者。
试试其它关键字
　NPOI　　Excel　　导出Excel　　GridView　
同语言下
可能有用的
Chingy贡献的其它代码(1)
.利用NPOI组件将Data和GridView导出为Excel