Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Writing to an Excel File using Apache POI

Posted on 2012-03-15
3
Medium Priority
?
906 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 2000 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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Question has a verified solution.

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

INTRODUCTION Working with files is a moderately common task in Java.  For most projects hard coding the file names, using parameters in configuration files, or using command-line arguments is sufficient.   However, when your application has vi…
In this post we will learn how to make Android Gesture Tutorial and give different functionality whenever a user Touch or Scroll android screen.
This tutorial will introduce the viewer to VisualVM for the Java platform application. This video explains an example program and covers the Overview, Monitor, and Heap Dump tabs.
Viewers will learn how to properly install Eclipse with the necessary JDK, and will take a look at an introductory Java program. Download Eclipse installation zip file: Extract files from zip file: Download and install JDK 8: Open Eclipse and …
Suggested Courses

610 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