|
|
This is a long shot, but does anyone know how to set the ShrinkToFit property for an Excel Cell or CellStyle and apply it in POI?
|
|
|
|
|
|
Thanks. I'd actually got thar far recently after hours of searching through namespaces/classes... what I can't figure out is how to apply the ExtendedFormatRecord to an existing CellStyle.
|
|
|
|
|
Hi David, Nevermind the first solution you can use the Java Excel API [^]
import java.io.*;
import jxl.*;
import jxl.write.*;
public class ShrinkToFitExample
{
public static void main(String[] args) throws Exception
{
WritableWorkbook workbook = Workbook.createWorkbook(new File("output.xls"));
WritableSheet sheet = workbook.createSheet("First Sheet", 0);
WritableCellFormat arial10format = new WritableCellFormat();
arial10format.setShrinkToFit(true);
Label label2 = new Label(1,0, "demo Text", arial10format);
sheet.addCell(label2);
workbook.write();
workbook.close();
}
}
|
|
|
|
|
Unfortunately, I have to use POI - I'm actually not using Java but C#.
I'm using NPOI which is a port of the Java POI to .NET. I asked here because it is a direct port of the Java solution so should work identically (assuming there isn't a bug or ommision in the port). NPOI isn't widely used yet so there's no one to ask in the C# world!
Thanks for the time and trouble you've gone to anyway 
|
|
|
|
|
I've just found the solution!
HSSFCellStyle doesn't have a property ShrinkToFit or SetShrinkToFit method. I created a property that gets/sets the internal ExternalFormatRecord.ShrinkToFit property. To be successful, it also needs to set the ExternalFormatRecord.XFType to XF_CELL.
C# solution:
public bool ShrinkToFit
{
get
{
return format.ShrinkToFit;
}
set
{
format.IsIndentNotParentAlignment = (true);
format.ShrinkToFit = (value);
format.XFType = ExtendedFormatRecord.XF_CELL;
}
}
|
|
|
|
|
Hi David, you gave me a push to actually do it in Java so I asked Tony from NOPI project and he added the shrink property to HSSFCellStyle[^]. The new Solution would be:
HSSFCellStyle cellstyle1 = hssfworkbook.CreateCellStyle();
cellstyle1.ShrinkToFit = true;
HSSFCellStyle style = workbook.createCellStyle();
style.setShrinkToFit(true);
To do the same in Java POI I added the shrinktofit property to the HSSFCellStyle and recompiled the library:
Link[^] All you need to do is overwrite the same jar in the poi-bin-3.5-beta6-20090622.zip[^] folder.
Thanks and Regards to you all
|
|
|
|
|
Cool  I posted it on the NPOI discussion board (and the solution) and got a response from Tony.
I actually have another issue now - I need to insert an image in a header.
In VBA, you just set the relavent header section to "&G" (this is in the constants in POI too) and then use the sheet's PageLayout.LeftHeaderPicture (or Centre/Right) to the file path as shown in this thread[^]. I can't find a way to do this in POI. I can of course add images and place them but that requires cell references to anchor to which doesn't apply to headers/footers.
Any ideas? I have asked Tony but no response so far...
|
|
|
|
|
I believe you want to use your code server side without having Excel be installed. But for those of us who want to add image to header I did the following:
public static void SetLeftHeaderPicture(String Workbook,String Sheet,String Image)
{
Application excelApp = new ApplicationClass();
excelApp.Visible = false;
Workbook newWorkbook = excelApp.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
string workbookPath = Workbook;
Workbook excelWorkbook = excelApp.Workbooks.Open(workbookPath, 0, false, 5, "", "", false, XlPlatform.xlWindows, "", true, false, 0, true, false, false);
Sheets excelSheets = excelWorkbook.Worksheets;
string currentSheet = Sheet;
Worksheet excelWorksheet = (Worksheet)excelSheets.get_Item(currentSheet);
excelWorksheet = (Worksheet)excelWorkbook.ActiveSheet;
excelWorksheet.PageSetup.LeftHeaderPicture.Filename = Path.GetFullPath(Image);
excelWorksheet.PageSetup.LeftHeader = "&G";
excelWorksheet.PageSetup.LeftHeaderPicture.Height = 100;
excelWorksheet.PageSetup.LeftHeaderPicture.Width = 150;
excelWorkbook.Save();
}
Just repeat the same method for the rest for example if you want to add right header then the things to change will be the method name will be SetRightHeaderPicture and the following lines would be:
excelWorksheet.PageSetup.RightHeaderPicture.Filename = Path.GetFullPath(Image);
excelWorksheet.PageSetup.RightHeader = "&G";
excelWorksheet.PageSetup.RightHeaderPicture.Height = 100;
excelWorksheet.PageSetup.RightHeaderPicture.Width = 150;
You can also add any other property you want by excelWorksheet.PageSetup.RightHeaderPicture.<"your selection"> = value
In Java it is a little different but will yield the same result.
1. Download the new Jar and HFPicture.exe from Link[^]
2. Set a Java project and paste the exe in the project directory and add a build reference to the jar
3. Code
HSSFHeader header = sheet.getHeader();
HeaderFooter.HFPicture("CH", "C:\\Excel.xls", "Sheet0", "c:\\image.jpg");
In addition to the code of setting the image in header for Java users we need:
StreamReader objReader = new StreamReader("HFPicture.txt");
string sLine = "";
ArrayList arrText = new ArrayList();
while (sLine != null)
{
sLine = objReader.ReadLine();
if (sLine != null)
arrText.Add(sLine);
}
objReader.Close();
if (arrText[0].ToString() == "LH")
SetLeftHeaderPicture(arrText[1].ToString(), arrText[2].ToString(), arrText[3].ToString());
if (arrText[0].ToString() == "CH")
SetCenterHeaderPicture(arrText[1].ToString(), arrText[2].ToString(), arrText[3].ToString());
if (arrText[0].ToString() == "RH")
SetRightHeaderPicture(arrText[1].ToString(), arrText[2].ToString(), arrText[3].ToString());
if (arrText[0].ToString() == "LF")
SetLeftFooterPicture(arrText[1].ToString(), arrText[2].ToString(), arrText[3].ToString());
if (arrText[0].ToString() == "CF")
SetCenterFooterPicture(arrText[1].ToString(), arrText[2].ToString(), arrText[3].ToString());
if (arrText[0].ToString() == "RF")
SetRightFooterPicture(arrText[1].ToString(), arrText[2].ToString(), arrText[3].ToString());
I hope this helps. If you develop the solution in pure NPOI and you post it here I will try to also do it in Java POI. Also refer that the changes in my previous post are also included in this build.
Regards
|
|
|
|
|
Cheers - could be just what I need Is there any source code (any language) available for the HFPicture.exe that you know of?
|
|
|
|
|
Sorry David the HFPicture.exe is the C# code in my previous post, I added the HFPicture method to the HeaderFooter class in the Java POI org.apache.poi.usermodel that simply outputs the parameters of that method to be inputted in the exe to add the picture header. I searched a lot for a good reference for the HFPicture and all I could find is the specification from Microsoft which does not say much so I used COM the only way I could think of. In C#, its easier just add the 6 methods to HeaderFooter class in NPOI and recompile. When I searched the Mail archives of POI they said that the HeaderFooter does not have a patriarch to be able to draw on it that was as version 3.2. I don't know though why they didn't implement it on the first hand?
Regards
|
|
|
|
|
Ah, I see how you've handled it now. Thanks again for your help.
This is for a machine without Excel on it unfortunately. It has been suggested elsewhere to use a template file with the image already in but that's not practical as I'd have to have a template for each company (their company logo needs to be in the header) this document is to be output for.
I may be able to figure it out by using BiffView[^] and or a hex editor to compare a file with and another without. 
|
|
|
|
|
Hi David, While I was looking at Biffview I stumbled on Microsoft OpenXML SDK[^] which can create a word[^] document with an Image header but for Excel [^]I give up for the day. Maybe you can get it to work?
|
|
|
|
|
Another Solution uses PHPExcel[^]. The idea here is after you create the Excel file call:
First we need Xampp[^] Only need the Apache and PHP, then copy the extracted file of PHPExcel to htdocs of Xampp
<?php
error_reporting(E_ALL);
require_once '../Classes/PHPExcel.php';
require_once '../Classes/PHPExcel/IOFactory.php';
echo date('H:i:s') . " Create new PHPExcel object\n";
$objPHPExcel = PHPExcel_IOFactory::load("Test.xlsx");
echo date('H:i:s') . " Set header/footer\n";
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddHeader('&L&G&C&HPlease treat this document as confidential!');
$objPHPExcel->getActiveSheet()->getHeaderFooter()->setOddFooter('&L&G&C&HPlease treat this document as confidential!');
echo date('H:i:s') . " Add a drawing to the header\n";
$objDrawing = new PHPExcel_Worksheet_HeaderFooterDrawing();
$objDrawing->setName('PHPExcel logo');
$objDrawing->setPath('./images/phpexcel_logo.gif');
$objDrawing->setHeight(36);
$objPHPExcel->getActiveSheet()->getHeaderFooter()->addImage($objDrawing, PHPExcel_Worksheet_HeaderFooter::IMAGE_FOOTER_LEFT);
$objPHPExcel->setActiveSheetIndex(0);
echo date('H:i:s') . " Write to Excel2007 format\n";
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('Test.xlsx');
That would add the header/footer. This is what I reached so far I will wrap it up with a nice hidden gui and post back. Do tell if it helps
Regards
|
|
|
|
|
Continuing previous post:
Control DOWNLOAD[^]
To use in C#
StreamWriter sw = new StreamWriter("HFPicture.txt");
sw.WriteLine("&L&G&C&HPlease treat this document as confidential!");
sw.WriteLine("C:\\image1.gif");
sw.WriteLine("Test.xlsx");
sw.WriteLine("Header");
sw.WriteLine("IMAGE_HEADER_LEFT");
sw.WriteLine("C:\\");
sw.Close();
System.Diagnostics .Process .Start ("HFPicturePHPExcel.exe");
To use in Java
PrintWriter out = new PrintWriter(new File("HFPicture.txt"));
out.println("&L&G&C&HPlease treat this document as confidential!");
out.println("C:\\image1.gif");
out.println("Test.xlsx");
out.println("Header");
out.println("IMAGE_HEADER_LEFT");
out.println("C:\\");
out.close();
Runtime.getRuntime().exec("HFPicturePHPExcel.exe");
Don't forget to read previous post to get the steps before this one.
Hope this helps
|
|
|
|
|
Hi,
I need to manage threads to a specific core.
In c# I know how to do that, now I need to know if it can
be done with JAVA.
thanks...
|
|
|
|
|
|
First thank you for your response.
I review the links you write and I didn't see
if there a way to choose the CPU/core to set the thread to ?
It is very imporant to me to choose the CPU/core.
In C#, one way to do that is using the library kernel32 and the method SetThreadAffinityMask.
Here is example in C#.
SetThreadAffinityMask(GetCurrentThread(), new IntPtr(2));
|
|
|
|
|
One thing to note that although C# came to be from Java and C++ etc.. they do not use a bijection method mapping. In my third link I wanted to mention that the Executor Service is the closest thing you can get to multi core programming Example[^]
Good Luck
|
|
|
|
|
We have to write a program that adds records to, searches records in, and deletes records from a hash table. So I have a little something but it is acting weird. Sometimes it only lets me add like 4 names in a row and then it will stop. Also in the example I have in the book they use a delete marker '#' to delete a record. I can mark the record to be deleted but it is not deleting it. Another problem I have is that if the user a enters a name that does not exist, it is supposed to display " record not found" but it does not do that. And last issue is that I would like the names to print in one column
First Name
Second Name
Third Name
Fourth Name ....etc.
Instead of the way it is printing it as a table. Is that possible? I got as far as I did thanks to an example in my book which was a bit different so there may still be some code in there that don't need to be. But I had to start somewhere....doing Baby Steps here. Any help is appreciated.
Here is what I got, it compiles and runs somewhat....at least until it doesn't do anything anymore. 
import java.io.*;
import java.io.File;
public class HashingUnit8{
private final int bucketSize = 2, tableSize = 3, stringLength = 20;
private final int recordLength = stringLength;
private final byte empty = ' ', delMarker = '#';
private long[] positions;
private BufferedReader buffer = new BufferedReader(
new InputStreamReader(System.in));
private RandomAccessFile outfile;
public HashingUnit8() {
}
private void print(byte[] s) {
for(int k = 0; k < s.length; k++)
System.out.print((char)s[k]);
}
private long hash(byte[] s) {
long xor = 0, pack;
int i, j, slength;
for (slength = s.length; s[slength-1] == ' '; slength--);
for (i = 0; i < slength; ) {
for (pack = j = 0; ; j++, i++) {
pack |= (long) s[i];
if (j == 3 || i == slength - 1) {
i++;
break;
}
pack <<= 8;
}
xor ^= pack;
}
return (xor % tableSize) * bucketSize * recordLength;
}
private byte[] getName() throws IOException {
System.out.print("Enter customers name: ");
String s = buffer.readLine();
for (int i = s.length(); i < recordLength; i++)
s += ' ';
return s.getBytes();
}
private int comparesTo(byte[] s1, byte[] s2) {
for (int i = 0; i < s1.length; i++)
if (s1[i] != s2[i])
return s1[i] - s2[i];
return 0;
}
void insert() throws IOException {
insertion(getName());
}
void insertion(byte[] line) throws IOException {
byte[] name = new byte[recordLength];
boolean done = false, inserted = false;
int counter = 0;
long address = hash(line);
outfile.seek(address);
while (!done && outfile.read(name) != -1) {
if (name[0] == empty || name[0] == delMarker) {
outfile.seek(address+counter*recordLength);
outfile.write(line);
done = inserted = true;
}
else if (comparesTo(name,line) == 0) {
print(line);
System.out.println(" is already in the file");
return;
}
else counter++;
if (counter == bucketSize)
done = true;
else outfile.seek(address+counter*recordLength);
}
if (!inserted) {
done = false;
counter = 0;
while (!done ) {
if (name[0] == delMarker)
done = true;
else if (comparesTo(name,line) == 0) {
print(line);
System.out.println(" is already in the file");
return;
}
else counter++;
}
}
}
private void delete() throws IOException {
byte[] line = getName();
long address = hash(line);
outfile.seek(address);
int counter = 0;
boolean done = false, deleted = false;
byte[] name = new byte[recordLength];
while (!done && outfile.read(name) != -1) {
if (comparesTo(line,name) == 0) {
outfile.seek(address+counter*recordLength);
outfile.write(delMarker);
done = deleted = true;
}
else counter++;
if (counter == bucketSize)
done = true;
else outfile.seek(address+counter*recordLength);
}
if (!deleted) {
done = false;
counter = 0;
while (!done ) {
if (comparesTo(line,name) == 0) {
done = deleted = true;
}
else counter++;
}
}
if (!deleted) {
print(line);
System.out.println(" is not in database");
}
}
public void processFile(String fileName) {
char command = '1';
byte[] line = new byte[recordLength];
String commandLine;
try {
(new File(".\\","outfile")).delete();
RandomAccessFile fIn = new RandomAccessFile(fileName,"rw");
outfile = new RandomAccessFile("outfile","rw");
for (int i = 1; i <= tableSize*bucketSize*recordLength; i++)
outfile.write(empty);
while (fIn.read(line) != -1)
insertion(line);
printFile("Customers:",outfile);
while (command != '3') {
System.out.print("Enter your choice "
+ "(1. insert, 2. delete, 3. exit): ");
commandLine = buffer.readLine();
command = commandLine.charAt(0);
if (command == '1')
insert();
else if (command == '2')
delete();
else if (command != '3')
System.out.println("Wrong command entered, please retry.");
printFile("Customers:",outfile);
}
outfile.close();
fIn.close();
}
catch (IOException ioe) {
}
}
private void printFile(String name, RandomAccessFile f) throws IOException {
byte ch = '1';
RandomAccessFile outf = new RandomAccessFile("hash.out","rw");
outf.seek(outf.length());
System.out.println(name);
outf.writeBytes(name + "\n");
f.seek(0);
while (true) {
for (int i = 1; i <= bucketSize; i++) {
for (int j = 1; j <= recordLength; j++) {
try {
ch = f.readByte();
}
catch (EOFException e) {
System.out.println();
outf.write('\n');
outf.close();
return;
}
System.out.print((char)ch);
outf.write(ch);
}
}
outf.write('\n');
}
}
static public void main(String args[]) {
String fileName = "";
InputStreamReader isr = new InputStreamReader(System.in);
BufferedReader buffer = new BufferedReader(isr);
HashingUnit8 hashClass = new HashingUnit8();
try {
if (args.length == 0) {
System.out.print("Enter a file name: ");
fileName = buffer.readLine();
}
else fileName = args[0];
}
catch(IOException io) {
System.err.println("Cannot open " + fileName);
}
hashClass.processFile(fileName);
}
}
|
|
|
|
|
If I where you I would do it in a simpler manner. see the code in http://www.javacoffeebreak.com/faq/faq0034.html[^]
It has what you need. To remove use hash.remove(key);
From what I saw in your code the first three int variables are the constraints of the code try changing the first int value that would allow for more input.
Good Luck
|
|
|
|
|
Thanks for your reply. Although I was not able to use the code from the web site you posted because we were not allowed to use the Java utility hash table, after doing some more research on the bucket size and such I actually did find some code that helped me get everything resolved. So you still helped me by pointing out where my problem was. Thanks a lot! 
|
|
|
|
|
Hi,
I'm a noob at this, how would I go about extracting data from a .sdf file, manipulating it then spit it out into a csv file using Java/Eclipse?
What drivers would I need? Is there any sample source code?
Your help is most appreciated.
Best regards,
Jason.
|
|
|
|
|
Use this JDBC connection to connect to your database:
http://jtds.sourceforge.net/index.html[^]
Then after your done with the sql statements a simple printwriter code can do the csv file for you.
Good luck
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
