Java產生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裡面設定了樣式，現在只有樣式1if (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);// copyint 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) {}}

　　

Java產生Excel表格的代碼