using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
//add
using System.Data;
using System.IO;
using NPOI;
using NPOI.HSSF.UserModel;
public partial class ExeclOperation : System.Web.UI.Page
{
#region 页面加载
protected void Page_Load(object sender, EventArgs e)
{
DataTable dt = ReadExcelToDataTable("~/xls/demo001.xls", 0, 0);
ViewState["dtview"] = dt;
GridView1.DataSource = dt;
GridView1.DataBind();
}
#endregion
#region DS直接生成Execl
protected void btnExport_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
DataTable dt = ViewState["dtview"] as DataTable;
ds.Tables.Add(dt);
bool success = ExportExcelByDataSet(ds, "~/xls/", "demo.xls", "这是测试数据");
if (success)
{
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "alert1", "alert('生成execl文件成功')", true);
}
else
{
ScriptManager.RegisterClientScriptBlock(Page, typeof(Page), "alert2", "alert('生成execl文件失败')", true);
}
}
#endregion
#region GridView自动列适应，不换行
protected void GridView1_RowCreated(object sender, GridViewRowEventArgs e)
{
if (e.Row.RowType == DataControlRowType.DataRow || e.Row.RowType == DataControlRowType.Header)
{
TableCellCollection cells1 = e.Row.Cells;
for (int i = 0; i < cells1.Count; i++)
{
cells1[i].Wrap = false;
}
}
}
#endregion
#region DataSet与Execl互转
/// <summary>
/// 传入ds直接生成excel文件
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="strPath">文件路径</param>
/// <param name="strFileName">文件名</param>
/// <param name="ReportHeader">execl表头</param>
/// <returns></returns>
public static bool ExportExcelByDataSet(DataSet ds, string strPath, string strFileName, string ReportHeader = "")
{
//NPOI
HSSFWorkbook hssfworkbook2 = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("sheet1");
//定义字体 font 设置字体类型和大小
HSSFFont font = (HSSFFont)hssfworkbook2.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//定义单元格格式；单元格格式style1 为font的格式
HSSFCellStyle style1 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();
style1.SetFont(font);
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
HSSFCellStyle style2 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//style2.WrapText = true;
//设置大标题行
int RowCount = 0;
int arrFlag = 0;
string TileName1 = "";
string TileName2 = "";
string s = ReportHeader;
string[] sArray = s.Split('|');
if (ReportHeader != "")
{
foreach (string i in sArray)
{
string str1 = i.ToString();
string[] subArray = str1.Split('@');
foreach (string k in subArray)
{
Console.WriteLine(k.ToString());
if (arrFlag == 0)
{
TileName1 = k.ToString();
}
else
{
TileName2 = k.ToString();
}
arrFlag = arrFlag + 1;
}
HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
row0.CreateCell(0).SetCellValue(TileName1);
row0.CreateCell(1).SetCellValue(TileName2);
RowCount = RowCount + 1;
arrFlag = 0;
}
}
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14;//全局列宽
sheet.DefaultRowHeightInPoints = 15;//全局行高
//设置标题行数据
int a = 0;
string mColumnName = "";
HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
for (int k = 0; k < ds.Tables[0].Columns.Count; k++)
{
mColumnName = ds.Tables[0].Columns[k].ColumnName.ToString();
row1.CreateCell(a).SetCellValue(mColumnName);
row1.Cells[a].CellStyle = style2;
a++;
}
//填写ds数据进excel
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)//写6行数据
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1);
int b = 0;
for (int j = 0; j < ds.Tables[0].Columns.Count; j++)
{
string DgvValue = "";
DgvValue = ds.Tables[0].Rows[i][j].ToString(); ;
row2.CreateCell(b).SetCellValue(DgvValue);
b++;
}
}
//获取用户选择路径
string ReportPath = HttpContext.Current.Server.MapPath(strPath + strFileName);
//创建excel
System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);
hssfworkbook2.Write(file3);
file3.Close();
return true;
}
/// <summary>
/// 用NPOI直接读取excel返回DataTable
/// </summary>
/// <param name="ExcelFileStream">文件流</param>
/// <param name="SheetIndex">Sheet序号</param>
/// <param name="StartRowIndex">开始行号</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(Stream ExcelFileStream, int SheetIndex, int StartRowIndex)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(SheetIndex);
DataTable table = new DataTable();
HSSFRow headerRow = (HSSFRow)sheet.GetRow(StartRowIndex);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
int rowCount = sheet.LastRowNum;
for (int i = (StartRowIndex + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j).ToString();
}
table.Rows.Add(dataRow);
}
ExcelFileStream.Close();
workbook = null;
sheet = null;
return table;
}
/// <summary>
/// 用NPOI直接读取excel返回DataTable
/// </summary>
/// <param name="FilePath">文件路径</param>
/// <param name="SheetIndex">Sheet序号</param>
/// <param name="StartRowIndex">开始行号</param>
/// <returns></returns>
public static DataTable ReadExcelToDataTable(string FilePath, int SheetIndex, int StartRowIndex)
{
DataSet ds = new DataSet();
FileStream fs = File.Open(HttpContext.Current.Server.MapPath(FilePath), FileMode.Open);
DataTable dt = ReadExcelToDataTable(fs, SheetIndex, StartRowIndex);
return dt;
}
#endregion
}
#region DataSet与Execl互转
/// <summary>
/// 传入ds直接生成excel文件
/// </summary>
/// <param name="ds">DataSet</param>
/// <param name="strPath">文件路径</param>
/// <param name="ReportHeader">execl表头</param>
/// <returns></returns>
public static bool ExportExcelByDataSet(DataSet ds, string strPath, string ReportHeader = "")
{
//NPOI
HSSFWorkbook hssfworkbook2 = new HSSFWorkbook();
#region 循环开始
for (int p = 0; p < ds.Tables.Count; p++)
{
int t = p + 1;
HSSFSheet sheet = (HSSFSheet)hssfworkbook2.CreateSheet("page" + t);
//定义字体 font 设置字体类型和大小
HSSFFont font = (HSSFFont)hssfworkbook2.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//定义单元格格式；单元格格式style1 为font的格式
HSSFCellStyle style1 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();
style1.SetFont(font);
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.LEFT;
HSSFCellStyle style2 = (HSSFCellStyle)hssfworkbook2.CreateCellStyle();
style2.SetFont(font);
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.CENTER;
style2.BorderBottom = NPOI.SS.UserModel.BorderStyle.THIN;
style2.BorderLeft = NPOI.SS.UserModel.BorderStyle.THIN;
style2.BorderRight = NPOI.SS.UserModel.BorderStyle.THIN;
style2.BorderTop = NPOI.SS.UserModel.BorderStyle.THIN;
//style2.WrapText = true;
//设置大标题行
int RowCount = 0;
int arrFlag = 0;
string TileName1 = "";
string TileName2 = "";
string s = ReportHeader;
string[] sArray = s.Split('|');
if (ReportHeader != "")
{
foreach (string i in sArray)
{
string str1 = i.ToString();
string[] subArray = str1.Split('@');
foreach (string k in subArray)
{
Console.WriteLine(k.ToString());
if (arrFlag == 0)
{
TileName1 = k.ToString();
}
else
{
TileName2 = k.ToString();
}
arrFlag = arrFlag + 1;
}
HSSFRow row0 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
row0.CreateCell(0).SetCellValue(TileName1);
row0.CreateCell(1).SetCellValue(TileName2);
RowCount = RowCount + 1;
arrFlag = 0;
}
}
//设置全局列宽和行高
sheet.DefaultColumnWidth = 14;//全局列宽
sheet.DefaultRowHeightInPoints = 15;//全局行高
//设置标题行数据
int a = 0;
string mColumnName = "";
HSSFRow row1 = (HSSFRow)sheet.CreateRow(RowCount); //创建报表表头标题 8列
for (int k = 0; k < ds.Tables[p].Columns.Count; k++)
{
mColumnName = ds.Tables[p].Columns[k].ColumnName.ToString();
row1.CreateCell(a).SetCellValue(mColumnName);
row1.Cells[a].CellStyle = style2;
a++;
}
//填写ds数据进excel
for (int i = 0; i < ds.Tables[p].Rows.Count; i++)//写6行数据
{
HSSFRow row2 = (HSSFRow)sheet.CreateRow(i + RowCount + 1);
int b = 0;
for (int j = 0; j < ds.Tables[p].Columns.Count; j++)
{
string DgvValue = "";
DgvValue = ds.Tables[p].Rows[i][j].ToString(); ;
row2.CreateCell(b).SetCellValue(DgvValue);
b++;
}
}
}
#endregion
//获取用户选择路径
string ReportPath = (strPath);
//创建excel
System.IO.FileStream file3 = new FileStream(ReportPath, FileMode.Create);
hssfworkbook2.Write(file3);
file3.Close();
return true;
}
/// <summary>
/// 用NPOI直接读取excel返回DataSet
/// </summary>
/// <param name="ExcelFileStream">FileStream fs = File.Open(dlg.FileName, FileMode.Open);</param>
/// <param name="SheetCount">sheet数量</param>
/// <returns></returns>
public static DataSet ReadExcelToDataSet(Stream ExcelFileStream,int SheetCount)
{
HSSFWorkbook workbook = new HSSFWorkbook(ExcelFileStream);
DataSet ds = new DataSet();
for (int k = 0; k < SheetCount; k++)
{
HSSFSheet sheet = (HSSFSheet)workbook.GetSheetAt(k);
DataTable table = new DataTable("table" + k);
HSSFRow headerRow = (HSSFRow)sheet.GetRow(0);
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
string columnName = headerRow.GetCell(i).StringCellValue;
DataColumn column = new DataColumn(columnName);
if (!table.Columns.Contains(columnName))
{
table.Columns.Add(column);
}
}
int rowCount = sheet.LastRowNum;
for (int i = (0 + 1); i <= sheet.LastRowNum; i++)
{
HSSFRow row = (HSSFRow)sheet.GetRow(i);
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
dataRow[j] = row.GetCell(j);
}
table.Rows.Add(dataRow);
}
sheet = null;
ds.Tables.Add(table);
}
workbook = null;
ExcelFileStream.Close();
return ds;
}
#endregion
