java poi excel 操作

最新推荐文章于 2024-02-16 16:37:09 发布

lilongherolilong

最新推荐文章于 2024-02-16 16:37:09 发布

阅读量947

点赞数

分类专栏： java web 文章标签： java excel string path list constructor

本文链接：https://blog.csdn.net/lilongherolilong/article/details/6724673

版权

java web 专栏收录该内容

8 篇文章 0 订阅

订阅专栏

java poi excel 操作

package test;

import java.io.*;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.poifs.filesystem.POIFSFileSystem;

class ExcelNode{
	public String str[] = new String[7];
	public ExcelNode() {
		// TODO Auto-generated constructor stub
	}
}

public class Test2 {

	
	/**
	 * @param args
	 * @throws IOException 
	 */
	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		String filename = "内资分支-存续-企业基本信息-【经营(驻在)期限至】大于【经营(驻在)期限自】或为空（准确性）";
		FileInputStream in = new FileInputStream("a.xls");
		File file = new File(filename);
		file.mkdir();
		POIFSFileSystem fs = new POIFSFileSystem(in);
		HSSFWorkbook wb = new HSSFWorkbook(fs);
				
		//HSSFCellStyle cs = wb.createCellStyle();
		//cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
		HSSFSheet sheet =  wb.getSheetAt(0);
		
	
		String index = "";
		String curindex = "";
		boolean flag = true;
		List<ExcelNode> list = new ArrayList<ExcelNode>();
		for(int i = 0;i<=sheet.getLastRowNum();++i) {			
			HSSFRow row = sheet.getRow(i);
			
			if(row.getCell(0).getCellType()==0) {
				curindex = (row.getCell(0).getNumericCellValue()+"").substring(0, 4);
			} else {
				System.out.println(row.getCell(0).getStringCellValue());
				if(row.getCell(0).getStringCellValue().length()<4){ flag = false; }
				else curindex = (row.getCell(0).getStringCellValue().substring(0, 4));				
			}
			if(!flag) {
				String path = filename+ "/" +index + filename+".xls";
				output(list,path);
				break;
			}
			if(!index.equals(curindex) && i!=sheet.getLastRowNum()) {				
				String path = filename+ "/" +index + filename+".xls";
				output(list,path);
				list.clear();
				index = curindex;
			}
			ExcelNode node = new ExcelNode();
			for(int j=0;j<6;++j) {//row.getLastCellNum()
				HSSFCell cell = row.getCell(j);
				if(cell.getCellType()==0) {
					node.str[j] = cell.getNumericCellValue()+"";
				}else {
					node.str[j] = cell.getStringCellValue();
				}				
			}
			list.add(node);
			if(i >= sheet.getLastRowNum() ) {
				//index = curindex;
				String path = filename+ "/" +index + filename+".xls";
				output(list,path);
				list.clear();
				break;
			}
			
		}
		
		
	}
	
	
	public static void output(List<ExcelNode> list,String path) throws IOException {
		HSSFWorkbook wb = new HSSFWorkbook();
		// create a new sheet
		HSSFSheet sheet = wb.createSheet();
		// declare a row object reference
		HSSFRow row = null;
		// declare a cell object reference
		HSSFCell cell = null;
		
		HSSFCellStyle cs = wb.createCellStyle();
		cs.setDataFormat(HSSFDataFormat.getBuiltinFormat("text"));
		
		row = sheet.createRow(0);
		row.createCell(0).setCellValue("登记机关");
		row.createCell(1).setCellValue("类别");
		row.createCell(2).setCellValue("状态");
		row.createCell(3).setCellValue("注册号");
		row.createCell(4).setCellValue("名称");
		row.createCell(5).setCellValue("PriPID");
		for(int i = 1;i<=list.size();++i) {
			row = sheet.createRow(i);
			for(int j = 0;j<6;++j) {
				row.createCell(j).setCellValue(list.get(i-1).str[j]);
			}
		}
		
		// Save
		FileOutputStream out = new FileOutputStream(path);
		wb.write(out);
		out.close();
	}
}