using System;
using System.Collections.Generic;
using NPOI;
using NPOI.HPSF;
using NPOI.HSSF;
using NPOI.HSSF.UserModel;
using NPOI.POIFS;
using NPOI.Util;
using System.Text;
using System.IO;
using System.Data;
using System.Data.OleDb;
using NPOI.SS.UserModel; namespace Business
{
public class CExcelOut
{
/// <summary>导出EXCEL表</summary>
/// <param name="table">table表</param>
/// <returns>二进制流</returns>
public MemoryStream GetExecOut(DataTable table)
{
if (table == null)
{
return null;
}
List<DataTable> listTable = new List<DataTable>();
listTable.Add(table);
return GetExecOut(listTable);
}
/// <summary>导入多个EXCEL表</summary>
/// <param name="listTable">表集合</param>
/// <returns>二进制流</returns>
public MemoryStream GetExecOut(List<DataTable> listTable)
{
try
{
if (listTable == null || listTable.Count == )
{
return null;
}
Dictionary<string, DataTable> rowTable = new Dictionary<string, DataTable>();
for (int i = ; i < listTable.Count; i++)
{
rowTable.Add("sheet" + (i + ), listTable[i]);
}
return GetExecOut(rowTable);
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
}
/// <summary>导入多个EXCEL表 键是表名 值是table表</summary>
/// <param name="listTable">键值对集合</param>
/// <returns>二进制流</returns>
public MemoryStream GetExecOut(Dictionary<string, DataTable> listTable)
{
if (listTable == null || listTable.Count == )
{
return null;
}
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
try
{
foreach (KeyValuePair<string, DataTable> item in listTable)
{
string steerName = item.Key;
//文件名
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(steerName);
//头部
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow();
foreach (DataColumn itemColumn in item.Value.Columns)
{
headerRow.CreateCell(itemColumn.Ordinal).SetCellValue(itemColumn.ColumnName);
}
int rowIndex = ;
foreach (DataRow itemRow in item.Value.Rows)
{
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn itemColumn in item.Value.Columns)
{
dataRow.CreateCell(itemColumn.Ordinal).
SetCellValue(itemRow[itemColumn.ColumnName].ToString());
}
rowIndex++;
}
}
workbook.Write(ms);
ms.Flush();
ms.Position = ;
return ms; }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
finally
{
workbook = null;
}
}
/// <summary>把流转换为文件</summary>
/// <param name="PathUrl">文件路径，如果文件存在则覆盖,不存在创建</param>
/// <param name="ms">IO流</param>
public void ExeclOut(string PathUrl, Stream ms)
{
try
{
if (PathUrl.Length == || ms == null)
{
return;
}
FileStream file = new FileStream(PathUrl, FileMode.Create);
if (ms.CanRead)
{
int lentgth = ;
byte[] by = new byte[lentgth];
lentgth = ms.Read(by, , lentgth);
while (lentgth != )
{
file.Write(by, , lentgth);
lentgth = ms.Read(by, , lentgth);
}
file.Close();
}
}
catch (Exception ex)
{
Console.WriteLine(ex.Message);
}
} /// <summary>Excel表变成dataTable</summary>
/// <param name="filePath">Excel表格路径</param>
/// <param name="sheetName">查询表名</param>
/// <returns>数据集合</returns>
public DataTable GetTop1ExcelData(string filePath, string sheetName)
{
DataSet ds = new DataSet();
try
{ string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended properties=Excel 8.0;";
string sql = "select * from [" + sheetName + "$]";
using (OleDbConnection con = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adapt = new OleDbDataAdapter(cmd);
adapt.Fill(ds);
if (ds != null && ds.Tables.Count > )
{
return ds.Tables[];
}
return null;
}
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
return null;
}
} /// <summary>Excel表变成dataTable</summary>
/// <param name="filePath">Excel表格路径</param>
/// <param name="sheetName">查询表名</param>
/// <returns>数据集合</returns>
public DataTable GetExcelData(string filePath, string sheetName)
{
DataSet ds = new DataSet();
try
{
string connectionString = "provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended properties='Excel 8.0;HDR=NO;IMEX=1'";
string sql = "select * from [" + sheetName + "$]";
using (OleDbConnection con = new OleDbConnection(connectionString))
{
OleDbCommand cmd = new OleDbCommand(sql, con);
OleDbDataAdapter adapt = new OleDbDataAdapter(cmd);
adapt.Fill(ds);
if (ds != null && ds.Tables.Count > )
{
return ds.Tables[];
}
return null;
}
}
catch (Exception exp)
{
Console.WriteLine(exp.Message);
return null;
}
}
/// <summary>
/// Excel表变成dataTable
/// </summary>
/// <param name="filePath">Excel表格路径</param>
/// <returns>数据集合</returns>
public DataTable GetExcelData(string filePath)
{
DataTable table = new DataTable();
try
{
FileStream fileStream = new FileStream(filePath, FileMode.Open, FileAccess.Read);
HSSFWorkbook hssfWorkbook = new HSSFWorkbook(fileStream); ISheet iSheet = hssfWorkbook.GetSheetAt();
if (iSheet != null)
{
IRow iRow = iSheet.GetRow();
int cellCount = iRow.LastCellNum; for (int i = iRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(iRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
} int rowCount = iSheet.LastRowNum;
for (int i = (iSheet.FirstRowNum + ); i < iSheet.LastRowNum; i++)
{
IRow row = iSheet.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);
}
} fileStream.Flush();
fileStream.Dispose();
fileStream.Close();
fileStream = null; hssfWorkbook.Dispose();
hssfWorkbook = null;
iSheet.Dispose();
iSheet = null;
}
catch (Exception exp)
{ }
finally
{
}
return table;
}
/// <summary>
/// App设置导出Excel模板
/// </summary>
/// <param name="tbData"></param>
/// <param name="headTb"></param>
/// <param name="steerName"></param>
/// <returns></returns>
public MemoryStream GetExecOut(DataTable tbData, DataTable headTb, string steerName)
{
if (tbData == null || tbData.Rows.Count == )
{
return null;
}
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
try
{
//文件名
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet(steerName);
ICellStyle style = GetStyle(workbook);
ICellStyle styleLeft = GetStyle(workbook, HorizontalAlignment.LEFT);
ICellStyle styleSize = GetStyle(workbook, ); int rowIndex = ;
//头部
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
ICell cell = headerRow.CreateCell(, CellType.STRING);
cell.SetCellValue("分层标电子水准测量记录手簿");
cell.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, , ));
//设置单元格的高度实际是设置其所在行高，所以要在单元格所在行上设置行高，行高设置数值好像是像素点的1/20，所以*20以便达到设置效果；
//设置单元格的宽度实际上是设置其所在列宽，所以要在单元格所在列上设置（列的设置在工作表上），宽度数值好像是字符的1/256，所以*256以便达到设置效果。
for (int i = ; i < ; i++)
{
sheet.SetColumnWidth(i, * );
}
headerRow.Height = * ;
rowIndex++;
foreach (DataRow itemRow in headTb.Rows)
{
headerRow = sheet.CreateRow(rowIndex);
cell = headerRow.CreateCell(, CellType.STRING);
cell.SetCellValue(itemRow["Columns1"].ToString());
cell.CellStyle = style; cell = headerRow.CreateCell(, CellType.STRING);
cell.SetCellValue(itemRow["Columns2"].ToString());
cell.CellStyle = styleLeft; cell = headerRow.CreateCell(, CellType.STRING);
cell.CellStyle = style; cell = headerRow.CreateCell(, CellType.STRING);
cell.CellStyle = style; cell = headerRow.CreateCell(, CellType.STRING);
cell.SetCellValue(itemRow["Columns3"].ToString());
cell.CellStyle = style; cell = headerRow.CreateCell(, CellType.STRING);
cell.SetCellValue(itemRow["Columns4"].ToString());
cell.CellStyle = styleLeft; cell = headerRow.CreateCell(, CellType.STRING);
cell.CellStyle = style; cell = headerRow.CreateCell(, CellType.STRING);
cell.CellStyle = style; headerRow.Height = * ;
//跨行 跨列 起始行 终止行 起始列 终止列
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, , ));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(rowIndex, rowIndex, , ));
rowIndex++;
}
NPOI.SS.UserModel.IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = * ;
sheet.SetColumnWidth(rowIndex, * );
cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("测回编号");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("标点编号");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("中丝读数1");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("中丝读数2");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("中丝读数3");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("距离");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("中丝读数均值");
cell.CellStyle = styleSize; cell = dataRow.CreateCell(, CellType.STRING);
cell.SetCellValue("高差");
cell.CellStyle = styleSize; rowIndex++;
foreach (DataRow itemRow in tbData.Rows)
{
dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = * ;
foreach (DataColumn itemColumn in tbData.Columns)
{
dataRow.CreateCell(itemColumn.Ordinal, CellType.STRING).SetCellValue(itemRow[itemColumn.ColumnName].ToString());
dataRow.Cells[itemColumn.Ordinal].CellStyle = styleSize;
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = ;
return ms; }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
finally
{
workbook = null;
}
}
/// <summary>
/// App原始中间报表导出模板
/// </summary>
/// <param name="tbData"></param>
/// <returns></returns>
public MemoryStream GetExcelOut(DataTable tbData)
{
if (tbData == null || tbData.Rows.Count == )
{
return null;
}
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
try
{
//文件名
NPOI.SS.UserModel.ISheet sheet = workbook.CreateSheet("Sheet1");
ICellStyle style = GetStyle(workbook);
ICellStyle styleSize = GetStyle(workbook, ); int rowIndex = ;
//头部
NPOI.SS.UserModel.IRow headerRow = sheet.CreateRow(rowIndex);
//设置单元格的高度实际是设置其所在行高，所以要在单元格所在行上设置行高，行高设置数值好像是像素点的1/20，所以*20以便达到设置效果；
//设置单元格的宽度实际上是设置其所在列宽，所以要在单元格所在列上设置（列的设置在工作表上），宽度数值好像是字符的1/256，所以*256以便达到设置效果。
headerRow.Height = * ;
foreach (DataColumn itemColumn in tbData.Columns)
{
ICell cell = headerRow.CreateCell(itemColumn.Ordinal, CellType.STRING);
cell.SetCellValue(itemColumn.ColumnName);
cell.CellStyle = style;
//设置列宽度
sheet.SetColumnWidth(itemColumn.Ordinal, * );
} rowIndex++;
foreach (DataRow itemRow in tbData.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
dataRow.Height = * ;
foreach (DataColumn itemColumn in tbData.Columns)
{
dataRow.CreateCell(itemColumn.Ordinal, CellType.STRING).SetCellValue(itemRow[itemColumn.ColumnName].ToString());
dataRow.Cells[itemColumn.Ordinal].CellStyle = styleSize;
}
rowIndex++;
}
workbook.Write(ms);
ms.Flush();
ms.Position = ;
return ms; }
catch (Exception ex)
{
Console.WriteLine(ex.Message);
return null;
}
finally
{
workbook = null;
}
}
/// <summary>
/// 获取默认样式
/// </summary>
/// <param name="workbook"></param>
/// <returns></returns>
public ICellStyle GetStyle(HSSFWorkbook workbook)
{
return GetStyle(workbook, , HorizontalAlignment.CENTER_SELECTION);
}
/// <summary>
/// 获取样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="size">设置字体大小</param>
/// <returns></returns>
public ICellStyle GetStyle(HSSFWorkbook workbook, short size)
{
return GetStyle(workbook, size, HorizontalAlignment.CENTER_SELECTION);
}
/// <summary>
/// 获取样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="ali">设置文本居中</param>
/// <returns></returns>
public ICellStyle GetStyle(HSSFWorkbook workbook, HorizontalAlignment ali)
{
return GetStyle(workbook, , ali);
}
/// <summary>
/// 获取样式
/// </summary>
/// <param name="workbook"></param>
/// <param name="size">设置字体大小</param>
/// <param name="ali">设置文本居中</param>
/// <returns></returns>
public ICellStyle GetStyle(HSSFWorkbook workbook, short size, HorizontalAlignment ali)
{
try
{
ICellStyle style = workbook.CreateCellStyle();
//居中
style.VerticalAlignment = VerticalAlignment.CENTER;
//水平居中
style.Alignment = ali;
//设置字体
IFont font = workbook.CreateFont();
font.FontName = "微软雅黑";
//设置大小
font.FontHeightInPoints = size;
style.SetFont(font);
return style;
}
catch (Exception ex)
{
return null;
}
} }
}
NPOI.DLL下载地址 http://files.cnblogs.com/files/changeMe/NPOI.zip