JAVA使用JXL操作Excel
~~
?
code:
?
import java.io.File;import java.io.IOException;import java.util.ArrayList;import java.util.List;import jxl.Cell;import jxl.Sheet;import jxl.Workbook;import jxl.format.Alignment;import jxl.format.Border;import jxl.format.BorderLineStyle;import jxl.format.Colour;import jxl.format.VerticalAlignment;import jxl.read.biff.BiffException;import jxl.write.Label;import jxl.write.WritableCellFormat;import jxl.write.WritableFont;import jxl.write.WritableSheet;import jxl.write.WritableWorkbook;public class JxlTest{ public static void main(String[] args) { List<String[]> list = readExcel("bbb.xls"); for (String[] s : list) { // System.out.println(Arrays.asList(s).toString()); } toExcelTest("ccc.xls"); } /** * 用COMMON UPLOAD进行EXCEL文件上传,得到fileItem对象，这里 进行解析，返回集合对象。该方法适合在WEB项目中使用。 * * @param fileItem * @param beginIndex * 正式数据的起始行 例如EXCEL文件 有大标题和小标题和列标题，那么该参数应为 4 * @return * @throws BiffException * @throws IOException */ public static List<String[]> readExcel(String fileName) { int beginIndex = 1; // 保存结果集 List<String[]> result = null; // 保存EXCEL每行的所有单元格中的数据 String[] temp = null; try { File file = new File(fileName); Workbook workBook = Workbook.getWorkbook(file); Sheet sheet = workBook.getSheet(0); Cell cell = null; int rowSize = sheet.getRows(); int colSize = sheet.getColumns(); result = new ArrayList<String[]>(); for (int i = beginIndex - 1; i < rowSize; i++) { // 保存EXCEL每行的所有单元格中的数据 temp = new String[colSize]; for (int t = 0; t < colSize; t++) { cell = sheet.getCell(t, i); String content = ""; if (cell.getContents() != null) { // 去空格，特殊字符和回车键盘 content = cell.getContents().replace("%", "").replace("|", "").replace(" ", "").replaceAll( "\\n", "").replaceAll("\\r", "").trim(); } temp[t] = content; } // 将每列的的数据存入结果集中 result.add(temp); } } catch (Exception ex) { ex.printStackTrace(); } return result; } /** * * 生成excel * * @param fileName * @author * @date */ public static void toExcelTest(String fileName) { // 初始化工作表 WritableWorkbook workbook = null; try { // 设置弹出对话框 // 创建工作表 workbook = Workbook.createWorkbook(new File(fileName)); // 定义工作表 sheet 标题 WritableSheet ws = workbook.createSheet("Mapping", 0); ws.getSettings().setShowGridLines(true); ws.getSettings().setProtected(false); // 控制列的宽度,如果你要不给一样的宽度,就单独写,i代表的是列的下标,从0开始 ,从左到右 for (int i = 0; i < 7; i++) { ws.setColumnView(i, 20); } // 創建标题列名称 Label titleLabel = null; titleLabel = new Label(0, 0, "StuNo", getHeadFormat()); ws.addCell(titleLabel); titleLabel = new Label(1, 0, "StuName", getHeadFormat()); ws.addCell(titleLabel); titleLabel = new Label(2, 0, "StuMoniter", getHeadFormat()); ws.addCell(titleLabel); titleLabel = new Label(3, 0, "StuAddr", getHeadFormat()); ws.addCell(titleLabel); titleLabel = new Label(4, 0, "Stu TELL", getHeadFormat()); ws.addCell(titleLabel); titleLabel = new Label(5, 0, "Stu sex", getHeadFormat()); ws.addCell(titleLabel); titleLabel = new Label(6, 0, "Stu classNo", getHeadFormat()); ws.addCell(titleLabel); // 循环添加对象数据 for (int i = 0; i < 2; i++) { Label stuNoLabel = new Label(0, i + 1, "stuNo"); Label stuNameLabel = new Label(1, i + 1, "stuName"); Label stuminterLabel = new Label(2, i + 1, "stuminter"); Label stuAddrLable = new Label(3, i + 1, "stuAddr"); // Label stuTellLabel = new Label(4, i + 1, stutell); // 如果用这个导出后会有数值和文本的区别 jxl.write.Number tellNumber = new jxl.write.Number(4, i + 1, Double.parseDouble("23")); Label stusexLabel = new Label(5, i + 1, "stusex"); // Label classNoLabel = new Label(6, i + 1,stuClassno); // 一下要注意了,必须要转成double类型的数据 jxl.write.Number classNoNumber = new jxl.write.Number(6, i + 1, Double.parseDouble("36")); ws.addCell(stuNoLabel); ws.addCell(stuNameLabel); ws.addCell(stuminterLabel); ws.addCell(stuAddrLable); ws.addCell(tellNumber); ws.addCell(stusexLabel); ws.addCell(classNoNumber); } workbook.write(); workbook.close(); } catch (Exception e) { System.out.println(e.getCause()); System.out.println(e.getMessage()); } } /** * 设置单元格样式 * * @return * @throws Exception */ public static WritableCellFormat getHeadFormat() throws Exception { // 设置字体 WritableFont wf = new WritableFont(WritableFont.ARIAL, 8, WritableFont.BOLD); // 创建单元格FORMAT WritableCellFormat wcf = new WritableCellFormat(wf); wcf.setAlignment(Alignment.CENTRE); wcf.setVerticalAlignment(VerticalAlignment.CENTRE); wcf.setLocked(true); wcf.setBorder(Border.ALL, BorderLineStyle.THIN, Colour.BLACK); wcf.setBackground(Colour.GREY_25_PERCENT); return wcf; }}?