题记:
这是我到宇信易诚的第二个年头,我被调到了数据部门,现在的我现在无休止的和数据与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) {
}
}