关闭

读取txt数据,向excel中写入数据

标签: excelWritableWorkBookLabel
95人阅读 评论(0) 收藏 举报
分类:

WritableWorkBook实现创建excel,并向excel写入数据。代码如下:


import java.io.BufferedReader;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.FileReader;
import java.io.IOException;
import java.io.InputStreamReader;
import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;
import java.util.UUID;


import org.junit.Test;


import jxl.CellFormat;
import jxl.Workbook;
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 net.sf.json.JSONArray;
import net.sf.json.JSONObject;


public class TestCreateExcel {


private final String basePath = "F://错误/";
private final String txtpath = "F:/错误20151117.txt";

// private TestCreateExcel test;
//
// public TestCreateExcel(){
// test = new TestCreateExcel();
// }

public static void main(String[] args) {

TestCreateExcel test = new TestCreateExcel();

File txtFile =  new File(test.writeTxt());//读取的文件

File excelFile = new File(test.getPath());//写进的文件

WritableWorkbook workbook =null;
try {
//创建新的工作簿
workbook = Workbook.createWorkbook(excelFile);
//创建一张工作表
WritableSheet  sheet = workbook.createSheet("one", 0);
//创建单元格
WritableCell cell = null;
//创建Label对象
Label label = null;
//设置单元格的格式
WritableFont font1 = new WritableFont(WritableFont.TIMES,10,WritableFont.BOLD);//数字10为字体大小
font1.setColour(Colour.BLUE);//设置字体颜色
WritableCellFormat format = new WritableCellFormat(font1);
format.setAlignment(jxl.format.Alignment.CENTRE);//设置居中

String[] headers = { "carrier", "flightNumber", "depAirport", "depTime", "arrAirport", "arrTime", "cabin","","carrier", "flightNumber", "depAirport", "depTime", "arrAirport", "arrTime", "cabin"};
for(int i =0;i<headers.length;i++){//设置第一行字段
label = new Label(i,0,headers[i]);
sheet.addCell(label);//将单元格添加到表格中
sheet.setColumnView(i, 15);
}

StringBuffer buffer = new StringBuffer();
//读取txt文件数据
BufferedReader reader = new BufferedReader(new FileReader(txtFile));
String temp = null;
while((temp = reader.readLine())!= null){
buffer.append(temp);
}
reader.close();

String carrier = null;
String flightNumber = null;
String depAirport = null;
String depTime = null;
String arrAirport = null;
String arrTime = null;
String cabin = null;


JSONObject object = JSONObject.fromObject(buffer.toString());
JSONArray arr = JSONArray.fromObject(object.getString("abc"));
int x = 0;
int y = 0;
for(int m=0;m<arr.size();m++){
JSONObject obj = arr.getJSONObject(m);
JSONObject obj2 = JSONObject.fromObject(obj.getString("routing"));
JSONArray arr2 = JSONArray.fromObject(obj2.getString("fromSegments"));
x = m+1;
for(int n=0;n<arr2.size();n++){
JSONObject obj3 = arr2.getJSONObject(n);
carrier = obj3.getString("carrier");
flightNumber = obj3.getString("flightNumber");
depAirport = obj3.getString("depAirport");
depTime = obj3.getString("depTime");
arrAirport = obj3.getString("arrAirport");
arrTime = obj3.getString("arrTime");
cabin  = obj3.getString("cabin");

y = 8*n;

label = new Label(y,x,carrier,format);
sheet.addCell(label);
label = new Label(y+1,x,flightNumber,format);
sheet.addCell(label);
label = new Label(y+2,x,depAirport,format);
sheet.addCell(label);
label = new Label(y+3,x,depTime,format);
sheet.addCell(label);
label = new Label(y+4,x,arrAirport,format);
sheet.addCell(label);
label = new Label(y+5,x,arrTime,format);
sheet.addCell(label);
label = new Label(y+6,x,cabin,format);
sheet.addCell(label);
}

}

workbook.write();
workbook.close();



} catch (Exception e) {
// TODO Auto-generated catch block
e.printStackTrace();
}


// //UUID
// for(int k=0;k<5;k++){
// String uid = UUID.randomUUID().toString();
// System.out.println(uid);
// String uid2 = uid.replace("-", "");
// System.out.println(uid2);
// }


}

//创建本地文件(路径名)
public String getPath(){

SimpleDateFormat formate = new SimpleDateFormat("yyyyMMddHHmm");

String name = formate.format(new Date())+".xls";

String path = basePath+name;

return path;
}

//读取Txt文件,并转换格式
public String readTxt() throws IOException{

// String txtPath = "F:/22.txt";
String txtPath = txtpath;

File file = new File(txtPath);

// String strs = "{\"abc\":[";
StringBuffer buffer = new StringBuffer();
//读取txt文件
try {
BufferedReader reader = new BufferedReader(new FileReader(file));
String temp = null;
while((temp = reader.readLine()) != null){
buffer.append(temp);
}
reader.close();
// buffer.append("]}");
//添加','
String str = buffer.toString();
String result = null;
List<String> list = new ArrayList<String>();
int x = 0;
int index = str.indexOf("tripType");

while(true){
if(index > 0){
if(x == 0){
result = str.substring(x, index+14);//第一条数据
}else{
index = str.indexOf("tripType", x);
if((index+14) > (str.length()-1)){
result = str.substring(x);      //最后一条数据
list.add(result);
break;
}else{
result = str.substring(x,index+14);

}
}
}else{
break;
}
x = index+14;
list.add(result);
}
// while(true){
// if(index > 0){
// if(x == 0){
// result = str.substring(x, index+46);//第一条数据
// }else{
// index = str.indexOf("tripType", x);
// if((index+46) > (str.length()-1)){
// result = str.substring(x);      //最后一条数据
// list.add(result);
// break;
// }else{
// result = str.substring(x,index+46);
//
// }
// }
// }else{
// break;
// }
// x = index+46;
// list.add(result);
// }

StringBuffer buf = new StringBuffer();
for(String s:list){
buf.append(s);
buf.append(",");
}
String st = buf.toString();
String strs = st.substring(0, st.length()-1);
//将数据整体转为json格式
StringBuffer buf2 = new StringBuffer("{\"abc\":[");
buf2.append(strs);
buf2.append("]}");

return buf2.toString();

} catch (FileNotFoundException e) {
e.printStackTrace();
}
return null;

}

//创建一个txt文档,并写进内容
public String writeTxt(){
String str = null;
try {
str = readTxt();
} catch (IOException e) {
e.printStackTrace();
}

str = str.replaceAll(" ", "");

SimpleDateFormat formate = new SimpleDateFormat("yyyyMMddHHmm");
String fileName = formate.format(new Date())+".txt";
String path = basePath+fileName;
File file = new File(basePath);
if(!file.exists()){
file.mkdirs();
}

try {
FileOutputStream out = new FileOutputStream(new File(path));
try {
out.write(str.getBytes());
out.close();
} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}

return path;

}
/*
@Test
public void testStrLength(){
File file = new File("D://错误.txt");
StringBuffer buf = new StringBuffer();

try {
BufferedReader reader = new BufferedReader(new FileReader(file));
String temp=null;
try {
while((temp = reader.readLine()) != null){
buf.append(temp);
}
reader.close();
String strs = buf.toString();
System.out.println(">>>>"+strs);
System.out.println("****"+strs.length());

} catch (IOException e) {
e.printStackTrace();
}
} catch (FileNotFoundException e) {
e.printStackTrace();
}
}
*/

}
0
0

查看评论
* 以上用户言论只代表其个人观点,不代表CSDN网站的观点或立场
    个人资料
    • 访问:212次
    • 积分:21
    • 等级:
    • 排名:千里之外
    • 原创:2篇
    • 转载:0篇
    • 译文:0篇
    • 评论:0条
    文章分类
    文章存档