做过java的人应该都经历过将数据库中的数据导出到excel中的情况,一般来说会使用poi或jxl等成型的工具,现在向大家推荐一款很好用的工具ExcelUtils,它是基于poi的。
ExcelUtils:an excel report template engine for Java
从网站上可以很容易的学习到ExcelUtils的使用方法,这里写一个简单的例子:
excel模板:/WEB-INF/xls/balance.xls
java:我使用的spring2.5基于注解的controller
import static com.netqin.common.GlobalNamingConstant.OPERATE_LIST;
import java.io.IOException;
import java.util.List;
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import net.sf.excelutils.ExcelException;
import net.sf.excelutils.ExcelUtils;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.ui.Model;
import org.springframework.web.bind.annotation.RequestMapping;
import com.netqin.common.Sorter;
import com.netqin.common.annotation.UserOperateLog;
import com.netqin.function.settlement.model.BalanceApplyInfo;
import com.netqin.function.settlement.service.IChannelChargeService;
import com.netqin.web.SimpleBaseController;
@Controller
public class ChannelChargeController{
@Autowired
private IChannelChargeService channelChargeService;
/**
* 描述 : .
*
*
* @param bean
* @param model
* @param flog
* @return
*/
@RequestMapping("/settlement/export_excel.do")
@UserOperateLog(value = "报表导出", type = OPERATE_LIST)
public String handleExportExcel(BalanceApplyInfo bean, Model model,
int flog, HttpServletRequest request, HttpServletResponse response) {
List list = channelChargeService.selectAll(bean,
new Sorter(), null, flog);
ExcelUtils.addValue("list", list);
String config = "/WEB-INF/xls/balance.xls";
response.reset();
response.setContentType("application/vnd.ms-excel");
try {
ExcelUtils.export(request.getSession().getServletContext(), config,
response.getOutputStream());
} catch (ExcelException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
return null;
}
}
值得注意的是,在使用#sum allusernum on ${list}进行汇总时,allusernum 的数据类型一定要是double的,否则不能进行汇总,这也许是ExcelUtils的bug.