3月13日
今天继续顶实现写入excel文件,资询同学后了解到是相当于按照文件格式新建一个新的excel文件,在网上看到需要将excel格式改为03版本的.xls ,具体代码如下:
public class excelutil {
private static WritableFont arial14font = null;
private static WritableCellFormat arial14format = null;
private static WritableFont arial10font = null;
private static WritableCellFormat arial10format = null;
private static WritableFont arial12font = null;
private static WritableCellFormat arial12format = null;
private final static String UTF8_ENCODING = "UTF-8";
/**
* 单元格的格式设置 字体大小 颜色 对齐方式、背景颜色等...
*/
//
private static void format() {
try {
arial14font = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
arial14font.setColour(jxl.format.Colour.LIGHT_BLUE);
arial14format = new WritableCellFormat(arial14font);
arial14format.setAlignment(jxl.format.Alignment.CENTRE);
arial14format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
arial14format.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);
arial10font = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
arial10format = new WritableCellFormat(arial10font);
arial10format.setAlignment(jxl.format.Alignment.CENTRE);
arial10format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
arial10format.setBackground(Colour.GRAY_25);
arial12font = new WritableFont(WritableFont.ARIAL, 10);
arial12format = new WritableCellFormat(arial12font);
//对齐格式
arial10format.setAlignment(jxl.format.Alignment.CENTRE);
//设置边框
arial12format.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
} catch (WriteException e) {
e.printStackTrace();
}
}
/**
* 初始化Excel表格
*
* @param filePath 存放excel文件的路径(path/demo.xls)
* @param sheetName Excel表格的表名
* @param colName excel中包含的列名(可以有多个)
*/
public static void initExcel(String filePath, String sheetName, String[] colName,String name,String ban,String mb,String xuehao) {
format();
WritableWorkbook workbook = null;
try {
File file = new File(filePath);
if (!file.exists()) {
file.createNewFile();
} else {
return;
}
String nm="江小平";
String xh=xuehao;
String dh=mb;
String bj="信1905-2";
workbook = Workbook.createWorkbook(file);
//设置表格的名字
WritableSheet sheet = workbook.createSheet(sheetName, 0);
//创建标题栏
//sheet.addCell((WritableCell) new Label(0, 0, filePath, arial14format));
sheet.addCell(new Label(0, 0, "学生14天健康情况登记表"));
sheet.addCell(new Label(1, 2, bj));
sheet.addCell(new Label(1, 3, "江小平"));
sheet.addCell(new Label(5, 3, xh));
sheet.addCell(new Label(1, 4, "健康"));
sheet.addCell(new Label(5, 4, dh));
sheet.addCell(new Label(0, 2, "单位名称"));
sheet.addCell(new Label(4, 2, "填表日期"));
sheet.addCell(new Label(0, 3, "姓名"));
sheet.addCell(new Label(0, 4, "健康状况"));
sheet.addCell(new Label(4, 3, "学号"));
sheet.addCell(new Label(4, 4, "手机号"));
sheet.addCell(new Label(0, 5, "每日体温、健康状况监测(周期14天)"));
sheet.addCell(new Label(0, 6, "日期"));
sheet.addCell(new Label(1, 6, "体温"));
sheet.addCell(new Label(2, 6, "健康状况"));
sheet.addCell(new Label(3, 6, "所在地"));
sheet.addCell(new Label(5, 6, "备注"));
sheet.addCell(new Label(0, 7, "3月5号"));
sheet.addCell(new Label(0, 8, "3月6号"));
sheet.addCell(new Label(0, 9, "3月7号"));
sheet.addCell(new Label(0, 10, "3月8号"));
sheet.addCell(new Label(0, 11, "3月9号"));
sheet.addCell(new Label(0, 12, "3月10号"));
sheet.addCell(new Label(0, 13, "3月11号"));
sheet.addCell(new Label(0, 14, "3月12号"));
sheet.addCell(new Label(0, 15, "3月13号"));
sheet.addCell(new Label(0, 16, "3月14号"));
sheet.addCell(new Label(0, 17, "3月15号"));
sheet.addCell(new Label(0, 18, "3月16号"));
sheet.addCell(new Label(0, 19, "3月17号"));
sheet.addCell(new Label(0, 20, "3月18号"));
sheet.mergeCells(0,0,6,1);
sheet.mergeCells(1,3,3,3);
sheet.mergeCells(5,3,6,3);
sheet.mergeCells(1,4,3,4);
sheet.mergeCells(5,4,6,4);
sheet.mergeCells(0,5,6,5);
//设置行高
sheet.setRowView(0, 340);
workbook.write();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (workbook != null) {
try {
workbook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
}
}
/**
* 将制定类型的List写入Excel中
*
* @param objList 待写入的list
* @param fileName
* @param <T>
*/
@SuppressWarnings("unchecked")
public static <T> void writeObjListToExcel(List<T> objList, String fileName) {
if (objList != null && objList.size() > 0) {
WritableWorkbook writebook = null;
InputStream in = null;
try {
WorkbookSettings setEncode = new WorkbookSettings();
setEncode.setEncoding(UTF8_ENCODING);
in = new FileInputStream(new File(fileName));
Workbook workbook = Workbook.getWorkbook(in);
writebook = Workbook.createWorkbook(new File(fileName), workbook);
WritableSheet sheet = writebook.getSheet(0);
for (int j = 0; j <objList.size(); j++) {
tempjilu demoBean =(tempjilu) objList.get(j);
List<String> list = new ArrayList<>();
list.add(demoBean.getTemp());
list.add(demoBean.getQk());
list.add(demoBean.getAddress());
int x=hs(demoBean.getTime1());
// sheet.addCell(new Label(1, 10, "list.get(i)", arial12format));
if(x!=0){
for (int i = 0; i < list.size(); i++) {
sheet.addCell(new Label(i+1, x, list.get(i), arial12format));
if (list.get(i).length() <= 4) {
//设置列宽
sheet.setColumnView(i, list.get(i).length() + 8);
} else {
//设置列宽
sheet.setColumnView(i, list.get(i).length() + 5);
}
}
//设置行高
sheet.setRowView(j + 1, 350);
}
}
writebook.write();
workbook.close();
// Toast.makeText(c, "导出Excel成功", Toast.LENGTH_SHORT).show();
} catch (Exception e) {
e.printStackTrace();
} finally {
if (writebook != null) {
try {
writebook.close();
} catch (Exception e) {
e.printStackTrace();
}
}
if (in != null) {
try {
in.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
}
}