https://bossanova.uk/jexcel/v3/
https://stackoverflow.com/questions/5794659/poi-how-do-i-set-cell-value-to-date-and-apply-default-excel-date-format
https://poi.apache.org/apidocs/dev/org/apache/poi/ss/usermodel/BuiltinFormats.html
https://www.codota.com/code/java/classes/org.apache.poi.ss.usermodel.BuiltinFormats
https://stackoverflow.com/questions/9431089/how-to-correctly-format-a-date-cell-and-populate-content-using-apache-poi-3-7
http://thinktibits.blogspot.com/2012/12/XLSX-Format-Date-Java-POI-Example.html
VLOOKUP
VALUE
Excel function
What are our possible options for dealing with the date formatting issue using POI?
- Use the VLOOKUP or some other Excel function for formatting the displayed value.
- Create a separate test Java class to see if we can produce an Excel file with proper date formatting
- Create a template file that has proper date formatting and use it.
- See which one we are using (HSSF, XSSF, SXSSF) and switch to the ones that we are not using to see if that resolve our issue.
- Set the formatListener using https://stackoverflow.com/questions/10230204/apache-poi-localized-date-into-excel-cell
- Switch to another library (not sure if it will bring any other problems)
What are some Excel shortcuts?
- CTRL + Right arrow: move to the next empty or non-empty cell
- CTRL + Left arrow: move to the next empty or non-empty cell
- CTRL + Down arrow
- CTRL + Up arrow
- CTRL + Home: move to the first cell (A1) in the worksheet
- CTRL + END: move to the last cell in the worksheet
- CTRL + F: Display the Find dialog
- CTRL + H: Display the Replace dialog
- Control + : Enter the current date
- Home: move to the beginning of a row
- Shift + SPACE: select the entire row
- CTRL + SPACE: select the entire column
- Control + Shift + : Enter the current time. With either shortcut, excel will enter the current date or time using a valid Excel date in serial number format, with dates as integers and times as decimal values. You can then apply date or time formatting as you like.
- Control + PgDn / Control + PgUp: Go to previous or next worksheet.
- Control + F1: Expand or collapse the top ribbon
How can we control the size (height and width) of particular column, row, or cell?
To control the width of a column, right click on the column header and select "Column Width". To cause the text to wrap, click on the appropriate cell and click on the "Wrap Text" button (in the Alignment section, at the top of the screen).
How can we quickly fill selected cells with the same value or formula?
- Select the cells that we want
- Enter the data (just type the data). The data will be entered into the first cell selected.
- Hit CTRL+ENTER.
How can we replace formula cells with its result?
When you replace formulas with their values, Microsoft Office Excel permanently removes the formulas. If you accidentally replace a formula with a value and want to restore the formula, click Undo Button image immediately after you enter or paste the value.
- Select the cell or range of cells that contains the formulas. If the formula is an array formula, select the range that contains the array formula:
- Click a cell in the array formula.
- On the Home tab, in the Editing group, click Find & Select, and then click Go To.
- Click Special.
- Click Current array.
- Click Copy
- Click Paste
- Click the arrow next to Paste Options Button image, and then click Values Only.
You can convert the formula in its cell to its value by doing the following:
- Press F2 to edit the cell.
- Press F9, and then press ENTER.
When you are editing a cell that contains a formula, you can press F9 to permanently replace the formula with its calculated value.
You can replace a part of a formula with its value:
- Click the cell that contains the formula.
- In the formula bar Button image, select the portion of the formula that you want to replace with its calculated value. When you select the part of the formula that you want to replace, make sure that you include the entire operand. For example, if you select a function, you must select the entire function name, the opening parenthesis, the arguments, and the closing parenthesis.
- To calculate the selected portion, press F9.
- To replace the selected portion of the formula with its calculated value, press ENTER. If the formula is an array formula, press CTRL+SHIFT+ENTER.
How can we enter a formula?
Click on the cell that you want to contain the formula and enter the formula:
=SUM(C5:F5)
=AVERAGE(E1:E11)
How can we select cells?
Just click on on cell and drag your mouse and release the mouse.
How can we select multiple cells that are not next to each other?
Hold down the SHIFT or CTRL key, select the cells as normal, and release the shift key after all desired cells are selected.
How can we enter a line-break into a cell?
ALT+ENTER
How can we lock a header so that it stay in place as the user scroll?
- Click on the first cell that is part of the data (not the header).
- Click on Windows -> Freeze Panes
What exactly happens when we use Windows -> Freeze Panes?
When we click on a cell and click on Windows -> Freeze Panes, everything to the left and above the current cell is freezed.
How can we lock a column so that it stay in place as the user scroll left or right?
- Click on the first cell that is part of the data (not the header).
- Click on Windows -> Freeze Panes
If we have 3 columns containing numbers and we want to calculate the sum for each column, what do we do?
Select the cells that should contain the sum, and click on the sigma / sum icon on the toolbar. See http://www.youtube.com/watch?v=8L1OVkw2ZQ8
How can we merge cells?
Select the cells, and then click on the Merge cells icon on the toolbar.
How can we center the text within a cell?
Just click on the cell and click on appropriate icon in the toolbar at the top.
How can we set background color for a cell?
Just click on the cell and click on appropriate icon in the toolbar at the top.
How can we apply a printing grid?
By default, Excel shows the border between cells. However, when we print, the border for the cells are not printed automatically. To print the border for the cells, select the cells, and click on border icon in the toolbar at the top and select the grid option.
How can we create a chart?
Select the cells (not including the total row and column), and click on the chart icon on the toolbar, select the appropriate chart type, and click Finish. See http://www.youtube.com/watch?v=8L1OVkw2ZQ8
How can we format your cells as currency?
- Select the cells
- Click on the Format -> Number -> Currency. Select appropriate currency, and change appropriate options as you want and click on OK
If you enter a formula in one cell, how can we quickly apply the same formula to the other cell within the same column?
Double click on the cell that contains the formula. Excel will automatically copy the formula to the other cells within the same column until it find a blank row. See the last 2 minutes of http://www.youtube.com/watch?v=RgvdCHjOKYg
How can we add a column?
Right click on an existing column header and select Insert
How can we add a row?
Right click on an existing row header and select Insert
How can we shift cells below the current cell down?
Right click on the current cell, and select Insert -> Shift cells down
How can we delete a row or column?
Right click on appropriate cell, and select Delete
How can we resize a column?
Probably just click on the column handles and drag them appropriately
How can we cause Excel to resize the column so that all the values within the column is visible?
Double click on the column handle.
How can we add comment to a cell?
Right click on the cell and select Insert Comment. Enter the comment and hit enter. The comment is not displayed. However, the cell now have a red indicator. To see the comment, just move your mouse over the cell.
How can we make a comment permanently visible?
You can click on the red indicator in the cell or you can right click on the cell and select Show / Hide Comments
Can we resize the comment?
Yes. Once the comment is visible, just click one of its edges and drag it as appropriate.
Can we change the position of a comment?
Yes. Once the comment is visible, just click on the comment and drag it to wherever appropriate.
How can we edit a comment?
Right click on the cell, and select Edit Comment
How can we add a new sheet?
An Excel workbook can contain multiple sheets. Sheets are like tabs. The sheet names are displayed at the bottom. To add a new sheet, right click the name of an existing sheet, and select Add
How can we delete a sheet?
Probably by right clicking on the sheet and select Delete
How can we rename a sheet?
Probably, by right clicking on the name of the sheet and select Rename
How can we hide a sheet?
Right click on the name of the sheet and select Hide
How can we unhide a sheet?
Right click on any sheet and select Unhide. Excel will display a small window where you can select which sheet to unhide.
How can we move a sheet?
Just click on the name of the sheet and drag it.
Can we move a sheet from a workbook to another workbook?
Yes. Right click on the name of the sheet and select Move. Excel will display a window with two field. The first field display all the currently open workbooks. The second field display the sheets within the selected workbook.
How can we copy a sheet from a workbook to another workbook?
Right click on the name of the sheet and select Move or copy. Excel will display a window just as above, but to copy the sheet to a new sheet, look for a checkbox.
How can we change the color of the sheet's tab?
Right click on the sheet's name and select Tab color
How can we apply a pattern to a column?
Given that your column contains 3 existing numerical cells and their values follow a certain patterns such as 2, 4, 6. If you want to apply this pattern to the other cells so that you do not have to manually enter the values, select the existing cells and drag the bottom edge of the third cell down. Excel will automatically analyze the pattern and apply it to the other selected cells. Excel can do this for numbers, days of week, months of year. This works with the short names for months as well.
How can we specify a range in your formula?
Use the colon to separate the starting cell and the ending cell:
=sum(A1:E1) // Calculate the total between A1 and E1 horizontally
=sum(A1:A9) // Calculate the total between A1 and A9 vertically
If we have a formula entered in one cell, how can we avoid having to manually enter the formula again for another cell?
Just drag the bottom edge of the cell that contain the formula down or across. See the last 4 minutes of http://www.youtube.com/watch?v=L7dHA_8GzKw
How can we determine the address of the current cell?
Click on the cell and look at the top left of the screen.
How can we delete the formatting of a cell without deleting its content?
- Click on the cell
- Click on Edit -> Clear -> Formats
What does Windows -> Split do?
It split the screen horizontally so that the top half can be scrolled independently from the bottom half. This could be handy if the top half and the bottom half contains different data that somehow need to be shown together, or in special cases, where you have a long table and want to see the bottom of the table as you scroll up and down. This feature does not work well when Freeze Panes is being used.
With Windows -> Split, can we resize the top half and the bottom half?
Yes. When we use Windows -> Split, a horizontal bar is created to separate the top half and bottom half. We can drag this bar and position it to resize the top and bottom half.
Can we split the screen vertically?
Yes. Just use Windows -> Split twice.
How can we undo split screen?
Just drag the bar to the far edges of the screen (either vertically or horizontally as appropriate). We can also use Windows -> Remove Splits
Can we have four-way split?
Yes and no. Depending on the current cell, the panes scroll together. See the last quarter of http://www.youtube.com/watch?v=6uWTF1jLv9U
How can we hide or unhide a column?
When you want to unhide a sheet, you just right click on any sheet, select Hide, or Unhide and Excel will display a small window where you can choose which sheet to hide or unhide. When it comes to un-hiding a column, it may not be obvious for novice. You can select a range of columns (including the columns that you want to unhide), and the right click on the column header (the letters at the top) and choose Unhide. It is a bit tricky to unhide the first column because it is not possible for you to select the first column when it is hidden. To unhide the first column, you must use the Name Box which Excel uses to display the address of the cell. Click on the Name Box, type A1, hit enter, and click on the Format button in the top bar and select Hide & Unhide -> Unhide Columns. To hide or unhide a row, follow similar procedure.
How can we print row or column headers on all pages?
