NPOI 操作笔记
2024-08-25 21:43:13
public static class ExcelUtil
{
// Methods
public static DataTable GetDataTable(Stream stream)
{
HSSFWorkbook workbook;
try
{
workbook = new HSSFWorkbook(stream);
}
catch (Exception exception)
{
throw exception;
}
ISheet sheetAt = workbook.GetSheetAt();
IEnumerator rowEnumerator = sheetAt.GetRowEnumerator();
DataTable table = new DataTable();
IRow row = sheetAt.GetRow();
if (row != null)
{
for (int j = ; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
table.Columns.Add("cell" + j.ToString());
}
else
{
table.Columns.Add(cell.ToString());
}
}
}
int count = table.Columns.Count;
for (int i = ; rowEnumerator.MoveNext(); i++)
{
if (i > )
{
IRow current = (HSSFRow) rowEnumerator.Current;
DataRow row3 = table.NewRow();
for (int k = ; k < count; k++)
{
ICell cell2 = current.GetCell(k);
if (cell2 == null)
{
row3[k] = null;
}
else
{
row3[k] = cell2.ToString();
}
}
table.Rows.Add(row3);
}
}
return table;
} public static DataTable GetDataTable(string filePath)
{
HSSFWorkbook workbook;
try
{
using (FileStream stream = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
workbook = new HSSFWorkbook(stream);
}
}
catch (Exception exception)
{
throw exception;
}
ISheet sheetAt = workbook.GetSheetAt();
IEnumerator rowEnumerator = sheetAt.GetRowEnumerator();
DataTable table = new DataTable();
IRow row = sheetAt.GetRow();
if (row != null)
{
for (int j = ; j < row.LastCellNum; j++)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
table.Columns.Add("cell" + j.ToString());
}
else
{
table.Columns.Add(cell.ToString());
}
}
}
int count = table.Columns.Count;
for (int i = ; rowEnumerator.MoveNext(); i++)
{
if (i > )
{
IRow current = (HSSFRow) rowEnumerator.Current;
DataRow row3 = table.NewRow();
for (int k = ; k < count; k++)
{
ICell cell2 = current.GetCell(k);
if (cell2 == null)
{
row3[k] = null;
}
else
{
row3[k] = cell2.ToString();
}
}
table.Rows.Add(row3);
}
}
return table;
} /// <summary>
/// 导入数据到excel文件
/// </summary>
/// <param name="data">待导入的数据</param>
/// <param name="fileName">文件路径</param>
/// <param name="sheetName">excel表名</param>
/// <param name="hiddenColmnIndexs">隐藏列索引</param>
/// <param name="header">列头字典；原始列头=>重命名列名</param>
/// <param name="redHeaderClomnIndexs">标记红色加粗的列索引</param>
/// <param name="comments">列头批注字典；列名=>批注内容</param>
/// <returns></returns>
public static string SetDataTableToExcel(DataTable data,string fileName, string sheetName, int[] hiddenColmnIndexs = null, Dictionary<string, string> header = null,int[] redHeaderClomnIndexs = null,Dictionary<string,string> comments= null)
{
int num4 = ;
int num = ;
int column = ;
int rownum = ;
ISheet sheet = null;
IWorkbook workbook = new HSSFWorkbook();
//todo:tans
var stream = new FileStream(fileName ,FileMode.OpenOrCreate,FileAccess.ReadWrite); try
{
sheet = workbook.CreateSheet(sheetName); IRow row = sheet.CreateRow();
column = ;
int maxCount = Math.Max(header!=null ? header.Count : , data.Columns.Count); IDrawing patr1 = sheet.CreateDrawingPatriarch();// as HSSFPatriarch;
//创建列头
while (column < data.Columns.Count)
{
var col = data.Columns[column];
var colName = col.ColumnName;
ICell cell = row.GetCell(column);
if (header!=null && header.ContainsKey(colName))
{
string temp = header[colName];//列重命名
header.Remove(colName);
cell = row.CreateCell(column);
cell.SetCellValue(temp);
}
//创建批注
colName = cell.StringCellValue;
if (comments!=null && comments.ContainsKey(colName))
{
var commentStr = comments[colName];
IComment comment1 = patr1.CreateCellComment(new HSSFClientAnchor(, , , , , , , ));// as HSSFComment;
comment1.String = new HSSFRichTextString(commentStr);
cell.CellComment = comment1;
}
column++;
}
//附加列头
int colCount = data.Columns.Count;
foreach (var colName in header.Keys)
{
row.CreateCell(colCount).SetCellValue(header[colName]);
colCount++;
}
//设置列头样式
redHeaderClomnIndexs = redHeaderClomnIndexs ?? new int[]{};
for (int i = , l=, rl= redHeaderClomnIndexs.Length; i < row.Cells.Count; i++,l++)
{
var cell = row.GetCell(i);
if (cell==null)
{
continue;
}
//设置字体
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
if (l < rl)
{
int index = redHeaderClomnIndexs[l];
cell = row.GetCell(index);
font.Color = (short)FontColor.RED;
}
//设置样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.CENTER;
style.SetFont(font); cell.CellStyle = style;
}
//创建数据列，并填充数据
rownum = ;
for (num = ; num < data.Rows.Count; num++)
{
IRow row2 = sheet.CreateRow(rownum);
for (column = ; column < data.Columns.Count; column++)
{
row2.CreateCell(column).SetCellValue(data.Rows[num][column].ToString());
}
rownum++;
}
//设置隐藏列
if (hiddenColmnIndexs != null && hiddenColmnIndexs.Length <= column)
{
for (int i = , l = hiddenColmnIndexs.Length; i < l; i++)
{
sheet.SetColumnHidden(hiddenColmnIndexs[i], true);
}
}
//当创建完毕所有cell，设置居中才有效
for (int i = ; i < row.Cells.Count; i++)
{
sheet.AutoSizeColumn(i);
}
workbook.Write(stream);
}
catch (Exception ex)
{
return null;
}
finally
{
stream.Close();
}
return fileName;
}
}
最新文章
- Kinect开发资源汇总
- d3.js读书笔记-2
- angular表单
- C# 获取本机指定类型指定网卡的Ip地址
- mysql 数据库服务中的应用程序
- [转载]堆排序（HeapSort） Java实现
- 将yyyyMMdd HH:mm:ss格式的时间转换成时间类型
- CG&Game资源（转）
- 如何选择合适的PHP开发框架
- Kotlin 初窥门径[1]:基础概念
- Vue.directive 自定义指令的问题
- 谈谈线上CPU100%排查套路
- 【代码笔记】Web-ionic-index创建侧边栏
- Fiddler抓包使用教程-扫盲篇
- Swift学习笔记 - 变量和常量
- synchronized-锁重入
- EL表达式从数组 Map List集合取值
- Java多线程 - 控制线程
- matplotlib学习笔记（三）
- 怎么查找Linux系统调用