Put brackets around filename for Excel formula

Question

My project is based on Apache POI.I'm trying to use a formula on a cell. My formula is as follows.

sheet7.createRow(0).createCell(0).setCellFormula("+'C:\\Users\\Desktop\\[Test.xlsx]Average_Graph'!A2");

Im using a JFileChooser, which allows users to select the file. Therefore the filepath will be changed every time the program is used.

From the JFileChooser, I'm getting a filepath as follows.

 String filepath= "C:\\Users\\Desktop\\Sheet.xlsx"`

In order to work the formula correctly, the filepath should be in following format.

"C:\\Users\\Desktop[Sheet.xlsx]"

How Can I Change the string which I'm getting from the JFileCHooser to run the formula correctly?

In your java program there is a `String` with wrong escapes. Is that actually in the source code like this? — Federico klez Culloca, Aug 24 '18 at 07:39
What do you mean "as follows"? You want to **insert** the brackets? Or only get the filename? — OneCricketeer, Aug 24 '18 at 07:41
Also, is the result correct, without a `"\\"` before the `"["`? — Federico klez Culloca, Aug 24 '18 at 07:41
@LSH94 then try correct the example you have posted and also try to include so more code. It is not really clear what you want to achieve. — akortex, Aug 24 '18 at 07:41
@cricket_007 I need to add [ ] for the file name and add another \ for the file path. — LSH94, Aug 24 '18 at 07:43
@LSH94 so you want the `String` to be changed after having got it by `JFileChooser`? Do you want those brackets inserted or do you want the file name only? — deHaar, Aug 24 '18 at 07:43
@LSH94 please share your code. Otherwise having people try to speculate on what you're attempting to do and providing information in the way of comments is not enough. — akortex, Aug 24 '18 at 07:44
`JFileChoser` gives you a `File` object, not a String, AFAIK — OneCricketeer, Aug 24 '18 at 07:45

OneCricketeer · Answer 1 · 2018-08-24T07:55:26.757

5

If you have a JFileChooser, you have a File object, not just a String

And you should use the File or Paths API anyway to be OS independent

File f = jFileChooser.getSelectedFile();
String path = f.getParent();
String name = f.getName();

String newFormat = String.format("%s[%s]", path, name);

edited Aug 24 '18 at 07:55

answered Aug 24 '18 at 07:49

OneCricketeer

179,855
19
132
245

1

Given that the OP really does want to achieve the pattern they described, this should be the accepted answer. – akortex Aug 24 '18 at 07:49
This is clear and concise, and what I think is being requested for. – Timothy T. Aug 24 '18 at 07:52
@cricket_007 The code you gave about did the work. But for another part, I need to change it to get the output as C:\\Users\\Desktop\\[Sheet.xlsx] Please help. – LSH94 Aug 25 '18 at 15:17
What difficulties are you having adding the last slash? Java isn't storing that internally, it only displays it as an escape character, so I don't understand the problem. More specifically, don't you think it's simply `"%s\\[%s]"` as the format? – OneCricketeer Aug 25 '18 at 16:22

score 3 · Answer 2 · answered Aug 24 '18 at 07:50

As I understand it you get the path as a String from a file chooser. So in your example you can do:

   String filepath= "C:\\Users\\Desktop\\Sheet.xlsx"
   Path path = Paths.get(filepath);
   String result=path.getParent()+"["+path.getFileName()+"]";

And result will be your new string containing the file surrounded by braces

Or another solution would be using a File

  File f=new File(filepath);
  String result=f.getParent()+"["+f.getName()+"]";

Donatic · Answer 3 · 2018-08-24T08:06:44.087

1

Just split parent directonary name and file name and putting the chars "[" and "]" around the file name:

    File select = fileC.getSelectedFile();
    String filepath = select.getParentFile().getAbsolutePath()+"["+select.getName()+"]";

*EDIT i understood the question wrong so i thought about the false problem;

edited Aug 24 '18 at 08:06

answered Aug 24 '18 at 07:43

Donatic

323
1
13

Why would he need 2 file separators? He probably has two in the initial string for escape reasons – Veselin Davidov Aug 24 '18 at 07:44
1

Please read the comments, there he says "i get the string from JFileChooser. ". Thus he'd need a more flexible solution. – Thomas Aug 24 '18 at 07:44

Put brackets around filename for Excel formula

3 Answers3

Linked