Solved

Writing to an Excel File using Apache POI

Posted on 2012-03-15
3
864 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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
servlet example 11 75
jdbc error in jsp application 20 88
Android development question 2 108
CSV file parsing thru Java 13 54
Go is an acronym of golang, is a programming language developed Google in 2007. Go is a new language that is mostly in the C family, with significant input from Pascal/Modula/Oberon family. Hence Go arisen as low-level language with fast compilation…
Java functions are among the best things for programmers to work with as Java sites can be very easy to read and prepare. Java especially simplifies many processes in the coding industry as it helps integrate many forms of technology and different d…
Video by: Michael
Viewers learn about how to reduce the potential repetitiveness of coding in main by developing methods to perform specific tasks for their program. Additionally, objects are introduced for the purpose of learning how to call methods in Java. Define …
Viewers will learn about the different types of variables in Java and how to declare them. Decide the type of variable desired: Put the keyword corresponding to the type of variable in front of the variable name: Use the equal sign to assign a v…
Suggested Courses

732 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