POI读取Excel有两种格式一个是HSSF,另一个是XSSF。 HSSF和XSSF的区别如下:
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
HSSF is the POI Project's pure Java implementation of the Excel '97(-2007) file format.
XSSF is the POI Project's pure Java implementation of the Excel 2007 OOXML (.xlsx) file format.
即:HSSF适用2007以前的版本,XSSF适用2007版本及其以上的。
读取Excel
NPOI使用HSSFWorkbook类来处理xls,XSSFWorkbook类来处理xlsx,它们都继承接口IWorkbook,因此可以通过IWorkbook来统一处理xls和xlsx格式的文件。
以下是简单的例子
private void button1_Click(object sender, EventArgs e)
{
IWorkbook wk = null;
OpenFileDialog temp = new OpenFileDialog();
string filePath = null;
if (temp.ShowDialog() == DialogResult.OK)
filePath = temp.FileName;
string extension = System.IO.Path.GetExtension(filePath);
try
{
FileStream fs = File.OpenRead(filePath);
if (extension.Equals(".xls"))
{
//把xls文件中的数据写入wk中
wk = new HSSFWorkbook(fs);
}
else
{
//把xlsx文件中的数据写入wk中
wk = new XSSFWorkbook(fs);
}
fs.Close();
//读取当前表数据
ISheet sheet = wk.GetSheetAt(0);
IRow row = sheet.GetRow(0); //读取当前行数据
//LastRowNum 是当前表的总行数-1(注意)
int offset = 0;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i); //读取当前行数据
if (row != null)
{
//LastCellNum 是当前行的总列数
for (int j = 0; j < row.LastCellNum; j++)
{
//读取该行的第j列数据,数据为空则为null
ICell hssfCell = row.GetCell(j);
if (hssfCell == null)
continue;
richTextBox1.AppendText(hssfCell.ToString() + " ");
}
richTextBox1.AppendText("\n");
}
}
}
catch (Exception ex)
{
//只在Debug模式下才输出
richTextBox1.AppendText(ex.Message + "\n");
}
}
写Excel文件:
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet_dest = workbook.CreateSheet("StatusCode");
SaveFileDialog sf = new SaveFileDialog();
sf.Filter = "Excel2007 files (*.xlsx)|*.xlsx";
sf.FilterIndex = 0;
sf.RestoreDirectory = true;
sf.CreatePrompt = true;
sf.FileName = null;
if (sf.ShowDialog() == DialogResult.OK)
{
IRow row = sheet_source.GetRow(0); //读取当前行数据
for(int i=0;i<sheet_source.LastRowNum;i++)
{
row = sheet_source.GetRow(i); //读取当前行数据
IRow row_new = sheet_dest.CreateRow(i);//需要先创建行再创建cell
for (int j = 0; j < row.LastCellNum; j++)
{
string value = String.Empty;
if (row.GetCell(j) == null)
value = String.Empty;
else
value = row.GetCell(j).ToString();
row_new.CreateCell(j).SetCellValue(value);
}
}
FileStream sw = new FileStream(sf.FileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
workbook.Write(sw);
sw.Close();
}