Solved

Writing to an Excel File using Apache POI

Posted on 2012-03-15
3
868 Views
Last Modified: 2012-06-27
Hi, I have a HashMap with some 3000 key value pairs and I want to print all the values of the HashMap to a Excel sheet. Can someone give me the complete code if you dont mind. Thank you.
0
Comment
Question by:vgsrikanth
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 47

Expert Comment

by:for_yan
ID: 37727468
This is your complete code, which puts three pairs form Hashmpa onto Excel Spreadsheet
You'll see

two b
one a
three c

in the to three rows of your excel file

Tested it with poi-3.7.jar

import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class HSSFCreate {

	public static void main(String[] args) {
		try {

            HashMap<String, String> map = new HashMap<String, String>();
            map.put("one","a");
            map.put("two","b");
             map.put("three","c");

         //   Enumeration en = map.keys();

            Set<Map.Entry<String,String>> ss = map.entrySet();



			FileOutputStream fileOut = new FileOutputStream("poi-test.xls");
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

			// index from 0,0... cell A1 is cell(0,0)

            int j = 0;
            for(Map.Entry<String,String> men : ss){

			HSSFRow row1 = worksheet.createRow((short) j);
                j++;

			HSSFCell cellA1 = row1.createCell((short) 0);
			cellA1.setCellValue(men.getKey());
			HSSFCellStyle cellStyle = workbook.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.GOLD.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellA1.setCellStyle(cellStyle);

			HSSFCell cellB1 = row1.createCell((short) 1);
			cellB1.setCellValue(men.getValue());
			cellStyle = workbook.createCellStyle();
			cellStyle.setFillForegroundColor(HSSFColor.LIGHT_CORNFLOWER_BLUE.index);
			cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
			cellB1.setCellStyle(cellStyle);

			HSSFCell cellC1 = row1.createCell((short) 2);
			cellC1.setCellValue(true);

			HSSFCell cellD1 = row1.createCell((short) 3);
			cellD1.setCellValue(new Date());
			cellStyle = workbook.createCellStyle();
			cellStyle.setDataFormat(HSSFDataFormat
					.getBuiltinFormat("m/d/yy h:mm"));
			cellD1.setCellStyle(cellStyle);
            }

			workbook.write(fileOut);
			fileOut.flush();
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

}

Open in new window

0
 

Author Comment

by:vgsrikanth
ID: 37727533
Thank you Yan for the solution.My concern is if I have to print 3000 values then do i have to create 3000 cells using createcell() function. If you dont mind can you give code to print 3000 values of HashMap.
0
 
LVL 47

Accepted Solution

by:
for_yan earned 500 total points
ID: 37727594
Don't worry, just remove the style - it is too much with style
with this code i wrote 3000 rows - two cell in each row - and without
any problems
 
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.*;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFCellStyle;
import org.apache.poi.hssf.usermodel.HSSFDataFormat;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.hssf.util.HSSFColor;

public class HSSFCreate {

	public static void main(String[] args) {
		try {

            HashMap<String, String> map = new HashMap<String, String>();
            map.put("one","a");
            map.put("two","b");
             map.put("three","c");

         //   Enumeration en = map.keys();

            Set<Map.Entry<String,String>> ss = map.entrySet();



			FileOutputStream fileOut = new FileOutputStream("poi-test.xls");
			HSSFWorkbook workbook = new HSSFWorkbook();
			HSSFSheet worksheet = workbook.createSheet("POI Worksheet");

			// index from 0,0... cell A1 is cell(0,0)

            int j = 0;
     

                    for(int jj=0; jj<3000; jj++){

			HSSFRow row1 = worksheet.createRow((short) jj);
                j++;

			HSSFCell cellA1 = row1.createCell((short) 0);
			cellA1.setCellValue("Heelo");


			HSSFCell cellB1 = row1.createCell((short) 1);
			cellB1.setCellValue("OK");
	

			HSSFCell cellC1 = row1.createCell((short) 2);
			cellC1.setCellValue(true);

			HSSFCell cellD1 = row1.createCell((short) 3);

            }

			workbook.write(fileOut);
			fileOut.flush();
			fileOut.close();
		} catch (FileNotFoundException e) {
			e.printStackTrace();
		} catch (IOException e) {
			e.printStackTrace();
		}

	}

}

Open in new window

0

Featured Post

Salesforce Made Easy to Use

On-screen guidance at the moment of need enables you & your employees to focus on the core, you can now boost your adoption rates swiftly and simply with one easy tool.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

For beginner Java programmers or at least those new to the Eclipse IDE, the following tutorial will show some (four) ways in which you can import your Java projects to your Eclipse workbench. Introduction While learning Java can be done with…
This was posted to the Netbeans forum a Feb, 2010 and I also sent it to Verisign. Who didn't help much in my struggles to get my application signed. ------------------------- Start The idea here is to target your cell phones with the correct…
Viewers will learn one way to get user input in Java. Introduce the Scanner object: Declare the variable that stores the user input: An example prompting the user for input: Methods you need to invoke in order to properly get  user input:
This tutorial covers a practical example of lazy loading technique and early loading technique in a Singleton Design Pattern.

688 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question