using ExcelDataReader;
|
using NPOI.HSSF.UserModel;
|
using NPOI.SS.UserModel;
|
using NPOI.XSSF.UserModel;
|
using System;
|
using System.Collections;
|
using System.Collections.Generic;
|
using System.Data;
|
using System.Diagnostics;
|
using System.IO;
|
using System.Linq;
|
using System.Text;
|
using System.Threading.Tasks;
|
using System.Windows.Forms;
|
|
namespace Hydro.CommonBase
|
{
|
public class ExcelExport
|
{
|
private static void QuertExcel()
|
{
|
Process[] excels = Process.GetProcessesByName("EXCEL");
|
foreach (var item in excels)
|
{
|
item.CloseMainWindow();
|
//item.Kill();
|
}
|
}//进程查询
|
//public static string XLSSavesaCSV(string FilePath)
|
//{
|
// QuertExcel();
|
// string _NewFilePath = "";
|
|
// Microsoft.Office.Interop.Excel.Application excelApplication;
|
// Microsoft.Office.Interop.Excel.Workbooks excelWorkBooks = null;
|
// Microsoft.Office.Interop.Excel.Workbook excelWorkBook = null;
|
// Microsoft.Office.Interop.Excel.Worksheet excelWorkSheet = null;
|
|
// try
|
// {
|
// excelApplication = new Microsoft.Office.Interop.Excel.ApplicationClass();
|
// excelWorkBooks = excelApplication.Workbooks;
|
// excelWorkBook = ((Microsoft.Office.Interop.Excel.Workbook)excelWorkBooks.Open(FilePath, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value));
|
// excelWorkSheet = (Microsoft.Office.Interop.Excel.Worksheet)excelWorkBook.Worksheets[1];
|
// excelApplication.Visible = false;
|
// excelApplication.DisplayAlerts = false;
|
// _NewFilePath = FilePath.Replace(".xls", ".csv");
|
// // excelWorkSheet._SaveAs(FilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSVWindows, Missing.Value, Missing.Value, Missing.Value,Missing.Value,Missing.Value, Missing.Value, Missing.Value);
|
// excelWorkBook.SaveAs(_NewFilePath, Microsoft.Office.Interop.Excel.XlFileFormat.xlCSV, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Microsoft.Office.Interop.Excel.XlSaveAsAccessMode.xlNoChange, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
|
// QuertExcel();
|
// //ExcelFormatHelper.DeleteFile(FilePath);
|
// }
|
// catch (Exception exc)
|
// {
|
// throw new Exception(exc.Message);
|
// }
|
// QuertExcel();
|
// return _NewFilePath;
|
//}//excel转csv
|
static IWorkbook hssfworkbook;
|
/// <summary>
|
/// 使用DataTable导出数据
|
/// </summary>
|
/// <param name="dt">数据</param>
|
/// <param name="FileName">文件名</param>
|
/// <param name="msg">提示消息</param>
|
public static void DtToExcel(DataTable dt, string FileName = "", string SheetName = null, bool report = true)
|
{
|
//try
|
//{
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
SaveFileDialog dlg = new SaveFileDialog();//申明保存对话框
|
dlg.Filter = "Excel文件|*.xlsx";
|
dlg.FileName = FileName;//默认文件名称
|
if (FileName == "")
|
if (dlg.ShowDialog() == DialogResult.Cancel) return;
|
|
dlg.InitialDirectory = Directory.GetCurrentDirectory();//返回文件路径
|
string filename = dlg.FileName;//输出的文件名称
|
if (filename.Trim() == " ")//验证strFileName是否为空或值无效
|
{ return; }
|
hssfworkbook = new XSSFWorkbook();
|
ISheet sheet;
|
if (SheetName == null)
|
sheet = hssfworkbook.CreateSheet("Sheet1");
|
else
|
sheet = hssfworkbook.CreateSheet(SheetName);
|
|
int colscount = dt.Columns.Count;//定义表格内数据的列数
|
ArrayList arr = new ArrayList();//储存头信息
|
|
|
ICellStyle cstyle_Datetime = hssfworkbook.CreateCellStyle();
|
ICellStyle cstyle_Numeric = hssfworkbook.CreateCellStyle();
|
ICellStyle cstyle_String = hssfworkbook.CreateCellStyle();
|
|
IDataFormat format_Datetime = hssfworkbook.CreateDataFormat();
|
IDataFormat format_Numeric = hssfworkbook.CreateDataFormat();
|
IDataFormat format_String = hssfworkbook.CreateDataFormat();
|
|
cstyle_Datetime.DataFormat = format_Datetime.GetFormat("yyyy-MM-dd HH:mm:ss");
|
cstyle_Numeric.DataFormat = 0;
|
cstyle_String.DataFormat = 0;
|
IRow rowHeader = sheet.CreateRow(0);//创建列头数据
|
int displayColumnsCount = 0;//用于处理隐藏列头不被显示
|
|
XSSFFont hssffont = hssfworkbook.CreateFont() as XSSFFont;
|
hssffont.FontHeightInPoints = 11;
|
|
//设置单元格边框
|
for (int i = 0; i < dt.Columns.Count; i++)//生成表头信息
|
{
|
ICell cell = rowHeader.CreateCell(displayColumnsCount);
|
cell.SetCellValue(dt.Columns[i].ToString());
|
sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
|
displayColumnsCount++;
|
arr.Add(dt.Columns[i].ToString());
|
}
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
IRow irow = sheet.CreateRow(i + 1);
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
ICell cell;
|
if (dt.Columns[j].DataType == typeof(DateTime))
|
{
|
cell = irow.CreateCell(j, CellType.Numeric);
|
try { cell.SetCellValue(DateTime.Parse(dt.Rows[i][j].ToString())); cell.CellStyle = cstyle_Datetime; }
|
catch { cell.SetCellValue(dt.Rows[i][j].ToString()); }
|
}
|
else if (dt.Columns[j].DataType == typeof(double))
|
{
|
cell = irow.CreateCell(j, CellType.Numeric);
|
cell.CellStyle = cstyle_Numeric;
|
double d;
|
if (double.TryParse(dt.Rows[i][j].ToString(), out d))
|
cell.SetCellValue(d);
|
else
|
cell.SetCellValue(d.ToString());
|
}
|
else
|
{
|
cell = irow.CreateCell(j);
|
cell.CellStyle = cstyle_String;
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
}
|
using (Stream stream = File.OpenWrite(filename))
|
{
|
hssfworkbook.Write(stream);
|
if (report) MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
}
|
|
}
|
else
|
{
|
if (report) MessageBox.Show("没有数据可供导出", "提示");
|
}
|
//}
|
// catch
|
// {
|
// MessageBox.Show("操作失败", "提示");
|
// }
|
}//输出Excel
|
public static void DsToExcel(DataSet ds, string FileName = "")
|
{
|
try
|
{
|
if (ds != null)
|
{
|
#region 验证ds非空 & 保存对话框
|
SaveFileDialog dlg = new SaveFileDialog();//申明保存对话框
|
dlg.Filter = "Excel文件|*.xls";
|
dlg.FileName = FileName;//默认文件名称
|
if (dlg.ShowDialog() == DialogResult.Cancel)
|
return;
|
dlg.InitialDirectory = Directory.GetCurrentDirectory();//返回文件路径
|
string filename = dlg.FileName;//输出的文件名称
|
if (filename.Trim() == " ")//验证strFileName是否为空或值无效
|
{
|
return;
|
}
|
#endregion
|
|
hssfworkbook = new HSSFWorkbook();
|
ISheet sheet;
|
|
for (int l = 0; l < ds.Tables.Count; l++)
|
{
|
|
var dt = ds.Tables[l];
|
if (dt != null && dt.Rows.Count > 0)
|
{
|
|
if (dt.TableName == null)
|
sheet = hssfworkbook.CreateSheet("Sheet" + l.ToString());
|
else
|
sheet = hssfworkbook.CreateSheet(dt.TableName);
|
int colscount = dt.Columns.Count;//定义表格内数据的列数
|
ArrayList arr = new ArrayList();//储存头信息
|
ICellStyle cstyle = hssfworkbook.CreateCellStyle();
|
|
IRow rowHeader = sheet.CreateRow(0);//创建列头数据
|
int displayColumnsCount = 0;//用于处理隐藏列头不被显示
|
|
HSSFFont hssffont = hssfworkbook.CreateFont() as HSSFFont;
|
hssffont.FontHeightInPoints = 11;
|
|
//设置单元格边框
|
for (int i = 0; i < dt.Columns.Count; i++)//生成表头信息
|
{
|
ICell cell = rowHeader.CreateCell(displayColumnsCount);
|
cell.SetCellValue(dt.Columns[i].ToString());
|
sheet.SetColumnWidth(displayColumnsCount, 17 * 256);
|
displayColumnsCount++;
|
arr.Add(dt.Columns[i].ToString());
|
}
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
IRow irow = sheet.CreateRow(i + 1);
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
ICell cell = irow.CreateCell(j);
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
|
}
|
|
|
}
|
|
using (Stream stream = File.OpenWrite(filename))
|
{
|
hssfworkbook.Write(stream);
|
MessageBox.Show(filename + "\n\n导出完毕! ", "提示 ", MessageBoxButtons.OK, MessageBoxIcon.Information);
|
}
|
|
}
|
else
|
{
|
MessageBox.Show("没有数据可供导出", "提示");
|
}
|
}
|
catch
|
{
|
MessageBox.Show("操作失败", "提示");
|
}
|
}//DataSet输出Excel
|
|
|
}
|
|
public class ExcelReader
|
{
|
|
|
|
public static DataTable ExcelToDt(string filePath = null, Boolean firstRowNames = true, object sheetTag = null, int RowLimit = -1)
|
|
{
|
if (filePath == null)
|
{
|
OpenFileDialog ofd = new OpenFileDialog();
|
ofd.Filter = "Excel文件(*.xls;*.xlsx;*.csv)|*.xls;*.xlsx;*.csv|所有文件|*.*";
|
ofd.ValidateNames = true;
|
ofd.CheckPathExists = true;
|
ofd.CheckFileExists = true;
|
if (ofd.ShowDialog() == DialogResult.OK)
|
{
|
filePath = ofd.FileName;
|
//其他代码
|
}
|
else
|
{
|
return null;
|
}
|
var extension = Path.GetExtension(filePath).ToLower();
|
|
if (filePath.Length <= 0 || !(extension == ".xls" || extension == ".xlsx" || extension == ".csv"))
|
{
|
MessageBox.Show("文件名称错误或者格式错误");
|
return null;
|
}
|
int r = 0;
|
|
}
|
if (sheetTag == null)
|
sheetTag = 0;
|
var ds = ExcelToDs(filePath, firstRowNames);
|
if (sheetTag.GetType() == typeof(int))
|
return ds.Tables[(int)sheetTag];
|
else
|
if (sheetTag.GetType() == typeof(string))
|
{
|
for (int i = 0; i < ds.Tables.Count; i++)
|
{
|
if (sheetTag != null && (string)sheetTag != ds.Tables[i].TableName)
|
continue;
|
return ds.Tables[i];
|
}
|
}
|
return null;
|
|
|
|
}
|
/// <summary>
|
/// 根据Excel返回DataSet
|
/// </summary>
|
/// <param name="filePath">Excel文件地址</param>
|
/// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
|
/// <returns>DataSet</returns>
|
public static DataSet ExcelToDs(string filePath = null, Boolean firstRowNames = true)
|
{
|
DataSet ds;
|
var extension = Path.GetExtension(filePath).ToLower();
|
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
|
{
|
var sw = new Stopwatch();
|
sw.Start();
|
IExcelDataReader reader = null;
|
if (extension == ".xls")
|
{
|
reader = ExcelReaderFactory.CreateBinaryReader(stream);
|
}
|
else if (extension == ".xlsx")
|
{
|
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
|
}
|
else if (extension == ".csv")
|
{
|
reader = ExcelReaderFactory.CreateCsvReader(stream);
|
}
|
|
if (reader == null)
|
return null;
|
|
var openTiming = sw.ElapsedMilliseconds;
|
// reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
|
using (reader)
|
{
|
//ds = reader.AsDataSet(new ExcelDataSetConfiguration()
|
//{
|
// UseColumnDataType = true,
|
// ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
|
// {
|
// UseHeaderRow = firstRowNames
|
// }
|
//});
|
ds = reader.AsDataSet(new ExcelDataSetConfiguration()
|
{
|
UseColumnDataType = true,
|
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
|
{
|
UseHeaderRow = firstRowNames
|
}
|
});
|
}
|
}
|
|
|
return ds;
|
|
}
|
|
public static DataTable GetSheetInfo(string filePath = null, Boolean firstRowNames = true)
|
{
|
DataTable dt;
|
var extension = Path.GetExtension(filePath).ToLower();
|
using (var stream = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
|
{
|
var sw = new Stopwatch();
|
sw.Start();
|
IExcelDataReader reader = null;
|
if (extension == ".xls")
|
{
|
reader = ExcelReaderFactory.CreateBinaryReader(stream);
|
}
|
else if (extension == ".xlsx")
|
{
|
reader = ExcelReaderFactory.CreateOpenXmlReader(stream);
|
}
|
else if (extension == ".csv")
|
{
|
reader = ExcelReaderFactory.CreateCsvReader(stream);
|
}
|
|
if (reader == null)
|
return null;
|
|
var openTiming = sw.ElapsedMilliseconds;
|
// reader.IsFirstRowAsColumnNames = firstRowNamesCheckBox.Checked;
|
using (reader)
|
{
|
|
dt = reader.AsDataSet(new ExcelDataSetConfiguration()
|
{
|
UseColumnDataType = true,
|
ConfigureDataTable = (tableReader) => new ExcelDataTableConfiguration()
|
{
|
UseHeaderRow = firstRowNames
|
}
|
}).Tables[0];
|
}
|
}
|
|
|
return dt;
|
|
}
|
|
#region 历史
|
/// <summary>
|
/// 根据Excel和Sheet返回DataTable
|
/// </summary>
|
|
/// <param name="sheetIndex">Sheet索引</param>
|
/// <param name="T"> 0是HSSF，1是XSSF
|
/// <returns>DataTable</returns>
|
private static DataTable GetDt(int sheetIndex, IWorkbook fileWorkbook, int T)
|
{
|
Random rd = new Random();
|
|
DataTable dt = new DataTable();
|
ISheet sheet = fileWorkbook.GetSheetAt(sheetIndex);
|
//获取数据类型
|
int tryTime = 3;
|
|
List<Type> Types = new List<Type>();
|
for (int ts = 0; ts < tryTime; ts++)
|
{
|
IRow firstDataRow;
|
if (ts == 0)
|
firstDataRow = sheet.GetRow(sheet.FirstRowNum + 1);
|
else
|
firstDataRow = sheet.GetRow(rd.Next(sheet.FirstRowNum, sheet.PhysicalNumberOfRows));
|
Boolean HashValue = false;
|
for (int j = 0; j < firstDataRow.LastCellNum; j++)
|
{
|
if (firstDataRow.GetCell(j) != null) HashValue = true;
|
object obj;
|
if (T == 0)
|
obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as HSSFCell);
|
else
|
obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as XSSFCell);
|
|
if (ts == 0)
|
if (obj == DBNull.Value || obj == null) Types.Add(typeof(string));
|
else
|
{
|
Types.Add(obj.GetType());
|
|
}
|
|
else
|
if (obj == DBNull.Value || obj == null || obj.ToString() == "")
|
continue;
|
else
|
if (Types[j] != obj.GetType())
|
Types[j] = typeof(string);
|
}
|
if (!HashValue) ts--;
|
}
|
//表头
|
IRow header = sheet.GetRow(sheet.FirstRowNum);
|
List<int> columns = new List<int>();
|
for (int j = 0; j < header.LastCellNum; j++)
|
{
|
object obj;
|
if (T == 0)
|
obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell, typeof(string));
|
else
|
obj = GetValueTypeForXLS(header.GetCell(j) as XSSFCell, typeof(string));
|
if (obj == null || obj.ToString() == string.Empty)
|
{
|
dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
|
}
|
else
|
if (j < Types.Count)
|
dt.Columns.Add(new DataColumn(obj.ToString(), Types[j] == typeof(DateTime?) ? typeof(DateTime) : Types[j]));
|
else
|
dt.Columns.Add(new DataColumn(obj.ToString()));
|
|
|
columns.Add(j);
|
}
|
//数据
|
IEnumerator rows = sheet.GetEnumerator();
|
int RowIndex = sheet.FirstRowNum + 1;
|
while (rows.MoveNext())
|
{
|
|
DataRow dr = dt.NewRow();
|
bool hasValue = false;
|
foreach (int K in columns)
|
{
|
if (T == 0)
|
dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as HSSFCell, Types[K]);//应改写，强制按类型读取
|
else
|
dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as XSSFCell, Types[K]);
|
|
if (dr[K] != null && dr[K].ToString() != string.Empty)
|
{
|
hasValue = true;
|
}
|
}
|
if (hasValue)
|
{
|
dt.Rows.Add(dr);
|
}
|
RowIndex++;
|
if (RowIndex >= sheet.PhysicalNumberOfRows) break;
|
}
|
|
return dt;
|
}
|
/// <summary>
|
/// 根据Excel返回DataSet
|
/// </summary>
|
/// <param name="filePath">Excel文件地址</param>
|
/// <param name="sheetIndex">Sheet索引，可选，默认返回所有Sheet</param>
|
/// <returns>DataSet</returns>
|
private static DataSet ExcelToDs0(string filePath, object sheetTag = null)
|
{
|
DataSet ds = new DataSet();
|
IWorkbook fileWorkbook;
|
int T = 1;
|
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read))
|
{
|
if (filePath.Last() == 's')
|
{
|
try
|
{
|
fileWorkbook = new HSSFWorkbook(fs);
|
T = 0;
|
}
|
catch (Exception ex)
|
{
|
try
|
{
|
fileWorkbook = new XSSFWorkbook(fs);
|
T = 1;
|
}
|
catch
|
{
|
throw (ex);
|
}
|
}
|
}
|
else
|
{
|
|
try
|
{
|
fileWorkbook = new XSSFWorkbook(fs);
|
T = 1;
|
}
|
catch
|
{
|
fileWorkbook = new HSSFWorkbook(fs);
|
T = 0;
|
}
|
}
|
}
|
|
if (sheetTag != null)//单个sheet
|
for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
|
{
|
|
if (sheetTag.GetType() == typeof(int))
|
{
|
if (sheetTag != null && (int)sheetTag != i)
|
continue;
|
var dt = GetDt(i, fileWorkbook, T);
|
ds.Tables.Add(dt);
|
}
|
else
|
if (sheetTag.GetType() == typeof(string))
|
{
|
if (sheetTag != null && (string)sheetTag != fileWorkbook.GetSheetAt(i).SheetName)
|
continue;
|
var dt = GetDt(i, fileWorkbook, T);
|
ds.Tables.Add(dt);
|
}
|
|
|
}
|
else//多个sheet
|
{
|
for (int i = 0; i < fileWorkbook.NumberOfSheets; i++)
|
{
|
var dt = GetDt(i, fileWorkbook, T);
|
ds.Tables.Add(dt);
|
}
|
}
|
|
return ds;
|
}
|
|
|
/// <summary>
|
/// 根据Excel和Sheet返回DataTable
|
/// </summary>
|
/// <param name="filePath">Excel文件地址</param>
|
/// <param name="sheetName">Sheet名称</param>
|
/// <returns>DataTable</returns>
|
private static DataTable ExcelToDt0(string filePath = null, Boolean firstRowNames = true, object sheetTag = null)
|
|
{
|
if (filePath == null)
|
{
|
OpenFileDialog ofd = new OpenFileDialog();
|
ofd.Filter = "Excel文件(*.xls;*.xlsx)|*.xls;*.xlsx|所有文件|*.*";
|
ofd.ValidateNames = true;
|
ofd.CheckPathExists = true;
|
ofd.CheckFileExists = true;
|
if (ofd.ShowDialog() == DialogResult.OK)
|
{
|
filePath = ofd.FileName;
|
//其他代码
|
|
|
}
|
else
|
{
|
return null;
|
}
|
var strFileNme = filePath;
|
//strFileNme.LastIndexOf(".");
|
|
if (strFileNme.Length <= 0 || !(strFileNme.Substring(strFileNme.LastIndexOf(".")).ToLower() == ".xls" || strFileNme.Substring(strFileNme.LastIndexOf(".")).ToLower() == ".xlsx"))
|
{
|
MessageBox.Show("文件名称错误或者格式错误");
|
return null;
|
}
|
int r = 0;
|
|
}
|
if (sheetTag == null)
|
sheetTag = 0;
|
return ExcelToDs(filePath, firstRowNames).Tables[0];
|
}
|
|
#endregion 历史
|
#region nullable GetDt
|
|
///// <summary>
|
///// 根据Excel和Sheet返回DataTable
|
///// </summary>
|
|
///// <param name="sheetIndex">Sheet索引</param>
|
///// <param name="T"> 0是HSSF，1是XSSF
|
///// <returns>DataTable</returns>
|
//private static DataTable GetDt(int sheetIndex, IWorkbook fileWorkbook, int T)
|
//{
|
// Random rd = new Random();
|
|
// DataTable dt = new DataTable();
|
// ISheet sheet = fileWorkbook.GetSheetAt(sheetIndex);
|
// //获取数据类型
|
// int tryTime = 3;
|
|
// List<Type> Types = new List<Type>();
|
// for (int ts = 0; ts < tryTime; ts++)
|
// {
|
// IRow firstDataRow;
|
// if (ts == 0)
|
// firstDataRow = sheet.GetRow(sheet.FirstRowNum + 1);
|
// else
|
// firstDataRow = sheet.GetRow(rd.Next(sheet.FirstRowNum, sheet.PhysicalNumberOfRows));
|
// Boolean HashValue = false;
|
// for (int j = 0; j < firstDataRow.LastCellNum; j++)
|
// {
|
// if (firstDataRow.GetCell(j) != null) HashValue = true;
|
// object obj;
|
// if (T == 0)
|
// obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as HSSFCell);
|
// else
|
// obj = GetValueTypeForXLS(firstDataRow.GetCell(j) as XSSFCell);
|
|
// if (ts == 0)
|
// if (obj == DBNull.Value || obj == null) Types.Add(typeof(string));
|
// else
|
// {
|
// Types.Add(GetNullType(obj));
|
|
// }
|
|
// else
|
// if (obj == DBNull.Value || obj == null || obj.ToString() == "")
|
// continue;
|
// else
|
// if (Types[j] != GetNullType(obj))
|
// Types[j] = typeof(string);
|
// }
|
// if (!HashValue) ts--;
|
// }
|
// //表头
|
// IRow header = sheet.GetRow(sheet.FirstRowNum);
|
// List<int> columns = new List<int>();
|
// for (int j = 0; j < header.LastCellNum; j++)
|
// {
|
// object obj;
|
// if (T == 0)
|
// obj = GetValueTypeForXLS(header.GetCell(j) as HSSFCell, typeof(string));
|
// else
|
// obj = GetValueTypeForXLS(header.GetCell(j) as XSSFCell, typeof(string));
|
// if (obj == null || obj.ToString() == string.Empty)
|
// {
|
// dt.Columns.Add(new DataColumn("Columns" + j.ToString()));
|
// }
|
// else
|
// if (j < Types.Count)
|
// dt.Columns.Add(new DataColumn(obj.ToString(), Types[j] == typeof(DateTime?) ? typeof(DateTime) : Types[j]));
|
// else
|
// dt.Columns.Add(new DataColumn(obj.ToString()));
|
|
|
// columns.Add(j);
|
// }
|
// //数据
|
// IEnumerator rows = sheet.GetEnumerator();
|
// int RowIndex = sheet.FirstRowNum + 1;
|
// while (rows.MoveNext())
|
// {
|
|
// DataRow dr = dt.NewRow();
|
// bool hasValue = false;
|
// foreach (int K in columns)
|
// {
|
// if (T == 0)
|
// dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as HSSFCell, Types[K]);//应改写，强制按类型读取
|
// else
|
// dr[K] = GetValueTypeForXLS(sheet.GetRow(RowIndex).GetCell(K) as XSSFCell, Types[K]);
|
|
// if (dr[K] != null && dr[K].ToString() != string.Empty)
|
// {
|
// hasValue = true;
|
// }
|
// }
|
// if (hasValue)
|
// {
|
// dt.Rows.Add(dr);
|
// }
|
// RowIndex++;
|
// if (RowIndex >= sheet.PhysicalNumberOfRows) break;
|
// }
|
|
// return dt;
|
//}
|
#endregion
|
|
/// <summary>
|
/// 根据DataTable导出Excel
|
/// </summary>
|
/// <param name="dt">DataTable</param>
|
/// <param name="file">保存地址</param>
|
public static void GetExcelByDataTable(DataTable dt, string file)
|
{
|
DataSet ds = new DataSet();
|
ds.Tables.Add(dt);
|
GetExcelByDataSet(ds, file);
|
}
|
|
/// <summary>
|
/// 根据DataSet导出Excel
|
/// </summary>
|
/// <param name="ds">DataSet</param>
|
/// <param name="file">保存地址</param>
|
public static void GetExcelByDataSet(DataSet ds, string file)
|
{
|
IWorkbook fileWorkbook = new HSSFWorkbook();
|
int index = 0;
|
foreach (DataTable dt in ds.Tables)
|
{
|
index++;
|
ISheet sheet = fileWorkbook.CreateSheet("Sheet" + index);
|
|
//表头
|
IRow row = sheet.CreateRow(0);
|
for (int i = 0; i < dt.Columns.Count; i++)
|
{
|
ICell cell = row.CreateCell(i);
|
cell.SetCellValue(dt.Columns[i].ColumnName);
|
}
|
|
//数据
|
for (int i = 0; i < dt.Rows.Count; i++)
|
{
|
IRow row1 = sheet.CreateRow(i + 1);
|
for (int j = 0; j < dt.Columns.Count; j++)
|
{
|
ICell cell = row1.CreateCell(j);
|
cell.SetCellValue(dt.Rows[i][j].ToString());
|
}
|
}
|
}
|
|
//转为字节数组
|
MemoryStream stream = new MemoryStream();
|
fileWorkbook.Write(stream);
|
var buf = stream.ToArray();
|
|
//保存为Excel文件
|
using (FileStream fs = new FileStream(file, FileMode.Create, FileAccess.Write))
|
{
|
fs.Write(buf, 0, buf.Length);
|
fs.Flush();
|
}
|
}
|
|
/// <summary>
|
/// 根据单元格将内容返回为对应类型的数据
|
/// </summary>
|
/// <param name="cell">单元格</param>
|
/// <returns>数据</returns>
|
private static object GetValueTypeForXLS(HSSFCell cell, Type type = null)
|
{
|
|
DateTime t = DateTime.FromOADate(0);
|
if (cell == null)
|
return DBNull.Value;
|
if (type != null)
|
{
|
if (type == typeof(bool?))
|
return (Boolean?)cell.BooleanCellValue;
|
if (type == typeof(string))
|
return cell.ToString();
|
if (type == typeof(DateTime?))
|
return TimeExTreat(cell.DateCellValue);
|
if (type == typeof(double?))
|
return (double?)cell.NumericCellValue;
|
}
|
switch (cell.CellType)
|
{
|
case CellType.Blank: //BLANK:
|
return DBNull.Value;
|
case CellType.Boolean: //BOOLEAN:
|
return (Boolean?)cell.BooleanCellValue;
|
case CellType.String: //STRING:
|
if (DateTime.TryParse(cell.StringCellValue, out t))
|
return t;
|
else
|
return cell.StringCellValue;
|
case CellType.Numeric: //NUMERIC:
|
if (DateUtil.IsCellDateFormatted(cell))
|
return TimeExTreat(cell.DateCellValue);
|
else
|
return (double?)cell.NumericCellValue;
|
|
case CellType.Error: //ERROR:
|
return cell.ErrorCellValue;
|
case CellType.Formula: //FORMULA:
|
default:
|
return "=" + cell.CellFormula;
|
}
|
}
|
/// <summary>
|
/// 根据单元格将内容返回为对应类型的数据
|
/// </summary>
|
/// <param name="cell">单元格</param>
|
/// <returns>数据</returns>
|
private static object GetValueTypeForXLS(XSSFCell cell, Type type = null)
|
{
|
DateTime t = DateTime.FromOADate(0);
|
if (cell == null)
|
return DBNull.Value;
|
if (type != null)
|
{
|
if (type == typeof(bool?))
|
return (Boolean?)cell.BooleanCellValue;
|
if (type == typeof(string))
|
return cell.ToString();
|
if (type == typeof(DateTime?))
|
return TimeExTreat(cell.DateCellValue);
|
if (type == typeof(double?))
|
return (double?)cell.NumericCellValue;
|
}
|
switch (cell.CellType)
|
{
|
case CellType.Blank: //BLANK:
|
return DBNull.Value;
|
case CellType.Boolean: //BOOLEAN:
|
return (Boolean?)cell.BooleanCellValue;
|
case CellType.String: //STRING:
|
if (DateTime.TryParse(cell.StringCellValue, out t))
|
return t;
|
else
|
return cell.StringCellValue;
|
case CellType.Numeric: //NUMERIC:
|
if (DateUtil.IsCellDateFormatted(cell))
|
return TimeExTreat(cell.DateCellValue);
|
else
|
return (double?)cell.NumericCellValue;
|
|
case CellType.Error: //ERROR:
|
return cell.ErrorCellValue;
|
case CellType.Formula: //FORMULA:
|
default:
|
return "=" + cell.CellFormula;
|
}
|
}
|
private static DateTime? TimeExTreat(DateTime d)
|
{
|
if (d.Millisecond >= 500) d = d.AddSeconds(1);
|
return (DateTime?)d;
|
}
|
private static Type GetNullType(object obj)
|
{
|
Type Tobject = obj.GetType();
|
if (Tobject == typeof(double)) Tobject = typeof(double?);
|
if (Tobject == typeof(DateTime)) Tobject = typeof(DateTime?);
|
return Tobject;
|
}
|
private static Type GetNullTypeOf(object obj)
|
{
|
Type Tobject = obj.GetType();
|
if (Tobject == typeof(double)) Tobject = typeof(double?);
|
if (Tobject == typeof(DateTime)) Tobject = typeof(DateTime?);
|
return Tobject;
|
}
|
}
|
|
}
|
