315 lines
12 KiB
C#
315 lines
12 KiB
C#
using System;
|
||
using System.Collections.Generic;
|
||
using System.Data;
|
||
using System.IO;
|
||
using System.Linq;
|
||
using System.Web;
|
||
using NPOI.XSSF.UserModel;
|
||
using NPOI.HSSF.UserModel;
|
||
using NPOI.HPSF;
|
||
using NPOI.POIFS.FileSystem;
|
||
using NPOI.SS.UserModel;
|
||
|
||
namespace SuperMap.RealEstate.Personnel.Common
|
||
{
|
||
public class ExcelHelper
|
||
{
|
||
private string fileName = null; //文件名
|
||
private IWorkbook workbook = null;
|
||
private FileStream fs = null;
|
||
private bool disposed;
|
||
|
||
public ExcelHelper(string fileName)
|
||
{
|
||
this.fileName = fileName;
|
||
disposed = false;
|
||
}
|
||
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
|
||
{
|
||
int i = 0;
|
||
int j = 0;
|
||
int count = 0;
|
||
ISheet sheet = null;
|
||
|
||
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
|
||
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
|
||
workbook = new XSSFWorkbook();
|
||
else if (fileName.IndexOf(".xls") > 0) // 2003版本
|
||
workbook = new HSSFWorkbook();
|
||
|
||
try
|
||
{
|
||
if (workbook != null)
|
||
{
|
||
sheet = workbook.CreateSheet(sheetName);
|
||
}
|
||
else
|
||
{
|
||
return -1;
|
||
}
|
||
|
||
if (isColumnWritten == true) //写入DataTable的列名
|
||
{
|
||
IRow row = sheet.CreateRow(0);
|
||
for (j = 0; j < data.Columns.Count; ++j)
|
||
{
|
||
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
|
||
}
|
||
count = 1;
|
||
}
|
||
else
|
||
{
|
||
count = 0;
|
||
}
|
||
|
||
for (i = 0; i < data.Rows.Count; ++i)
|
||
{
|
||
IRow row = sheet.CreateRow(count);
|
||
for (j = 0; j < data.Columns.Count; ++j)
|
||
{
|
||
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
|
||
}
|
||
++count;
|
||
}
|
||
//workbook.Write(fs); //写入到excel,但这种直接写路径不是 下载那个位置，是虚拟目录上
|
||
|
||
// 写入到客户端
|
||
System.IO.MemoryStream ms = new System.IO.MemoryStream();
|
||
workbook.Write(ms);
|
||
|
||
HttpContext.Current.Response.Clear();
|
||
HttpContext.Current.Response.Buffer = true;
|
||
HttpContext.Current.Response.Charset = "utf-8";
|
||
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("utf-8");//设置输出流为简体中文
|
||
HttpContext.Current.Response.AddHeader("Content-Disposition", string.Format("attachment; filename=" + "年度利率表.xlsx", DateTime.Now.ToString("yyyyMMddHHmmssfff")));
|
||
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
|
||
HttpContext.Current.Response.ContentType = "application/ms-excel";//设置输出文件类型为excel文件。
|
||
HttpContext.Current.Response.End();
|
||
workbook = null;
|
||
ms.Close();
|
||
ms.Dispose();
|
||
|
||
|
||
|
||
|
||
|
||
return count;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
Console.WriteLine("Exception: " + ex.Message);
|
||
return -1;
|
||
}
|
||
}
|
||
|
||
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn)
|
||
{
|
||
ISheet sheet = null;
|
||
DataTable data = new DataTable();
|
||
int startRow = 0;
|
||
try
|
||
{
|
||
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
|
||
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
|
||
// workbook = new XSSFWorkbook(fs);
|
||
//else if (fileName.IndexOf(".xls") > 0) // 2003版本
|
||
// workbook = new HSSFWorkbook(fs);
|
||
try
|
||
{
|
||
workbook = new XSSFWorkbook(fs);
|
||
}
|
||
catch
|
||
{
|
||
workbook = new HSSFWorkbook(fs);
|
||
}
|
||
|
||
if (sheetName != null)
|
||
{
|
||
sheet = workbook.GetSheet(sheetName);
|
||
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
|
||
{
|
||
sheet = workbook.GetSheetAt(0);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
sheet = workbook.GetSheetAt(0);
|
||
}
|
||
if (sheet != null)
|
||
{
|
||
IRow firstRow = sheet.GetRow(1);
|
||
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
|
||
|
||
if (isFirstRowColumn)
|
||
{
|
||
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
||
{
|
||
ICell cell = firstRow.GetCell(i);
|
||
if (cell != null)
|
||
{
|
||
string cellValue = cell.StringCellValue;
|
||
if (cellValue != null)
|
||
{
|
||
DataColumn column = new DataColumn(cellValue);
|
||
data.Columns.Add(column);
|
||
}
|
||
}
|
||
}
|
||
startRow = sheet.FirstRowNum + 1;
|
||
}
|
||
else
|
||
{
|
||
startRow = sheet.FirstRowNum;
|
||
}
|
||
//最后一列的标号
|
||
int rowCount = sheet.LastRowNum;
|
||
for (int i = startRow; i <= rowCount; ++i)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue; //没有数据的行默认是null
|
||
|
||
DataRow dataRow = data.NewRow();
|
||
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
||
{
|
||
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
|
||
dataRow[j] = row.GetCell(j).ToString();
|
||
}
|
||
data.Rows.Add(dataRow);
|
||
}
|
||
}
|
||
|
||
return data;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw ex;
|
||
}
|
||
}
|
||
public DataTable ExcelToDataTableOvertime(string sheetName, bool isFirstRowColumn)
|
||
{
|
||
ISheet sheet = null;
|
||
DataTable data = new DataTable();
|
||
int startRow = 0;
|
||
try
|
||
{
|
||
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
|
||
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
|
||
// workbook = new XSSFWorkbook(fs);
|
||
//else if (fileName.IndexOf(".xls") > 0) // 2003版本
|
||
// workbook = new HSSFWorkbook(fs);
|
||
try
|
||
{
|
||
workbook = new XSSFWorkbook(fs);
|
||
}
|
||
catch
|
||
{
|
||
workbook = new HSSFWorkbook(fs);
|
||
}
|
||
|
||
if (sheetName != null)
|
||
{
|
||
sheet = workbook.GetSheet(sheetName);
|
||
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
|
||
{
|
||
sheet = workbook.GetSheetAt(0);
|
||
}
|
||
}
|
||
else
|
||
{
|
||
sheet = workbook.GetSheetAt(0);
|
||
}
|
||
if (sheet != null)
|
||
{
|
||
IRow firstRow = sheet.GetRow(1);
|
||
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
|
||
|
||
if (isFirstRowColumn)
|
||
{
|
||
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
|
||
{
|
||
ICell cell = firstRow.GetCell(i);
|
||
if (cell != null)
|
||
{
|
||
string cellValue = cell.StringCellValue;
|
||
if (cellValue != null)
|
||
{
|
||
DataColumn column = new DataColumn(cellValue);
|
||
if (data.Columns.Contains(column.Caption))
|
||
{
|
||
column.ColumnName+=i.ToString();
|
||
}
|
||
data.Columns.Add(column);
|
||
}
|
||
}
|
||
}
|
||
startRow = sheet.FirstRowNum + 1;
|
||
}
|
||
else
|
||
{
|
||
startRow = sheet.FirstRowNum;
|
||
}
|
||
//最后一列的标号
|
||
int rowCount = sheet.LastRowNum;
|
||
for (int i = startRow; i <= rowCount; ++i)
|
||
{
|
||
IRow row = sheet.GetRow(i);
|
||
if (row == null) continue; //没有数据的行默认是null
|
||
|
||
DataRow dataRow = data.NewRow();
|
||
for (int j = row.FirstCellNum; j < cellCount; ++j)
|
||
{
|
||
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
|
||
dataRow[j] = row.GetCell(j).ToString();
|
||
}
|
||
data.Rows.Add(dataRow);
|
||
}
|
||
}
|
||
|
||
return data;
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw ex;
|
||
}
|
||
}
|
||
|
||
public void ExcelExport(string sheetName, bool isFirstRowColumn)
|
||
{
|
||
|
||
try
|
||
{
|
||
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
|
||
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
|
||
workbook = new XSSFWorkbook(fs);
|
||
else if (fileName.IndexOf(".xls") > 0) // 2003版本
|
||
workbook = new HSSFWorkbook(fs);
|
||
|
||
// 写入到客户端
|
||
System.IO.MemoryStream ms = new System.IO.MemoryStream();
|
||
workbook.Write(ms);
|
||
|
||
|
||
string downloadName = "导入模板表格.xls";
|
||
HttpContext.Current.Response.Clear();
|
||
HttpContext.Current.Response.Buffer = true;
|
||
HttpContext.Current.Response.Charset = "gb2312";
|
||
HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("gb2312");//设置输出流为简体中文
|
||
if (HttpContext.Current.Request.Browser.Browser == "IE")
|
||
downloadName = HttpUtility.UrlPathEncode(downloadName);
|
||
string headerValue = string.Format("attachment; filename=\"{0}\"", downloadName);
|
||
HttpContext.Current.Response.AddHeader("Content-Disposition", headerValue);
|
||
HttpContext.Current.Response.ContentType = "application/octet-stream";//设置输出文件类型为excel文件。
|
||
HttpContext.Current.Response.BinaryWrite(ms.ToArray());
|
||
HttpContext.Current.Response.End();
|
||
workbook = null;
|
||
ms.Close();
|
||
ms.Dispose();
|
||
}
|
||
catch (Exception ex)
|
||
{
|
||
throw ex;
|
||
}
|
||
}
|
||
|
||
}
|
||
} |