110,536
社区成员
发帖
与我相关
我的任务
分享
public static class EPPlusHelper
{
/// <summary>
/// 得到工作薄中的工作表
/// </summary>
/// <param name="FilePath">文件路径</param>
/// <param name="sheetName">表名</param>
/// <returns>worksheet</returns>
public static ExcelWorksheet readExcelToWorkSheet(string FilePath, string sheetName)
{
ExcelWorksheet worksheet;
FileInfo existingFile = new FileInfo(FilePath);
using (ExcelPackage package = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
worksheet = package.Workbook.Worksheets[sheetName];
}
return worksheet;
}
/// <summary>
/// 得到工作薄中的工作表
/// </summary>
/// <param name="FilePath">文件路径</param>
/// <param name="sheetIndex">表序号,从1开始</param>
/// <returns>ExcelWorksheet</returns>
public static ExcelWorksheet readExcelToWorkSheet(string FilePath, int sheetIndex)
{
ExcelWorksheet worksheet;
FileInfo existingFile = new FileInfo(FilePath);
try
{
using (ExcelPackage package = new ExcelPackage(existingFile))
{
// get the first worksheet in the workbook
worksheet = package.Workbook.Worksheets[sheetIndex];
}
return worksheet;
}
catch(Exception e)
{
MessageBox.Show(e.Message);
return null;
}
}
/// <summary>
/// 添加数据到excel并保存
/// </summary>
/// <param name="strTempletExcel">模板Excel文件名,不含后缀</param>
/// <param name="strTargetExcel">目标文件名,不含后缀</param>
public static void writeDataToWorkSheet( string strTargetExcel, DataTable dt)
{
if (dt.Rows.Count == 0) return;
string strOutputDir = Config.strOutputDir;
DirectoryInfo outputDir = new DirectoryInfo(strOutputDir);
FileInfo newFile = new FileInfo(outputDir.FullName + strTargetExcel + DateTime.Now.ToString("yyyyMMddhhmmss") + ".xlsx");
using (ExcelPackage pck = new ExcelPackage(newFile))
{
ExcelWorksheet ws = pck.Workbook.Worksheets.Add("Accounts");
ws.Cells["A1"].LoadFromDataTable(dt, true);
pck.Save();
}
}
/// <summary>
/// 得到DataTable
/// </summary>
/// <param name="path">路径</param>
/// <returns>DataTable</returns>
public static DataTable getDataTableFromExcel(string path)
{
using (var pck = new OfficeOpenXml.ExcelPackage())
{
using (var stream = File.OpenRead(path))
{
pck.Load(stream);
}
var ws = pck.Workbook.Worksheets.First();
DataTable tbl = new DataTable();
bool hasHeader = true; // adjust it accordingly( i've mentioned that this is a simple approach)
foreach (var firstRowCell in ws.Cells[1, 1, 1, ws.Dimension.End.Column])
{
tbl.Columns.Add(hasHeader ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
}
var startRow = hasHeader ? 2 : 1;
for (var rowNum = startRow; rowNum <= ws.Dimension.End.Row; rowNum++)
{
var wsRow = ws.Cells[rowNum, 1, rowNum, ws.Dimension.End.Column];
var row = tbl.NewRow();
// int i = 0;
foreach (var cell in wsRow)
{
if (cell.Value == null) continue;
row[cell.Start.Column - 1] = cell.Value.ToString();
}
tbl.Rows.Add(row);
}
return tbl;
}
}
}