前段时间朋友问我,C#操作excel的问题,代码传过来后发现是他下载的dll版本不对,之后我就上百度查了,发现这个库是有源码的。
C#读取excel 的方式有很多,有些库你必须安装office (odbc,Microsoft.Office.Interop.Excel),而有些是不需要的(npoi,epplus,exceldatareader)。
第一种方式,我在项目里用的是npoi(支持.xls,.xlsx),这种方式确实挺好用的,可以任意操作excel。我的思路是把excel文件转成datatable,然后操作datatable,最后把操作完的datatable保存成excel,如果不转成datatable,而是拿着ISheet直接操作表格,这样在记录比较多的情况下会出错(我的表有5000多条记录,我要删除不符合条件的记录,在删除完,保存的时候,发现一直报索引无效)。后边我就把它转成了datatable。
示例代码:
class Program
{
static void Main(string[] args)
{
string path = System.Environment.CurrentDirectory;
Console.WriteLine("请拖入想修改的文件。。。");
string str = Console.ReadLine();
FileInfo finfo = new FileInfo(str);
string folder = finfo.Directory.FullName;
//string pp = @"C:\Users\myth\Desktop\ExcelTool\ExcelTool\ExcelTool\bin\x64\Release\1.xls";
DataTable dt = ExcelHelper.ExcelToTable(str);
string currentRow = string.Empty, lastRow = string.Empty;
Console.WriteLine("rows count: " + dt.Rows.Count);
for (int i = dt.Rows.Count - 1; i >= 0; i--)
{
DataRow row = dt.Rows[i];
if (i > 1)
{
currentRow = row[8].ToString();
string[] ss = currentRow.Split('+');
if (!string.IsNullOrEmpty(currentRow) && !string.IsNullOrEmpty(lastRow))
{
string[] dd = lastRow.Split('+');
int temp = Math.Abs(int.Parse(ss[1].Trim()) - int.Parse(dd[1].Trim()));
if (dd[0] == ss[0])
{
if (temp > 1 && temp < 20)
{
//Console.WriteLine("current:{0} last:{1}", currentRow, lastRow);
}
else
{
dt.Rows.Remove(row);
}
}
else
{
dt.Rows.Remove(row);
}
}
else
{
dt.Rows.Remove(row);
}
lastRow = currentRow;
}
}
string ps = Path.Combine(folder, finfo.Name + "_new.xls");
ExcelHelper.TableToExcel(dt, ps);
Console.ReadKey();
}
}
以下代码来自互联网,只不过我按我的需求修改了一下:
internal class ExcelHelper
{
/// <summary>
/// Excel导入成Datable
/// </summary>
/// <param name="file"></param>
/// <returns></returns>
internal static DataTable ExcelToTable(string file)
{
DataTable dt = new DataTable();
IWorkbook workbook;
string fileExt = Path.GetExtension(file).ToLower();
using (FileStream fs = File.OpenRead(file))
{
//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++)
{
object obj = GetValueType(header.GetCell(i));
if (obj == null || obj.ToString() == string.Empty)
{
dt.Columns.Add(new DataColumn("Columns" + i.ToString()));
}
else
dt.Columns.Add(new DataColumn(obj.ToString()));
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] = GetValueType(sheet.GetRow(i).GetCell(j));
if (dr[j] != null && dr[j].ToString() != string.Empty)
{
hasValue = true;
}
}
if (hasValue)
{
dt.Rows.Add(dr);
}
}
}
return dt;
}
/// <summary>
/// Datable导出成Excel
/// </summary>
/// <param name="dt"></param>
/// <param name="file"></param>
internal static 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);
ICellStyle cellstyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
cellstyle.VerticalAlignment = VerticalAlignment.Center;
cellstyle.Alignment = HorizontalAlignment.Center;
//表头
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);
cell.CellStyle = cellstyle;
}
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count));
//数据
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);
if(j==2&&i>0)
{
cellstyle.DataFormat = format.GetFormat("yyyy-MM-dd");
cell.SetCellValue(DateTime.Parse(dt.Rows[i][j].ToString()).ToString("yyyy-MM-dd"));
}else if(j==3&&i>0)
{
cellstyle.DataFormat = format.GetFormat("hh:mm:ss");
cell.SetCellValue(DateTime.Parse(dt.Rows[i][j].ToString()).ToString("hh:mm:ss"));
}
else
{
cell.SetCellValue(dt.Rows[i][j].ToString());
}
cell.CellStyle = cellstyle;
}
}
//转为字节数组
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();
}
}
/// <summary>
/// 获取单元格类型
/// </summary>
/// <param name="cell"></param>
/// <returns></returns>
private static object GetValueType(ICell cell)
{
if (cell == null)
return null;
switch (cell.CellType)
{
case CellType.Blank: //BLANK:
return null;
case CellType.Boolean: //BOOLEAN:
return cell.BooleanCellValue;
case CellType.Numeric: //NUMERIC:
//判断单元格内容是否是日期格式
if (DateUtil.IsCellDateFormatted(cell))
{
return cell.DateCellValue;
}
else
{
return cell.NumericCellValue;
}
case CellType.String: //STRING:
return cell.StringCellValue;
case CellType.Error: //ERROR:
return cell.ErrorCellValue;
case CellType.Formula: //FORMULA:
default:
return "=" + cell.CellFormula;
}
}
}
第二种方式, ExcelDataReader(支持.xls,.xlsx)这个库只能读取,不能做修改,而且unity也可以使用,在做游戏的一些参数配置的时候,可以用这种方式把相应的excel数据都进来。这个库的源码路径:https://github.com/ExcelDataReader/ExcelDataReader从别的地方下载的可能会出现ICSharpCode.SharpZipLib.dll这个库的小版本号对不上。目前这个库的源码是vs2017的工程。我通过vs2017编译的dll已经上传,需要的朋友直接下载使用。http://download.csdn.net/download/u011017980/10131282
首先得using ExcelDataReader;
FileStream stream = File.Open(exlPath, FileMode.Open, FileAccess.Read, FileShare.Read);
//97 - 2003 format *.xls
IExcelDataReader excelReader = ExcelReaderFactory.CreateBinaryReader(stream);
//2007 format *.xlsx
IExcelDataReader excelReader = ExcelReaderFactory.CreateOpenXmlReader(stream);
然后你就从这个excelreader里循环取数据。
第三种方式,EPPlus(支持*.xlsx),有兴趣的朋友可以试一下。在nuget上的下载量挺多的。