使用java将变量列excel表导入mysql [英] importing variable column excel sheets to mysql using java
本文介绍了使用java将变量列excel表导入mysql的处理方法，对大家解决问题具有一定的参考价值，需要的朋友们下面随着小编来一起学习吧！
问题描述
我正在使用 apache poi 将数据从 excel 读取到 mysql 表.我已经为有 2 个字段的 excel 表做了它.我的代码就像，
试试{fis = new FileInputStream(filePath);//创建工作簿，工作表(来自poi jar文件)HSSFWorkbook workBook = 新的 HSSFWorkbook(fis);HSSFSheet 表 = workBook.getSheetAt(0);//迭代表中的行的迭代器迭代器 itr = sheet.iterator();而(itr.hasNext()){HSSFRow 行 = (HSSFRow) itr.next();迭代器单元格 = row.cellIterator();列表数据 = new ArrayList();而(cell.hasNext()){HSSFCell 值 = (HSSFCell) cell.next();if (value.getCellType() == Cell.CELL_TYPE_NUMERIC){data.add(value.getNumericCellValue());}else if (value.getCellType() == Cell.CELL_TYPE_BOOLEAN){data.add(value.getBooleanCellValue());}else if (value.getCellType() == Cell.CELL_TYPE_STRING){data.add(value.getStringCellValue());}}sheetData.add(data);}}捕获(异常 e){//TODO: 处理异常}然后将数据放在一个数组列表中.十把它插入数据库..但是现在我需要系统来获取具有可变数量的 cloumns 的 excel 文件.我能达到吗?我只是非常需要一些帮助.所以请帮助我一些想法，可能还有一些代码.
提前致谢.苏吉斯
解决方案
我们也可以使用 Simple And Best Way 2 将 Excel 表格上传到数据库中
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd"><头><meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1"><title>在此处插入标题</title><身体><form action="./ExcelSample" method="post" id="formdata"enctype="multipart/form-data" onsubmit="return excelUpload('Conform')"><label for="inputSuccess2" class="control-label"><div id="scn">选择 Excel 文件</div><input type="file" name="excel" id="excelfile"class="form-control active"><label class="control-label"> </label><input type="submit" class="form-control btn btn-orange" id="上传"value="上传"></form></html>包 com.excel.Sample.ExcelAnn;导入 java.lang.annotation.ElementType;导入 java.lang.annotation.Retention;导入 java.lang.annotation.RetentionPolicy;导入 java.lang.annotation.Target;@Retention(RetentionPolicy.RUNTIME)@Target(value=ElementType.METHOD)公共@interface ExcelColumn {boolean ignore() 默认为 false；String label() 默认"";}包 com.excel.Sample.ExcelAnn;导入 java.lang.annotation.ElementType;导入 java.lang.annotation.Retention;导入 java.lang.annotation.RetentionPolicy;导入 java.lang.annotation.Target;@Retention(RetentionPolicy.RUNTIME)@Target(value=ElementType.TYPE)公共@interface ExcelReport {字符串报告名称();}包 com.excel.Sample.Actions;导入 java.io.FileNotFoundException;导入 java.io.FileOutputStream;导入 java.io.IOException;导入 java.io.InputStream;导入 java.lang.reflect.Method;导入 java.util.ArrayList;导入 java.util.Date;导入 java.util.HashMap;导入 java.util.Iterator;导入 java.util.List;导入 java.util.Map;导入 org.apache.poi.hssf.usermodel.HSSFCellStyle;导入 org.apache.poi.hssf.usermodel.HSSFSheet;导入 org.apache.poi.hssf.usermodel.HSSFWorkbook；导入 org.apache.poi.hssf.util.HSSFColor;导入 org.apache.poi.ss.usermodel.Cell;导入 org.apache.poi.ss.usermodel.CellStyle;导入 org.apache.poi.ss.usermodel.Row;导入 org.apache.poi.ss.usermodel.Sheet;导入 org.apache.poi.ss.usermodel.Workbook;导入 com.excel.Sample.ExcelAnn.ExcelColumn;导入 com.excel.Sample.ExcelAnn.ExcelReport;公共类 ExcelAction {私人 HSSFWorkbook 工作簿 = null;private String workbookName = "Book1.xls";私有映射<字符串，字符串>fieldLabelMap = new HashMap();私人列表<字符串>orderLabels = new ArrayList();private CellStyle columnHeaderCellStyle = null;公共 ExcelAction() {初始化();}私有无效初始化(){设置工作簿(新的 HSSFWorkbook())；setColumnHeaderCellStyle(createColumnHeaderCellStyle());}私人 CellStyle createColumnHeaderCellStyle() {CellStyle cellStyle = getWorkbook().createCellStyle();cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);cellStyle.setFillBackgroundColor(new HSSFColor.GREY_25_PERCENT().getIndex());cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);返回单元格样式；}公共无效closeWorksheet(){FileOutputStream 文件输出；尝试 {fileOut = new FileOutputStream(getWorkbookName());getWorkbook().write(fileOut);fileOut.close();} catch (FileNotFoundException e) {e.printStackTrace();} catch (IOException e) {e.printStackTrace();}}私人 HSSFSheet getSheetWithName(字符串名称){HSSFSheet 工作表 = workbook.getSheet(name);退货单；}私有无效 initializeForRead(InputStream inp) 抛出 IOException {工作簿 = 新的 HSSFWorkbook(inp);}私人 <T>void processAnnotations(T 对象) {类clazz = object.getClass();ExcelReport reportAnnotation = (ExcelReport) clazz.getAnnotation(ExcelReport.class);for (方法方法: clazz.getMethods()) {ExcelColumn excelColumn = method.getAnnotation(ExcelColumn.class);if ((excelColumn != null) && !excelColumn.ignore()) {getFieldLabelMap().put(excelColumn.label(), method.getName());getOrderLabels().add(excelColumn.label());}}}@SuppressWarnings("未选中")公共 <T>列表<T>读取数据(字符串类名，输入流输入)抛出异常{Class clazz = Class.forName(classname);processAnnotations(clazz.newInstance());initializeForRead(inp);HSSFSheet sheet = getSheetWithName("Sheet1");列表<T>结果 = 新的 ArrayList();映射<字符串，字符串>mp = new HashMap();迭代器<行>rowIterator = sheet.rowIterator();int rowCount = 0;而 (rowIterator.hasNext()) {T one = (T) clazz.newInstance();尝试 {int colCount = 0;结果.添加(一)；行行 = rowIterator.next();迭代器<Cell>cellIterator = row.cellIterator();而(cellIterator.hasNext()){Cell cell = cellIterator.next();如果(行计数 == 0){mp.put(colCount + "", cell.getStringCellValue().toString().trim());} 别的 {int type = cell.getCellType();String labelName = mp.get(colCount + "");String getter = getFieldLabelMap().get(labelName);String fieldName = getter.substring(3);fieldName = decapitalize(fieldName);方法方法=constructMethod(clazz, fieldName);如果(类型== 1){字符串值 = cell.getStringCellValue();对象[]值=新对象[1]；值[0] = 值；method.invoke(one, values);} else if (type == 0) {Double num = cell.getNumericCellValue();类returnType = getGetterReturnClass(clazz,字段名);如果(返回类型 == Integer.class){method.invoke(one,(Integer) num.intValue());} else if (returnType == Double.class) {method.invoke(one, (Integer) num.intValue());} else if (returnType == Float.class) {method.invoke(one, num.floatValue());} else if (returnType == Date.class) {method.invoke(one, cell.getDateCellValue());}} else if (type == 3) {double num = cell.getNumericCellValue();对象[]值=新对象[1]；值[0] = num;method.invoke(one, values);}}colCount++;}} 捕获(异常 e){System.out.println(e);}行数++；}返回结果；}私人班级<?>getGetterReturnClass(Class clazz, String fieldName) {String methodName = "get" + capitalize(fieldName);类返回类型 = 空；for (方法方法: clazz.getMethods()) {if (method.getName().equals(methodName)) {returnType = method.getReturnType();休息;}}返回返回类型；}@SuppressWarnings("未选中")私有方法constructMethod(Class clazz, String fieldName)抛出 SecurityException，NoSuchMethodException {类fieldClass = getGetterReturnClass(clazz, fieldName);return clazz.getMethod("set" + capitalize(fieldName), fieldClass);}公共 <T>void writeReportToExcel(List data) 抛出异常 {processAnnotations(data.get(0));Sheet sheet = getWorkbook().createSheet(data.get(0).getClass().getName());int rowCount = 0;int columnCount = 0;Row row = sheet.createRow(rowCount++);for (String labelName : getOrderLabels()) {Cell cel = row.createCell(columnCount++);cel.setCellValue(labelName);cel.setCellStyle(getColumnHeaderCellStyle());}类classz = data.get(0).getClass();对于(T t :数据){row = sheet.createRow(rowCount++);列数 = 0;对于(字符串标签:getOrderLabels()){String methodName = getFieldLabelMap().get(label);Cell cel = row.createCell(columnCount);Method method = classz.getMethod(methodName);对象值 = method.invoke(t, (Object[]) null);如果(值！= null){如果(字符串的值实例){cel.setCellValue((String) 值);} else if (value instanceof Long) {cel.setCellValue((长)值);} else if (value instanceof Integer) {cel.setCellValue((Integer) value);} else if (value instanceof Double) {cel.setCellValue((双)值);}}列数++；}}}公共地图<字符串，字符串>getFieldLabelMap() {返回 fieldLabelMap;}public void setFieldLabelMap(Map fieldLabelMap) {this.fieldLabelMap = fieldLabelMap;}公共列表<字符串>getOrderLabels() {退货单标签；}public void setOrderLabels(List orderLabels) {this.orderLabels = orderLabels;}公共字符串大写(字符串字符串){字符串大写 = string.substring(0, 1).toUpperCase();返回大写 + string.substring(1);}公共字符串去大写(字符串字符串){字符串大写 = string.substring(0, 1).toLowerCase();返回大写 + string.substring(1);}公共字符串 getWorkbookName() {返回工作簿名称；}public void setWorkbookName(String workbookName) {this.workbookName = 工作簿名称；}void setWorkbook(HSSFWorkbook 工作簿){this.workbook = 工作簿；}工作簿 getWorkbook() {返还工作簿；}公共 CellStyle getColumnHeaderCellStyle() {返回 columnHeaderCellStyle;}public void setColumnHeaderCellStyle(CellStyle columnHeaderCellStyle) {this.columnHeaderCellStyle = columnHeaderCellStyle;}}包 com.excel.Sample.Model;导入 java.util.Date;导入 javax.persistence.Column;导入 javax.persistence.Entity;导入 javax.persistence.GeneratedValue;导入 javax.persistence.Id;导入 javax.persistence.Table;导入 com.excel.Sample.ExcelAnn.ExcelColumn;@实体@Table(name = "目录")公共类目录{@ID@GeneratedValue@Column(name = "id")私有整数 ID；@Column(name = "Test_Name")私人字符串名称；@Column(name = "Path", nullable = false)私有字符串路径；@Column(name = "目录", nullable = false)私有字符串目录；@Column(name = "ContainedFiles", nullable = false)私有整数包含文件；@Column(name = "DateFormate", nullable = false)私人日期日期格式；公共整数 getId() {返回标识；}公共无效setId(整数ID){this.id = id;}@ExcelColumn(label = "测试名称")公共字符串 getName() {返回名称；}公共无效集名称(字符串名称){this.name = 名称；}@ExcelColumn(label = "路径")公共字符串 getPath() {返回路径；}公共无效setPath(字符串路径){this.path = 路径；}@ExcelColumn(label = "目录")公共字符串 getDirectory() {返回目录；}公共无效设置目录(字符串目录){this.directory = 目录；}@ExcelColumn(label = "ContainedFiles")公共整数 getContainedFiles() {返回包含的文件；}public void setContainedFiles(Integer containsFiles) {this.containedFiles = containsFiles;}@ExcelColumn(label = "DateFormate")公共日期 getDateFormate() {返回日期格式；}public void setDateFormate(Date dateFormate) {this.dateFormate = dateFormate;}}包 com.excel.Sample.Servlet;导入 java.io.IOException;导入 java.io.PrintWriter;导入 java.text.SimpleDateFormat;导入 java.util.List;导入 javax.servlet.ServletException;导入 javax.servlet.http.HttpServlet;导入 javax.servlet.http.HttpServletRequest;导入 javax.servlet.http.HttpServletResponse;导入 org.apache.commons.fileupload.FileItem;导入 org.apache.commons.fileupload.FileItemFactory;导入 org.apache.commons.fileupload.FileUploadException;导入 org.apache.commons.fileupload.disk.DiskFileItemFactory;导入 org.apache.commons.fileupload.servlet.ServletFileUpload;导入 org.hibernate.HibernateException;导入 org.hibernate.Session;导入 org.hibernate.Transaction;导入 com.excel.Sample.Actions.ExcelAction;导入 com.excel.Sample.Model.Directory;导入 com.excel.util.HibernateUtil;/*** Servlet 实现类 ExcelSample*/公共类 ExcelSample 扩展 HttpServlet {private static final long serialVersionUID = 1L;public static String modelName = "com.excel.Sample.Model.Directory";/*** @see HttpServlet#HttpServlet()*/公共 ExcelSample() {极好的();//TODO 自动生成的构造函数存根}/*** @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse* 回复)*/protected void doGet(HttpServletRequest request,HttpServletResponse 响应) 抛出 ServletException, IOException {//TODO 自动生成的方法存根}/*** @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse* 回复)*/protected void doPost(HttpServletRequest 请求，HttpServletResponse 响应) 抛出 ServletException, IOException {//TODO 自动生成的方法存根PrintWriter out = response.getWriter();尝试 {boolean isMultipart = ServletFileUpload.isMultipartContent(request);如果(！isMultipart){} 别的 {FileItemFactory factory = new DiskFileItemFactory();ServletFileUpload upload = new ServletFileUpload(factory);列表项 = null;尝试 {items = upload.parseRequest(request);} catch (FileUploadException e) {e.getMessage();}FileItem item = (FileItem) items.get(0);尝试 {ExcelAction ea = new ExcelAction();列表<目录>e = ea.readData(modelName, item.getInputStream());out.println("");for (int i = 1; i < e.size(); i++) {目录 ex = (目录) e.get(i);会话会话 = HibernateUtil.getSessionFactory().openSession();交易交易=空；尝试 {交易 = session.beginTransaction();session.save(ex);交易.提交()；} catch (HibernateException exp) {事务回滚()；exp.printStackTrace();} 最后 {session.close();}String date = new SimpleDateFormat("dd/MM/yyyy").format(ex.getDateFormate());out.println("<tr><td>" + ex.getContainedFiles()+ " </td><td> " + ex.getDirectory()+ " </td><td> " + ex.getPath() + " </td><td> "+ ex.getName() + "</td><td> " + 日期+ "</td></tr>");}out.println("</table>");} 捕获(异常 e){System.out.println(e);}}} 捕获(异常 e){System.out.println(e);}}}- Excel For Mate 如下
<块引用>
测试名称路径目录 ContainedFiles DateFormate.settings/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/.settings 目录 1 5/2/2013src/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/src 目录 1 12/3/2013.classpath1/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/.classpath1 file1 1 11/4/2013测试/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/test 目录 1 3/5/2013test.xls/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/test.xls 文件 11 6/6/2013.project/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/.project 文件 1 12/7/2013bin/home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/bin 目录 1 6/8/2013
I am using apache poi to read the data from excel to mysql table. And i had did it for excel sheet with 2 fields. my code is like,
try
{
fis = new FileInputStream(filePath);
//creating workbook, sheet(from poi jar file)
HSSFWorkbook workBook = new HSSFWorkbook(fis);
HSSFSheet sheet = workBook.getSheetAt(0);
//Iterator for iterating rows in sheet
Iterator itr = sheet.iterator();
while(itr.hasNext())
{
HSSFRow row = (HSSFRow) itr.next();
Iterator cell = row.cellIterator();
List data = new ArrayList();
while(cell.hasNext())
{
HSSFCell value = (HSSFCell) cell.next();
if (value.getCellType() == Cell.CELL_TYPE_NUMERIC)
{
data.add(value.getNumericCellValue());
}
else if (value.getCellType() == Cell.CELL_TYPE_BOOLEAN)
{
data.add(value.getBooleanCellValue());
}
else if (value.getCellType() == Cell.CELL_TYPE_STRING)
{
data.add(value.getStringCellValue());
}
}
sheetData.add(data);
}
}
catch (Exception e) {
// TODO: handle exception
}
And the put the data on an arraylist. ten inserted it to database.. But
now i need the system to get the excel files with variale number of cloumns. Can i achiece it? I just need some help badly. So plz help me with some ideas and possibly with some codes.
thanks in advance.
sujith
解决方案 We Can Also Use Simple And Best Way 2 Upload An Excel Sheet Into Database
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=ISO-8859-1">
<title>Insert title here</title>
</head>
<body>
<form action="./ExcelSample" method="post" id="formdata"
enctype="multipart/form-data" onsubmit="return excelUpload('Conform')">
<label for="inputSuccess2" class="control-label">
<div id="scn">Select Excel File</div>
</label> <input type="file" name="excel" id="excelfile"
class="form-control active"> <label class="control-label"> </label>
<input type="submit" class="form-control btn btn-orange" id="Upload"
value="Upload"></form>
</body>
</html>
package com.excel.Sample.ExcelAnn;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(value=ElementType.METHOD)
public @interface ExcelColumn {
boolean ignore() default false;
String label() default "";
}
package com.excel.Sample.ExcelAnn;
import java.lang.annotation.ElementType;
import java.lang.annotation.Retention;
import java.lang.annotation.RetentionPolicy;
import java.lang.annotation.Target;
@Retention(RetentionPolicy.RUNTIME)
@Target(value=ElementType.TYPE)
public @interface ExcelReport {
String reportName();
}
package com.excel.Sample.Actions;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.lang.reflect.Method;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.Iterator;
import java.util.List;
import java.util.Map;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import com.excel.Sample.ExcelAnn.ExcelColumn;
import com.excel.Sample.ExcelAnn.ExcelReport;
public class ExcelAction {
private HSSFWorkbook workbook = null;
private String workbookName = "Book1.xls";
private Map<String, String> fieldLabelMap = new HashMap<String, String>();
private List<String> orderLabels = new ArrayList<String>();
private CellStyle columnHeaderCellStyle = null;
public ExcelAction() {
initialize();
}
private void initialize() {
setWorkbook(new HSSFWorkbook());
setColumnHeaderCellStyle(createColumnHeaderCellStyle());
}
private CellStyle createColumnHeaderCellStyle() {
CellStyle cellStyle = getWorkbook().createCellStyle();
cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
cellStyle.setFillBackgroundColor(new HSSFColor.GREY_25_PERCENT()
.getIndex());
cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
return cellStyle;
}
public void closeWorksheet() {
FileOutputStream fileOut;
try {
fileOut = new FileOutputStream(getWorkbookName());
getWorkbook().write(fileOut);
fileOut.close();
} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}
private HSSFSheet getSheetWithName(String name) {
HSSFSheet sheet = workbook.getSheet(name);
return sheet;
}
private void initializeForRead(InputStream inp) throws IOException {
workbook = new HSSFWorkbook(inp);
}
private <T> void processAnnotations(T object) {
Class<?> clazz = object.getClass();
ExcelReport reportAnnotation = (ExcelReport) clazz
.getAnnotation(ExcelReport.class);
for (Method method : clazz.getMethods()) {
ExcelColumn excelColumn = method.getAnnotation(ExcelColumn.class);
if ((excelColumn != null) && !excelColumn.ignore()) {
getFieldLabelMap().put(excelColumn.label(), method.getName());
getOrderLabels().add(excelColumn.label());
}
}
}
@SuppressWarnings("unchecked")
public <T> List<T> readData(String classname, InputStream inp)
throws Exception {
Class clazz = Class.forName(classname);
processAnnotations(clazz.newInstance());
initializeForRead(inp);
HSSFSheet sheet = getSheetWithName("Sheet1");
List<T> result = new ArrayList<T>();
Map<String, String> mp = new HashMap<String, String>();
Iterator<Row> rowIterator = sheet.rowIterator();
int rowCount = 0;
while (rowIterator.hasNext()) {
T one = (T) clazz.newInstance();
try {
int colCount = 0;
result.add(one);
Row row = rowIterator.next();
Iterator<Cell> cellIterator = row.cellIterator();
while (cellIterator.hasNext()) {
Cell cell = cellIterator.next();
if (rowCount == 0) {
mp.put(colCount + "", cell.getStringCellValue()
.toString().trim());
} else {
int type = cell.getCellType();
String labelName = mp.get(colCount + "");
String getter = getFieldLabelMap().get(labelName);
String fieldName = getter.substring(3);
fieldName = decapitalize(fieldName);
Method method = constructMethod(clazz, fieldName);
if (type == 1) {
String value = cell.getStringCellValue();
Object[] values = new Object[1];
values[0] = value;
method.invoke(one, values);
} else if (type == 0) {
Double num = cell.getNumericCellValue();
Class<?> returnType = getGetterReturnClass(clazz,
fieldName);
if (returnType == Integer.class) {
method.invoke(one,(Integer) num.intValue());
} else if (returnType == Double.class) {
method.invoke(one, (Integer) num.intValue());
} else if (returnType == Float.class) {
method.invoke(one, num.floatValue());
} else if (returnType == Date.class) {
method.invoke(one, cell.getDateCellValue());
}
} else if (type == 3) {
double num = cell.getNumericCellValue();
Object[] values = new Object[1];
values[0] = num;
method.invoke(one, values);
}
}
colCount++;
}
} catch (Exception e) {
System.out.println(e);
}
rowCount++;
}
return result;
}
private Class<?> getGetterReturnClass(Class<?> clazz, String fieldName) {
String methodName = "get" + capitalize(fieldName);
Class<?> returnType = null;
for (Method method : clazz.getMethods()) {
if (method.getName().equals(methodName)) {
returnType = method.getReturnType();
break;
}
}
return returnType;
}
@SuppressWarnings("unchecked")
private Method constructMethod(Class clazz, String fieldName)
throws SecurityException, NoSuchMethodException {
Class<?> fieldClass = getGetterReturnClass(clazz, fieldName);
return clazz.getMethod("set" + capitalize(fieldName), fieldClass);
}
public <T> void writeReportToExcel(List<T> data) throws Exception {
processAnnotations(data.get(0));
Sheet sheet = getWorkbook().createSheet(
data.get(0).getClass().getName());
int rowCount = 0;
int columnCount = 0;
Row row = sheet.createRow(rowCount++);
for (String labelName : getOrderLabels()) {
Cell cel = row.createCell(columnCount++);
cel.setCellValue(labelName);
cel.setCellStyle(getColumnHeaderCellStyle());
}
Class<? extends Object> classz = data.get(0).getClass();
for (T t : data) {
row = sheet.createRow(rowCount++);
columnCount = 0;
for (String label : getOrderLabels()) {
String methodName = getFieldLabelMap().get(label);
Cell cel = row.createCell(columnCount);
Method method = classz.getMethod(methodName);
Object value = method.invoke(t, (Object[]) null);
if (value != null) {
if (value instanceof String) {
cel.setCellValue((String) value);
} else if (value instanceof Long) {
cel.setCellValue((Long) value);
} else if (value instanceof Integer) {
cel.setCellValue((Integer) value);
} else if (value instanceof Double) {
cel.setCellValue((Double) value);
}
}
columnCount++;
}
}
}
public Map<String, String> getFieldLabelMap() {
return fieldLabelMap;
}
public void setFieldLabelMap(Map<String, String> fieldLabelMap) {
this.fieldLabelMap = fieldLabelMap;
}
public List<String> getOrderLabels() {
return orderLabels;
}
public void setOrderLabels(List<String> orderLabels) {
this.orderLabels = orderLabels;
}
public String capitalize(String string) {
String capital = string.substring(0, 1).toUpperCase();
return capital + string.substring(1);
}
public String decapitalize(String string) {
String capital = string.substring(0, 1).toLowerCase();
return capital + string.substring(1);
}
public String getWorkbookName() {
return workbookName;
}
public void setWorkbookName(String workbookName) {
this.workbookName = workbookName;
}
void setWorkbook(HSSFWorkbook workbook) {
this.workbook = workbook;
}
Workbook getWorkbook() {
return workbook;
}
public CellStyle getColumnHeaderCellStyle() {
return columnHeaderCellStyle;
}
public void setColumnHeaderCellStyle(CellStyle columnHeaderCellStyle) {
this.columnHeaderCellStyle = columnHeaderCellStyle;
}
}
package com.excel.Sample.Model;
import java.util.Date;
import javax.persistence.Column;
import javax.persistence.Entity;
import javax.persistence.GeneratedValue;
import javax.persistence.Id;
import javax.persistence.Table;
import com.excel.Sample.ExcelAnn.ExcelColumn;
@Entity
@Table(name = "directory")
public class Directory {
@Id
@GeneratedValue
@Column(name = "id")
private Integer id;
@Column(name = "Test_Name")
private String name;
@Column(name = "Path", nullable = false)
private String path;
@Column(name = "Directory", nullable = false)
private String directory;
@Column(name = "ContainedFiles", nullable = false)
private Integer containedFiles;
@Column(name = "DateFormate", nullable = false)
private Date dateFormate;
public Integer getId() {
return id;
}
public void setId(Integer id) {
this.id = id;
}
@ExcelColumn(label = "Test Name")
public String getName() {
return name;
}
public void setName(String name) {
this.name = name;
}
@ExcelColumn(label = "Path")
public String getPath() {
return path;
}
public void setPath(String path) {
this.path = path;
}
@ExcelColumn(label = "Directory")
public String getDirectory() {
return directory;
}
public void setDirectory(String directory) {
this.directory = directory;
}
@ExcelColumn(label = "ContainedFiles")
public Integer getContainedFiles() {
return containedFiles;
}
public void setContainedFiles(Integer containedFiles) {
this.containedFiles = containedFiles;
}
@ExcelColumn(label = "DateFormate")
public Date getDateFormate() {
return dateFormate;
}
public void setDateFormate(Date dateFormate) {
this.dateFormate = dateFormate;
}
}
package com.excel.Sample.Servlet;
import java.io.IOException;
import java.io.PrintWriter;
import java.text.SimpleDateFormat;
import java.util.List;
import javax.servlet.ServletException;
import javax.servlet.http.HttpServlet;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import org.apache.commons.fileupload.FileItem;
import org.apache.commons.fileupload.FileItemFactory;
import org.apache.commons.fileupload.FileUploadException;
import org.apache.commons.fileupload.disk.DiskFileItemFactory;
import org.apache.commons.fileupload.servlet.ServletFileUpload;
import org.hibernate.HibernateException;
import org.hibernate.Session;
import org.hibernate.Transaction;
import com.excel.Sample.Actions.ExcelAction;
import com.excel.Sample.Model.Directory;
import com.excel.util.HibernateUtil;
/**
* Servlet implementation class ExcelSample
*/
public class ExcelSample extends HttpServlet {
private static final long serialVersionUID = 1L;
public static String modelName = "com.excel.Sample.Model.Directory";
/**
* @see HttpServlet#HttpServlet()
*/
public ExcelSample() {
super();
// TODO Auto-generated constructor stub
}
/**
* @see HttpServlet#doGet(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doGet(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
}
/**
* @see HttpServlet#doPost(HttpServletRequest request, HttpServletResponse
* response)
*/
protected void doPost(HttpServletRequest request,
HttpServletResponse response) throws ServletException, IOException {
// TODO Auto-generated method stub
PrintWriter out = response.getWriter();
try {
boolean isMultipart = ServletFileUpload.isMultipartContent(request);
if (!isMultipart) {
} else {
FileItemFactory factory = new DiskFileItemFactory();
ServletFileUpload upload = new ServletFileUpload(factory);
List items = null;
try {
items = upload.parseRequest(request);
} catch (FileUploadException e) {
e.getMessage();
}
FileItem item = (FileItem) items.get(0);
try {
ExcelAction ea = new ExcelAction();
List<Directory> e = ea.readData(modelName, item
.getInputStream());
out.println("<table>");
for (int i = 1; i < e.size(); i++) {
Directory ex = (Directory) e.get(i);
Session session = HibernateUtil.getSessionFactory()
.openSession();
Transaction transaction = null;
try {
transaction = session.beginTransaction();
session.save(ex);
transaction.commit();
} catch (HibernateException exp) {
transaction.rollback();
exp.printStackTrace();
} finally {
session.close();
}
String date = new SimpleDateFormat("dd/MM/yyyy")
.format(ex.getDateFormate());
out.println("<tr><td>" + ex.getContainedFiles()
+ " </td><td> " + ex.getDirectory()
+ " </td><td> " + ex.getPath() + " </td><td> "
+ ex.getName() + "</td><td> " + date
+ "</td></tr>");
}
out.println("</table>");
} catch (Exception e) {
System.out.println(e);
}
}
} catch (Exception e) {
System.out.println(e);
}
}
}
- Excel For Mate Is As Follows
Test Name Path Directory ContainedFiles DateFormate
.settings /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/.settings directory 1 5/2/2013
src /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/src directory 1 12/3/2013
.classpath1 /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/.classpath1 file1 1 11/4/2013
test /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/test directory 1 3/5/2013
test.xls /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/test.xls file 11 6/6/2013
.project /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/.project file 1 12/7/2013
bin /home/david/dev/workspaces/Dec2009/ExcelAnnotationReport/bin directory 1 6/8/2013
这篇关于使用java将变量列excel表导入mysql的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
查看全文
登录
关闭
扫码关注1秒登录
发送“验证码”获取
|
15天全站免登陆