Java生成Excel表格的代码

题记：

 

这是我到宇信易诚的第二个年头，我被调到了数据部门，现在的我现在无休止的和数据与Excel之间纠结。

 

 

1. 我们先定义这三个类

 

 

DataItem类，表示一个单元格内的数字 

 

package com.tntxia.pem.entity;

public class DataItem {
	
	private String value;
	
	private String dataType;
	
	private String cellStyle="";
	
	public String getCellStyle() {
		return cellStyle;
	}

	public void setCellStyle(String cellStyle) {
		this.cellStyle = cellStyle;
	}

	/**
	 * 空构造函数
	 */
	public DataItem(){
		
	}
	
	/**
	 * 字符串参数的构造函数，默认将参数作为这个DataItem的值
	 */
	public DataItem(String value){
		this.value = value;
	}
	
	public String getDataType() {
		return dataType;
	}

	public void setDataType(String dataType) {
		this.dataType = dataType;
	}

	public String getValue() {
		return value;
	}

	public void setValue(String value) {
		this.value = value;
	}

}

 

DataRow类，表示Excel中的一行，由多个DataItem组成

 

package com.tntxia.pem.entity;

import java.util.ArrayList;

public class DataRow {
	
	private ArrayList<DataItem> dataItems=new ArrayList<DataItem>();

	public ArrayList<DataItem> getDataItems() {
		return dataItems;
	}

	public void setDataItems(ArrayList<DataItem> dataItems) {
		this.dataItems = dataItems;
	}
	
	public void add(DataItem item){
		this.dataItems.add(item);
	}
	
	public void add(String value){
		this.dataItems.add(new DataItem(value));
	}
	
	public int size(){
		return this.getDataItems().size();
	}

}

 

 

DataList类，整个表的数据，由多个DataRow组成。

 

package com.tntxia.pem.entity;

import java.util.ArrayList;

public class DataList {
	
	private ArrayList<DataRow> rows= new ArrayList<DataRow>();

	public ArrayList<DataRow> getRows() {
		return rows;
	}

	public void setRows(ArrayList<DataRow> rows) {
		this.rows = rows;
	}
	
	public void add(DataRow row){
		rows.add(row);
	}
	
	public void remove(int index){
		rows.remove(index);
	}
	
	// 把另外一个数据集合，注入到当前数据集合里面来。
	public void inject(DataList dataList){
		for(DataRow row : dataList.getRows()){
			this.rows.add(row);
		}
	}
	
	public String toString(){
		String res = "{";
		for(int i=0;i<rows.size();i++){
			DataRow row = rows.get(i);
			res+="{";
			for(int k=0;k<row.size();k++){
				res+= row.getDataItems().get(k).getValue()+",";
			}
			res+="},";
		}
		res+="}";
		return res;
	}
	
	public String[][] toTwoDArray(){
		ArrayList<DataRow> dataRows = this.getRows();
		String[][] result = new String[dataRows.size()][];
		int most = 0;
		for(int i=0;i<dataRows.size();i++){
			DataRow row = dataRows.get(i);
			ArrayList<DataItem> items = row.getDataItems();
			if(most<items.size()){
				most = items.size();
			}
			result[i]= new String[most];
			
			for(int j=0;j<most;j++){
				if(j<items.size()){
					DataItem item = items.get(j);
					result[i][j]=item.getValue();
				}else{
					result[i][j]="[null]";
				}
			}
		}
		return result;
	}

}

 

2. 写一个Excel的Util类

 

package com.tntxia.pem;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Locale;

import org.apache.poi.hssf.usermodel.HSSFCell;
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;

import com.tntxia.pem.entity.DataItem;
import com.tntxia.pem.entity.DataList;
import com.tntxia.pem.entity.DataRow;

import jxl.Cell;
import jxl.CellType;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Border;
import jxl.format.BorderLineStyle;
import jxl.format.Colour;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Excel的工具类
 * 
 * @author chensx
 * 
 */
public class ExcelUtil {

	/** 不居中不加粗，内无边框，白底黑字，上边框 */
	private static WritableCellFormat wcf_mbwb = null;

	/** 居中加粗，白底黑字 */
	private static WritableCellFormat getWcfMBWB() {
		if (wcf_mbwb == null) {
			WritableFont wcf_mbwb_font = new WritableFont(WritableFont
					.createFont("宋体"), 10, WritableFont.BOLD);
			try {
				wcf_mbwb_font.setColour(Colour.BLACK);
				wcf_mbwb = new WritableCellFormat(wcf_mbwb_font);
				wcf_mbwb.setAlignment(jxl.format.Alignment.CENTRE);
				wcf_mbwb
						.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);
				wcf_mbwb.setBackground(Colour.WHITE);
				wcf_mbwb.setBorder(Border.ALL, BorderLineStyle.THIN,
						Colour.BLACK);
				wcf_mbwb.setWrap(true);
			} catch (WriteException e) {
				e.printStackTrace();
			}
		}
		return wcf_mbwb;
	}

	/**
	 * 创建空白Excel文件，如果文件已经存在，为了避免覆盖已有文件引起的麻烦，直接返回
	 */
	public static void createExcelFile(String filePath) {
		File file = new File(filePath);
		if (file.exists()) {
			return;
		} else {
			try {
				file.createNewFile();
			} catch (Exception e) {
				e.printStackTrace();
				return;
			}

		}
		WorkbookSettings ws = new WorkbookSettings();
		ws.setLocale(new Locale("en", "EN"));
		try {
			WritableWorkbook workbook = null;
			workbook = Workbook.createWorkbook(file, ws);

			if (workbook.getSheets().length == 0) {
				workbook.createSheet("1", 0);
			}
			workbook.write();
		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	/**
	 * 将dataList里面的数据生成一张Excel表格
	 */
	public static void createExcelFile(String filePath, DataList dataList) {
		WritableWorkbook workbook = null;
		File file = new File(filePath);
		try {
			workbook = Workbook.createWorkbook(file);
			WritableSheet sheet = null;
			if (workbook.getSheets().length == 0) {
				sheet = workbook.createSheet("sheet", 0);
			} else {
				sheet = workbook.getSheet(0);
			}
			ArrayList<DataRow> rows = dataList.getRows();
			for (int i = 0; i < rows.size(); i++) {
				DataRow row = rows.get(i);
				ArrayList<DataItem> items = row.getDataItems();
				for (int j = 0; j < items.size(); j++) {
					DataItem item = items.get(j);
					Label value_label = null;
					// 如果Item里面设置了样式，现在只有样式1
					if (item.getCellStyle().equals("1")) {
						value_label = new Label(j, i, item.getValue(),
								getWcfMBWB());
					} else {
						value_label = new Label(j, i, item.getValue());
					}
					sheet.addCell(value_label);
				}
			}
			workbook.write();
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
			try {
				workbook.close();
			} catch (Exception e) {
				e.printStackTrace();
			}
		}
	}

	/**
	 * 拷贝后,进行修改,其中file1为被copy对象，file2为修改后创建的对象
	 * 尽单元格原有的格式化修饰是不能去掉的，我们还是可以将新的单元格修饰加上去， 以使单元格的内容以不同的形式表现
	 * 
	 * @param file1
	 * @param file2
	 */
	public static void modifyExcel(File file1, File file2) {
		try {
			Workbook rwb = Workbook.getWorkbook(file1);
			WritableWorkbook wwb = Workbook.createWorkbook(file2, rwb);// copy
			int sheetCount = wwb.getNumberOfSheets();
			for (int i = 0; i < sheetCount; i++) {
				WritableSheet ws = wwb.getSheet(i);
				int rows = ws.getRows();
				for (int k = 0; k < rows; k++) {
					Cell[] rowCells = ws.getRow(k);
					for (int j = 0; j < rowCells.length; j++) {
						WritableCell wc = ws.getWritableCell(j, k);
						// 判断单元格的类型,做出相应的转换
						if (wc.getType() == CellType.LABEL) {
							Label label = (Label) wc;
							if (label.getString().equals("北京京北方科技股份有限公司")) {
								label.setString("北京宇信易诚科技有限公司");
							}
						}
					}
				}
				wwb.write();
				wwb.close();
				rwb.close();
			}

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static DataList readDataFromFile(String file, int startIndex) {

		POIFSFileSystem fs = null;
		HSSFWorkbook wb = null;
		DataList dataList = new DataList();

		try {
			fs = new POIFSFileSystem(new FileInputStream(file));
			wb = new HSSFWorkbook(fs);
		} catch (IOException e) {
			e.printStackTrace();
			return null;
		}

		HSSFSheet sheet = wb.getSheetAt(0);
		HSSFRow row = null;
		HSSFCell cell = null;
		int rowNum;
		rowNum = sheet.getLastRowNum();
		for (int i = startIndex; i <= rowNum; i++) {
			row = sheet.getRow(i);
			if (row == null)
				continue;
			DataRow dataRow = new DataRow();
			int cellNum = row.getLastCellNum();
			for (int k = 0; k < cellNum; k++) {
				cell = row.getCell((short) k);
				if (cell == null) {
					dataRow.add(new DataItem(null));
				} else {
					if (cell.getCellType() == HSSFCell.CELL_TYPE_NUMERIC) {
						dataRow.add(String.valueOf((int) cell
								.getNumericCellValue()));
					} else if (cell.getCellType() == HSSFCell.CELL_TYPE_STRING) {
						dataRow.add(cell.getStringCellValue());
					}
				}
			}

			dataList.add(dataRow);

		}
		return dataList;
	}

	public static DataList readDataFromFiles(int startIndex) {
		File root = new File("F:\\exel\\五期导出记录2");
		File[] files = root.listFiles();
		DataList dataList = new DataList();
		for (File file : files) {
			DataList dl = readDataFromFile(file.getAbsolutePath(), startIndex);
			dataList.inject(dl);
		}
		return dataList;
	}

	public static void append(File modelFile, File destFile, int modelStart,
			int pasteFileStart) {
		DataList dataList = readDataFromFiles(pasteFileStart);
		System.out.println(dataList.getRows().size());
		try {
			Workbook rwb = Workbook.getWorkbook(modelFile);
			WritableWorkbook wwb = Workbook.createWorkbook(destFile, rwb);

			WritableSheet ws = wwb.getSheet(0);

			for (int i = 0; i < dataList.getRows().size(); i++) {
				DataRow row = dataList.getRows().get(i);
				ArrayList<DataItem> dataItems = row.getDataItems();
				for (int k = 0; k < dataItems.size(); k++) {
					DataItem item = dataItems.get(k);
					Label itemLabel = new Label(k, i + modelStart, item
							.getValue());
					ws.addCell(itemLabel);
				}
			}

			wwb.write();
			wwb.close();
			rwb.close();

		} catch (Exception e) {
			e.printStackTrace();
		}
	}

	public static DataList readExcel(String filePath) {
		DataList list = new DataList();
		try {
			FileInputStream in = new FileInputStream(filePath);
			Workbook wb = Workbook.getWorkbook(in);
			Sheet sheet = wb.getSheet(0);
			for (int i = 0; i < sheet.getRows(); i++) {
				DataRow dataRow = new DataRow();
				list.add(dataRow);
				Cell[] cells = sheet.getRow(i);
				for (int k = 0; k < cells.length; k++) {
					DataItem item = new DataItem(cells[k].getContents());
					dataRow.add(item);
				}
			}
		} catch (Exception e) {
			e.printStackTrace();
		}

		return list;

	}

	public static void main(String[] args) {

	}

}