public class ExcelHelper
{
/// <summary>
/// 导出地址以及导出文件名称
/// </summary>
/// <param name="CollumList"></param>
/// <returns></returns>
public string OutModel (List<ZhengceList> CollumList)
{
IWorkbook workbook;
string ModelFileName = DateTime.Now.ToString("yyyyMMddmm") + ".xlsx";
string path = Environment.CurrentDirectory + "\\" + ModelFileName;
workbook = new XSSFWorkbook();
//if (workbook == null) { return; }
ISheet sheet = workbook.CreateSheet("当前模板");
//表头
IRow row = sheet.CreateRow(0);
for (int i = 0; i < CollumList.Count; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(CollumList[i].Name);
}//完成创建表头
MemoryStream stream = new MemoryStream();
workbook.Write(stream);
var buf = stream.ToArray();
//保存为Excel文件
using (FileStream fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
fs.Write(buf, 0, buf.Length);
fs.Flush();
}
return path;
}
/// <summary>
/// 从选择文件导入数据库影响 三个表
/// </summary>
/// <param name="Path"></param>
public DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = new FileStream(file, FileMode.Open, FileAccess.Read))
{
//XSSFWorkbook 适用XLSX格式,HSSFWorkbook 适用XLS格式
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(fs); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(fs); } else { workbook = null; }
if (workbook == null) { return null; }
ISheet sheet = workbook.GetSheetAt(0);
//表头
IRow header = sheet.GetRow(sheet.FirstRowNum);
List<int> columns = new List<int>();
for (int i = 0; i < header.LastCellNum; i++)
{
dt.Columns.Add(new DataColumn(header.Cells[i].ToString()));//获取列名输入datatable
columns.Add(i);
}
//数据
for (int i = sheet.FirstRowNum + 1; i <= sheet.LastRowNum; i++)
{
DataRow dr = dt.NewRow();
//bool hasValue = false;
foreach (int j in columns)
{
dr[j] = sheet.GetRow(i).GetCell(j);
if (dr[j] == null && dr[j].ToString() == string.Empty)
{
dr[j] = "无";
}
}
dt.Rows.Add(dr);
}
}
return dt;
}
public void TableToExcel(DataTable dt, string file)
{
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
if (fileExt == ".xlsx") { workbook = new XSSFWorkbook(); } else if (fileExt == ".xls") { workbook = new HSSFWorkbook(); } else { workbook = null; }
if (workbook == null) { return; }
ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
//表头
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();
workbook.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();
}
}
}
Excel npoi操作方式
最新推荐文章于 2022-04-15 09:03:58 发布