npoi读取excel并修改单元格值

IWorkbook workbook = null;//IWorkbook是一个接口，XSSFWorkbook和HSSFWorkbook都继承自此接口
ISheet sheet = null;//ISheet也是一个接口

string fileName = "F:\\aa.xls";
string sheetName = "";
FileStream fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0)      // 2007版本
    workbook = new XSSFWorkbook(fs);
else if (fileName.IndexOf(".xls") > 0)  // 97-2003版本
    workbook = new HSSFWorkbook(fs);
//workbook = NPOI.SS.UserModel.WorkbookFactory.Create(fs);
if (sheetName != "")                    //是否有传入表名
{
    sheet = workbook.GetSheet(sheetName);
}
else
{
    sheet = workbook.GetSheetAt(0);    //读取第一个sheet
}

//【2】解析excel文件的内容，填充到dt中
if (sheet == null)//工作表中的内容为null时
{
    throw new Exception("未找到工作表");
}

int startRow = 0;   //开始读取行数 
IRow firstRow = sheet.GetRow(startRow);   //第一行 
int cellCount = firstRow.LastCellNum;     //一行最后一个cell的编号,即总的列数

//添加dt的所有行内容
startRow = startRow + 1;
int rowCount = sheet.LastRowNum;      //总行数

int showInt = 1;

for (int i = startRow; i <= rowCount; i++)
{
    IRow row = sheet.GetRow(i);
    if (row == null) continue; //没有数据的行默认是null

    ICell cell = row.GetCell(1);
    ICell cell1 = row.GetCell(0);
    string val = cell.StringCellValue;
    string s = "";
    if (val.Length > 2)
    {
        if (val.Substring(2, 1) == "（")
        {
            s = pinyin.GetSpellCode(val.Substring(0, 2));
        }
        else
        {
            s = pinyin.GetSpellCode(val.Substring(0, 3));
        }
    }
    else
        s = pinyin.GetSpellCode(val.Substring(0));
    s = s + showInt.ToString().PadLeft(4,'0');
    cell1.SetCellValue(s);

    showInt++;
}

//将修改写入文件
using (FileStream fs1 = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
    workbook.Write(fs1, true);
}

nuget注意引入NPOI

翻译

搜索

复制