0

This is a correction of my previous question Put brackets around filename for Excel formula

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 previous question, I mistakenly typed C:\Users\Desktop[Sheet.xlsx] instead of C:\Users\Desktop\[Sheet.xlsx] The answers gave me the output which i've mentioned. But I need the Output as C:\Users\Desktop\[Sheet.xlsx]

Please help.

MadProgrammer
  • 343,457
  • 22
  • 230
  • 366
LSH94
  • 109
  • 1
  • 9

2 Answers2

1

If you want to solve this by directly altering the file path, you may use String#replaceAll:

String filepath = "C:\\Users\\Desktop\\Sheet.xlsx";
filepath = filepath.replaceAll("(?<=\\\\)([^\\\\]+)$", "[$1]");
System.out.println(filepath);

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

Demo

Tim Biegeleisen
  • 502,043
  • 27
  • 286
  • 360
  • That Worked. Thanks – LSH94 Aug 26 '18 at 02:39
  • How to get "\\" instead of "\" as the output? – LSH94 Aug 28 '18 at 04:25
  • Why do you think you need this? – Tim Biegeleisen Aug 28 '18 at 04:33
  • I'm Using Apache POI for a project based on excel file. In that, I need to use a formula with a file path. It only runs if the file path is something like C:\\Users\\Desktop\\[Sheet.xlsx]. Not working with only one backslash. Please help. – LSH94 Aug 28 '18 at 04:36
  • You should open a new question. This is different from what you asked, and would invalidate the two answers given here already. – Tim Biegeleisen Aug 28 '18 at 04:38
  • Posted a new question. If you know the answer, Please help. https://stackoverflow.com/questions/52050242/change-a-string-put-brackets-around-file-name-and-get-two-backslashes – LSH94 Aug 28 '18 at 04:48
1

File names won't have \backslashes in them, so we can assume that our filename begins after the last backslash and ends at the end of the string.

We can use this:

String filepath = "C:\\Users\\Desktop\\Sheet.xlsx";
String dir = filepath.substring(0, filepath.lastIndexOf("\\"+1));
String filename = filepath.substring(filepath.lastIndexOf("\\"+1));

filepath = dir + "[" + filename + "]";

Or a shorter version:

String filepath = "C:\\Users\\Desktop\\Sheet.xlsx";
filepath =  filepath.substring(0, filepath.lastIndexOf("\\"+1)) +
            "[" + filepath.substring(filepath.lastIndexOf("\\"+1)) + "]";
Phil
  • 96
  • 1
  • 9