使用jxl读取excel内容，并转换成Json,用于Datagrid

一、上传excel文件，得到InputStream,由InputStream得到Jxl中的Workbook,取出内容，存到二维数组中。
 

 

1、使用 Jquery Uploadify 插件（http://www.uploadify.com/ ），得到FileItem,由FileItem得到InputStream。

   String[][] excelContent = getExcelContent(fileItem.getInputStream());

 

 

2、将Excel内容存放到二维数组中。

 

public String[][] getExcelContent(InputStream is) {
  try {
   Workbook workBook = Workbook.getWorkbook(is);
   Sheet sheet = workBook.getSheet(0);
   int sheetColumns = sheet.getColumns();
   int sheetRows = sheet.getRows();
   // excel内容
   String[][] excelContent = new String[sheetRows][sheetColumns];
   for (int i = 0; i < sheetRows; i++) {
    for (int j = 0; j < sheetColumns; j++) {
     // 将excel值放入二维数组excelContent中
     excelContent[i][j] = sheet.getCell(j, i).getContents();
    }
   }

 

 

二、将二维数组转换成JSON(stringBuffer)格式为：[{"filed1":,"value"},{"filed2":,"value2"}]

stringBuffer = convertToJson(excelContent);

 

  拼字符串花了我很多时间

private static final String QUOTATION_MARKS = "\"";  //双引号
private static final String COMMA = ",";   //逗号

public StringBuffer convertToJson(String[][] srcArray) {

		StringBuffer stringBuffer = new StringBuffer();
		boolean first = true;
		stringBuffer.append("[");

		for (int i = 0; i < srcArray.length; i++) {
			String[] arrayItem = srcArray[i];
			if (!first) {
				stringBuffer.append(COMMA);
			}
			stringBuffer.append("{");

			boolean first2 = true;
			for (int j = 0; j < arrayItem.length; j++) {

				if (!first2) {
					stringBuffer.append(COMMA);
				}
				stringBuffer.append(QUOTATION_MARKS + "field" + j
						+ QUOTATION_MARKS + ":\"" + arrayItem[j]
						+ QUOTATION_MARKS);
				first2 = false;
			}
			stringBuffer.append("}");
			first = false;
		}

		stringBuffer.append("]");
		return stringBuffer;

	}

 三、生成可用于DataGrid加载的Json格式，即 {"total":n,"rows":[{"filed1":,"value"},{"filed2":,"value2"}]}。

 

sheetRows = excelContent.length;
sheetColumns = excelContent[0].length;
 
StringBuffer stringBuffer2 = new StringBuffer("");
stringBuffer2.append("{");
   stringBuffer2.append(QUOTATION_MARKS + "total"
     + QUOTATION_MARKS + ":" + sheetRows + COMMA
     + QUOTATION_MARKS + "rows" + QUOTATION_MARKS + ":");
   stringBuffer2.append(stringBuffer);
stringBuffer2.append("}");