根据本人自身的需求,周末写了个excel行列转换代码,可处理一定量的数据。
package com.lfn.excel;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 此类只能用于excel 2003的读取
* @author Administrator
*
*/
public class ChangeRowConlum {
/**
* @param l,表示截断excel的多少列为一行
*
*
*/
public static void rowcolumn(int l , String inputfilename , String outputfilename)
{
Workbook wb = null;
WritableSheet ws = null;
jxl.write.Label lable = null ;
WritableWorkbook wwb = null;
//大的第二行的插入行的起始位置
int starth = 0;
int c = 0;
try {
wb = Workbook.getWorkbook(new File(inputfilename));
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(wb == null)
{
return;
}
Sheet[] sheet = wb.getSheets();//获取工作簿表对象
if(sheet!=null && sheet.length > 0)
{
//创建一个工作簿对象
try {
wwb = Workbook.createWorkbook(new File(outputfilename));
if(wwb != null)
{
//创建一个可写入的表,第一个是工作簿表名,第二个是工作簿的位置
ws = wwb.createSheet("sheet1", 0);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for(int i = 0 ; i < sheet.length ; i++)//这里一般是只有一张sheet
{
int rownum = sheet[i].getRows();//工作簿对象的行数
int column = sheet[i].getColumns();
for(int r = 0; r < rownum ; r++)
{
//得到当前行的所有单元格
Cell[] cell = sheet[i].getRow(r);
if(cell != null && cell.length > 0)
{
//写入excel文件的行数
int hang = (r+1)*(((column-1)/8)+1);
//插入位置应该接着上次插入的最后一行的下一行开始
for(int h = starth ; h < hang ; h ++)
{
//写入文件的行数
for(int ll = 0 ; ll < l ; ll++)
{
if(ll == 0)
{
lable = new jxl.write.Label(ll, h,cell[0].getContents());
}else{
c++;
// System.out.println("----"+c);
// System.out.println("===="+cell.length);
if(c>=cell.length)
{
// continue;
lable = new jxl.write.Label(ll, h,"0");
c=0;
}else{
lable = new jxl.write.Label(ll, h,cell[c].getContents());
System.out.println("----"+cell[c].getContents());
}
}
try {
ws.addCell(lable); //添加到excel
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
starth = hang;//把最后一行行号赋给第二个大行的插入位置
System.out.println("starh***"+starth);
}
}
}
}
try {
wwb.write();//写入excel文件
wwb.close();
}
catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
测试类:
package com.lfn.excel;
import java.io.File;
public class ReadExcelFileTest {
/**
* @param args
*/
public static void main(String[] args) {
ChangeRowConlum.rowcolumn(9, "f:\\111.xls", "f:\\26.xls");
}
}
package com.lfn.excel;
import java.io.File;
import java.io.IOException;
import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.read.biff.BiffException;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;
import jxl.write.biff.RowsExceededException;
/**
* 此类只能用于excel 2003的读取
* @author Administrator
*
*/
public class ChangeRowConlum {
/**
* @param l,表示截断excel的多少列为一行
*
*
*/
public static void rowcolumn(int l , String inputfilename , String outputfilename)
{
Workbook wb = null;
WritableSheet ws = null;
jxl.write.Label lable = null ;
WritableWorkbook wwb = null;
//大的第二行的插入行的起始位置
int starth = 0;
int c = 0;
try {
wb = Workbook.getWorkbook(new File(inputfilename));
} catch (BiffException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
if(wb == null)
{
return;
}
Sheet[] sheet = wb.getSheets();//获取工作簿表对象
if(sheet!=null && sheet.length > 0)
{
//创建一个工作簿对象
try {
wwb = Workbook.createWorkbook(new File(outputfilename));
if(wwb != null)
{
//创建一个可写入的表,第一个是工作簿表名,第二个是工作簿的位置
ws = wwb.createSheet("sheet1", 0);
}
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
for(int i = 0 ; i < sheet.length ; i++)//这里一般是只有一张sheet
{
int rownum = sheet[i].getRows();//工作簿对象的行数
int column = sheet[i].getColumns();
for(int r = 0; r < rownum ; r++)
{
//得到当前行的所有单元格
Cell[] cell = sheet[i].getRow(r);
if(cell != null && cell.length > 0)
{
//写入excel文件的行数
int hang = (r+1)*(((column-1)/8)+1);
//插入位置应该接着上次插入的最后一行的下一行开始
for(int h = starth ; h < hang ; h ++)
{
//写入文件的行数
for(int ll = 0 ; ll < l ; ll++)
{
if(ll == 0)
{
lable = new jxl.write.Label(ll, h,cell[0].getContents());
}else{
c++;
// System.out.println("----"+c);
// System.out.println("===="+cell.length);
if(c>=cell.length)
{
// continue;
lable = new jxl.write.Label(ll, h,"0");
c=0;
}else{
lable = new jxl.write.Label(ll, h,cell[c].getContents());
System.out.println("----"+cell[c].getContents());
}
}
try {
ws.addCell(lable); //添加到excel
} catch (RowsExceededException e) {
// TODO Auto-generated catch block
e.printStackTrace();
} catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
starth = hang;//把最后一行行号赋给第二个大行的插入位置
System.out.println("starh***"+starth);
}
}
}
}
try {
wwb.write();//写入excel文件
wwb.close();
}
catch (WriteException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
}
测试类:
package com.lfn.excel;
import java.io.File;
public class ReadExcelFileTest {
/**
* @param args
*/
public static void main(String[] args) {
ChangeRowConlum.rowcolumn(9, "f:\\111.xls", "f:\\26.xls");
}
}
转载于:https://blog.51cto.com/2197042/982904