|
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using System.Data;
|
using System.IO;
|
using NPOI.SS.Util;
|
using System.Data.OleDb;
|
using System.Linq;
|
|
namespace Common
|
{
|
public class NPOIExcel
|
{
|
private string _title;
|
private string _sheetName;
|
private string _filePath;
|
|
public NPOIExcel(string title, string sheetName, string filePath)
|
{
|
this._title = title;
|
this._sheetName = sheetName;
|
this._filePath = filePath;
|
}
|
|
/// <summary>
|
/// 导出到Excel
|
/// </summary>
|
/// <param name="table"></param>
|
/// <returns></returns>
|
public bool ToExcel(DataTable table)
|
{
|
FileStream fs = new FileStream(this._filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite);
|
IWorkbook workBook = new HSSFWorkbook();
|
this._sheetName = this._sheetName.IsEmpty() ? "sheet1" : this._sheetName;
|
ISheet sheet = workBook.CreateSheet(this._sheetName);
|
|
//处理表格标题
|
IRow row = sheet.CreateRow(0);
|
row.CreateCell(0).SetCellValue(this._title);
|
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, table.Columns.Count - 1));
|
row.Height = 500;
|
|
ICellStyle cellStyle = workBook.CreateCellStyle();
|
IFont font = workBook.CreateFont();
|
font.FontName = "微软雅黑";
|
font.FontHeightInPoints = 17;
|
cellStyle.SetFont(font);
|
cellStyle.VerticalAlignment = VerticalAlignment.Center;
|
cellStyle.Alignment = HorizontalAlignment.Center;
|
row.Cells[0].CellStyle = cellStyle;
|
|
//处理表格列头
|
row = sheet.CreateRow(1);
|
for (int i = 0; i < table.Columns.Count; i++)
|
{
|
row.CreateCell(i).SetCellValue(table.Columns[i].ColumnName);
|
row.Height = 350;
|
sheet.AutoSizeColumn(i);
|
}
|
|
//处理数据内容
|
for (int i = 0; i < table.Rows.Count; i++)
|
{
|
row = sheet.CreateRow(2 + i);
|
row.Height = 250;
|
for (int j = 0; j < table.Columns.Count; j++)
|
{
|
row.CreateCell(j).SetCellValue(table.Rows[i][j].ToString());
|
sheet.SetColumnWidth(j, 256 * 15);
|
}
|
}
|
|
//写入数据流
|
workBook.Write(fs);
|
fs.Flush();
|
fs.Close();
|
|
return true;
|
}
|
|
/// <summary>
|
/// 导出到Excel
|
/// </summary>
|
/// <param name="table"></param>
|
/// <param name="title"></param>
|
/// <param name="sheetName"></param>
|
/// <returns></returns>
|
public bool ToExcel(DataTable table, string title, string sheetName, string filePath)
|
{
|
this._title = title;
|
this._sheetName = sheetName;
|
this._filePath = filePath;
|
return ToExcel(table);
|
}
|
|
public static System.Data.DataTable InputExcel(string Path)
|
{
|
System.Data.DataTable dt = null;
|
try
|
{
|
string strExtension = System.IO.Path.GetExtension(Path);
|
string strConn = "";
|
switch (strExtension)
|
{
|
case ".xls":
|
strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Path + ";" + "Extended Properties=\"Excel 8.0;HDR=yes;IMEX=1;\"";
|
break;
|
case ".xlsx":
|
strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + Path + ";" + "Extended Properties=\"Excel 12.0;HDR=yes;IMEX=1;\"";
|
//此连接可以操作.xls与.xlsx文件 (支持Excel2003 和 Excel2007 的连接字符串)
|
//"HDR=yes;"是说Excel文件的第一行是列名而不是数，"HDR=No;"正好与前面的相反。"IMEX=1 "如果列中的数据类型不一致，使用"IMEX=1"可必免数据类型冲突。
|
break;
|
default:
|
strConn = null;
|
break;
|
}
|
|
using (OleDbConnection conn = new OleDbConnection(strConn))
|
{
|
|
conn.Open();
|
System.Data.DataTable sheetDt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
|
string[] sheet = new string[sheetDt.Rows.Count];
|
for (int i = 0; i < sheetDt.Rows.Count; i++)
|
{
|
sheet[i] = sheetDt.Rows[i]["TABLE_NAME"].ToString();
|
}
|
for (int i = 0; i < sheet.Count(); i++)
|
{
|
if (sheet[i].Contains("$"))
|
{
|
string strExcel = string.Format("select * from [{0}]", sheet[i]);
|
OleDbDataAdapter myCommand = new OleDbDataAdapter(strExcel, strConn);
|
dt = new System.Data.DataTable();
|
myCommand.Fill(dt);
|
conn.Close();
|
return dt;
|
}
|
}
|
}
|
}
|
catch (System.Exception ex)
|
{
|
}
|
|
return dt;
|
}
|
}
|
}
|
