我参考的博客
主方法：
package util;
import java.awt.event.ActionEvent;
import java.awt.event.ActionListener;
import java.io.File;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;import javax.swing.JButton;
import javax.swing.JFrame;
import javax.swing.JLabel;
import javax.swing.JPanel;
import javax.swing.JTextField;public class Main extends JFrame implements ActionListener{
public void init(){
//创建文本框
JPanel pan = new JPanel();
this.add(pan);
this.placeComponents(pan);
this.setSize(350, 200);
this.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
this.setVisible(true);
}
public static void main(String[] args) throws IOException {
Main main = new Main();
main.init();
}
JTextField ReadPath;
JTextField WritePath ;
private void placeComponents(JPanel panel) { /* 布局部分我们这边不多做介绍
* 这边设置布局为 null
*/
panel.setLayout(null);
/* 这个方法定义了组件的位置。
* setBounds(x, y, width, height)
* x 和 y 指定左上角的新位置，由 width 和 height 指定新的大小。
*/
// 创建 JLabel
JLabel InName = new JLabel("商户地址");
InName.setBounds(10,20,80,25);
panel.add(InName); /*
* 创建文本域用于用户输入商户信息地址
*/
ReadPath = new JTextField(20);
ReadPath.setBounds(100,20,165,25);
panel.add(ReadPath);
JLabel OutName = new JLabel("模版地址");
OutName.setBounds(10,50,80,25);
panel.add(OutName);
/*
* 创建文本域用于用户输入模版地址
*/
WritePath = new JTextField(20);
WritePath.setBounds(100,50,165,25);
panel.add(WritePath);
// 创建点击按钮
JButton downButton = new JButton("start");
downButton.setBounds(10, 80, 80, 25);
downButton.addActionListener(this);
panel.add(downButton);
} private void start(String readPath,String writePath) throws IOException{
ReadExcel re = new ReadExcel();
File file = new File(readPath);
WriterExcel we = new WriterExcel(writePath);
List<String[][]> result = new ArrayList<String[][]>();
//忽略第一行 列名
result = re.getData(file, 0);
//有多少行
int row = result.get(0).length;
//写入 传入参数row 不传column column是不确定的
we.writeEx(row,result.get(0));
}
@Override public void actionPerformed(ActionEvent e) {
// TODO Auto-generated method stub
String readPath = ReadPath.getText();
String writePath = WritePath.getText();
try {
this.start(readPath,writePath);
} catch (IOException e1) {
// TODO Auto-generated catch block
e1.printStackTrace();
}
}
}
读取Excel文件方法：
package util;
import java.io.BufferedInputStream;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.text.DecimalFormat;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;
import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
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;
/**
* @author shirenchuang
*
*/
public class ReadExcel {
/*private String fileUrl;
public ReadExecl(String fileUrl) {
// TODO Auto-generated constructor stub
this.fileUrl = fileUrl;
}*/
// File file = new File(fileUrl);
/**
* 读取Excel的内容，第一维数组存储的是一行中格列的值，二维数组存储的是多少个行
* @param file 读取数据的源Excel
* @param ignoreRows 读取数据忽略的行数，比喻行头不需要读入 忽略的行数为1
* @return 读出的Excel中数据的内容
* @throws FileNotFoundException
* @throws IOException
*/
@SuppressWarnings("deprecation")
public static List<String[][]> getData(File file,int ignoreRows) throws IOException{
//返回所有工作表的数据
List<String[][]> result = new ArrayList<String[][]>();
//得到文件的输入流
BufferedInputStream in = new BufferedInputStream(new FileInputStream(file));
POIFSFileSystem fs = new POIFSFileSystem(in);
HSSFWorkbook wb = new HSSFWorkbook(fs); //创建输入流的excel文件
HSSFCell cell = null;
System.out.println("NumOfSheets: "+wb.getNumberOfSheets());
//多个工作表
for(int i=0;i<wb.getNumberOfSheets();i++){
//得到工作表
HSSFSheet hf = wb.getSheetAt(i);
//一个工作表的数据 记得加上1 比如excel加上第一行的列名 总共66 但是getLastRowNum（）是65 把列名也算上
String[][] rowResult = new String[hf.getLastRowNum()+1][7];
System.out.println("LastRowNum: "+hf.getLastRowNum());
//每个工作表的多行
for(int rownumber = ignoreRows; rownumber<=hf.getLastRowNum();rownumber++){
//得到的row的行数不确定的 如果那一行后面有空格 有可能会忽略空格列
HSSFRow row = hf.getRow(rownumber);
if (row == null) {
continue;
}
//某一行的数据
String[] colResult = new String[row.getLastCellNum()];
//得到一行的多个列
/**
* 这里有个问题 就是row.getLastCellNum()有个情况得到的不准确
* 例子：EXECL总共7列数据 但是实际上最后几列有的为空，它会默认把空的列不计入列值；
* 导致的错误就是你调用写入方法的时候会有用的列值 会出错；
*
*/
for(short colnumber = 0;colnumber<row.getLastCellNum();colnumber++){
String value="";
cell=row.getCell(colnumber);
//将cell装换类型
if(cell!=null){
//cell.setEncoding(HSSFCell.ENCODING_UTF_16);
switch(cell.getCellType()){
case HSSFCell.CELL_TYPE_STRING:
value = cell.getStringCellValue();
break;
case HSSFCell.CELL_TYPE_NUMERIC:
if (HSSFDateUtil.isCellDateFormatted(cell)) {
Date date = cell.getDateCellValue();
if (date != null) {
value = new SimpleDateFormat("yyyy-MM-dd")
.format(date);
} else {
value = "";
}
} else {
value = new DecimalFormat("0").format(cell
.getNumericCellValue());
}
break;
case HSSFCell.CELL_TYPE_FORMULA:
// 导入时如果为公式生成的数据则无值
if (!cell.getStringCellValue().equals("")) {
value = cell.getStringCellValue();
} else {
value = cell.getNumericCellValue() + "";
}
break;
case HSSFCell.CELL_TYPE_BLANK:
break;
case HSSFCell.CELL_TYPE_ERROR:
value = "";
break;
case HSSFCell.CELL_TYPE_BOOLEAN:
value = (cell.getBooleanCellValue() == true ? "Y"
: "N");
break;
default:
value = "";
}//switch
}//if
if (colnumber == 0 && value.trim().equals("")) {
// break;
}
colResult[colnumber]=rightTrim(value);
}//for()列
rowResult[rownumber]=colResult;
}//for() 行
if(rowResult!=null)
result.add(rowResult);
}//for工作表
in.close();
return result;
}
/**
* 去掉字符串右边的空格
* @param str 要处理的字符串
* @return 处理后的字符串
*/
public static String rightTrim(String str) {
if (str == null) {
return "";
}
int length = str.length();
for (int i = length - 1; i >= 0; i--) {
if (str.charAt(i) != 0x20) {
break;
}
length--;
}
return str.substring(0, length);
}
}
批量写Excel方法后并另存到E:\test中。
package util;
import java.io.File;
import java.io.IOException;import jxl.Cell;
import jxl.CellType;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCell;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
/**
* @author
*
*/
public class WriterExcel {
private static String writeUrl ="";
private static int NUM = 0;
public String getWriteUrl() {
return writeUrl;
}
public void setWriteUrl(String writeUrl) {
this.writeUrl = writeUrl;
}
public WriterExcel(String writeUrl ) {
// TODO Auto-generated constructor stub
this.writeUrl= writeUrl;
}
public static void writeCell(int sheetNum,int col,int row,String[][] data) throws BiffException, IOException, WriteException{
String file =writeUrl;
WritableWorkbook wwb = null;
Label label = null;
// Excel获得文件
Workbook wb = Workbook.getWorkbook(new File(file));
// 打开一个文件的副本，并且指定数据写回到原文件
wwb = Workbook.createWorkbook(new File(file),wb);
WritableSheet ws = wwb.getSheet(sheetNum);
WritableCell wc = ws.getWritableCell(col,row);
if (ws != null) {
//判断单元格的类型,做出相应的转换;找到相应的工作表填写单元格
if(wc.getType() == CellType.LABEL && sheetNum == 0)
{
label = (Label)wc;
label.setString(data[NUM][0]);
System.out.println(data[NUM][0]);
}else if(wc.getType() == CellType.LABEL && sheetNum == 1){
label = (Label)wc;
label.setString(data[NUM][1]);
System.out.println(data[NUM][1]);
}else if(wc.getType() == CellType.LABEL && sheetNum == 2){
label = (Label)wc;
label.setString(data[NUM][2]);
System.out.println(data[NUM][2]);
}
}
// 从内存中写入到文件
wwb.write();
wwb.close();
wb.close();
System.out.println("路径为：" + file + "的工作簿写入数据成功！");
}
/**
*
* 另存填写的excel表格，命名规则按，1.xsl ...
* **/
private static void copy(String inname,String outname) throws BiffException, IOException, WriteException {
// Excel获得文件
Workbook workbook = Workbook.getWorkbook(new File(inname));
WritableWorkbook outExcel =Workbook.createWorkbook(new File(outname),workbook);
outExcel.write();
outExcel.close();
} /**
*
* 这是单纯的写EXCEL表格
* **/
public static void writeEx(int row,String[][] data){
while(NUM<=row){
NUM++;
try {
writeCell(0,1,3,data);
writeCell(1,1,2,data);
writeCell(2,1,3,data);
copy(writeUrl,"E:/test/"+NUM+".xls");
} catch (Exception e) {
System.out.println(e.getMessage());
} finally {}
}
}}
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
