Tag Archives: Excel

  1. Pages:
  2. 1
  3. 2
  4. 3

Batch Excel To CSV Converter Application

Posted on by
1

I have been truly fascinated by the amount of visitors that I get because of the simple XLS/XLSX to CSV converter script that I made a while back (original post). For that reason I have created an HTML application (HTA) which offers the same functionality and then some:
XLS To CSV Converter
DOWNLOAD THIS APP

How this Differs from the Script

  • There is an easy to use interface.
  • You can customize how the resulting files are named.
  • You can filter which sheets will be exported as CSVs using simple text matches and/or regular expressions.
  • You will see the results of the conversion process in one place after the process is finished.

Using It

  • Download it from here.
  • Unzip the .HTA file.
  • Double-click on the .HTA file.
  • Click the Browse for Folder button and browse for the folder that contains the XLS and/or XLSX files whose worksheets should be exported as CSVs.
  • Modify the CSV Naming Schema as you like (hints are given in the application).
  • If you would like to only export worksheets with specific names, enter those names in the Sheet Filters textbox (only one per line). You may also use JavaScript-style regular expressions (one on each line) to match the sheets that you want to convert.
  • If you would like to remove the linefeed characters from all cells, check the appropriate checkbox.
  • Click the Start Conversion button and wait for the results to appear.

Plans for Improvement

As of right now this application is very simple, but I do hope to beef it up a little as time goes on. Let me know if you have any comments, questions, suggestions, or issues with this new application. Most of all, have fun!!! 8)

VBA – Encode URL Query Parts In Excel

Posted on by
Reply

Have you ever had a column text for which you wish you could easily create links? For example, let’s say that you have a column of search words (or phrases) that should link to the Google page for each search. How would you do it? First of all, you would probably want to be able to build the URL in a separate column. Unfortunately, Excel doesn’t provide a way of encode URL parameters. Therefore, you can use the following as your own UDF:

Public Function encodeURL(ByVal queryPart As String)
    Dim c As String
    While Len(queryPart) > 0
        c = Left(queryPart, 1)
        queryPart = Mid(queryPart, 2, Len(queryPart) - 1)
        If c Like "[A-Za-z0-9._~-]" Then
            encodeURL = encodeURL & c
        ElseIf c = " " Then
            encodeURL = encodeURL & "+"
        Else
            encodeURL = encodeURL & "%" & Right("0" & Hex(Asc(c)), 2)
        End If
    Wend
End Function

That function will take in one string and output the equivalent as an encoded URL query part. That function should be the equivalent of PHP’s urlencode() function. Tomorrow, we will see how this function can actually help you to be able to create links for an entire column.

From Office 2003 To Office 2007 or Office 2010

Posted on by
Reply

Did you know about the interactive guides provided by Microsoft to help Office 2003 users to migrate to the corresponding 2007 or 2010 software? If you are trying to get use to Office 2010, you can use the interactive guides on this page. If you are trying to get use to Office 2007, you can use the interactive guides listed below (or go here to download them):

I realize that this is a very late post, but I figured it is still worth mentioning for all of those who remember how to do something in the old version of Excel, Word, etc. but not in the newer version.  Click here for all of the information from an official Microsoft site.

XLS Colors for JasperReports

Posted on by
Reply

One of the things that I often end up Googling is a list of the 56 default XLS colors. I finally decided to compile my own list so that I can simply copy and paste the value that I need to use for either the Forecolor or Backcolor in the properties pane:

Name Jasper Value Hex Code R G B
Black [0,0,0] #000000 0 0 0
White [255,255,255] #FFFFFF 255 255 255
Red [255,0,0] #FF0000 255 0 0
Green [0,255,0] #00FF00 0 255 0
Blue [0,0,255] #0000FF 0 0 255
Yellow [255,255,0] #FFFF00 255 255 0
Magenta [255,0,255] #FF00FF 255 0 255
Cyan [0,255,255] #00FFFF 0 255 255
[Color 9] [128,0,0] #800000 128 0 0
[Color 10] [0,128,0] #008000 0 128 0
[Color 11] [0,0,128] #000080 0 0 128
[Color 12] [128,128,0] #808000 128 128 0
[Color 13] [128,0,128] #800080 128 0 128
[Color 14] [0,128,128] #008080 0 128 128
[Color 15] [192,192,192] #C0C0C0 192 192 192
[Color 16] [128,128,128] #808080 128 128 128
[Color 17] [153,153,255] #9999FF 153 153 255
[Color 18] [153,51,102] #993366 153 51 102
[Color 19] [255,255,204] #FFFFCC 255 255 204
[Color 20] [204,255,255] #CCFFFF 204 255 255
[Color 21] [102,0,102] #660066 102 0 102
[Color 22] [255,128,128] #FF8080 255 128 128
[Color 23] [0,102,204] #0066CC 0 102 204
[Color 24] [204,204,255] #CCCCFF 204 204 255
[Color 25] [0,0,128] #000080 0 0 128
[Color 26] [255,0,255] #FF00FF 255 0 255
[Color 27] [255,255,0] #FFFF00 255 255 0
[Color 28] [0,255,255] #00FFFF 0 255 255
[Color 29] [128,0,128] #800080 128 0 128
[Color 30] [128,0,0] #800000 128 0 0
[Color 31] [0,128,128] #008080 0 128 128
[Color 32] [0,0,255] #0000FF 0 0 255
[Color 33] [0,204,255] #00CCFF 0 204 255
[Color 34] [204,255,255] #CCFFFF 204 255 255
[Color 35] [204,255,204] #CCFFCC 204 255 204
[Color 36] [255,255,153] #FFFF99 255 255 153
[Color 37] [153,204,255] #99CCFF 153 204 255
[Color 38] [255,153,204] #FF99CC 255 153 204
[Color 39] [204,153,255] #CC99FF 204 153 255
[Color 40] [255,204,153] #FFCC99 255 204 153
[Color 41] [51,102,255] #3366FF 51 102 255
[Color 42] [51,204,204] #33CCCC 51 204 204
[Color 43] [153,204,0] #99CC00 153 204 0
[Color 44] [255,204,0] #FFCC00 255 204 0
[Color 45] [255,153,0] #FF9900 255 153 0
[Color 46] [255,102,0] #FF6600 255 102 0
[Color 47] [102,102,153] #666699 102 102 153
[Color 48] [150,150,150] #969696 150 150 150
[Color 49] [0,51,102] #003366 0 51 102
[Color 50] [51,153,102] #339966 51 153 102
[Color 51] [0,51,0] #003300 0 51 0
[Color 52] [51,51,0] #333300 51 51 0
[Color 53] [153,51,0] #993300 153 51 0
[Color 54] [153,51,102] #993366 153 51 102
[Color 55] [51,51,153] #333399 51 51 153
[Color 56] [51,51,51] #333333 51 51 51

JasperReport Excel Options

Posted on by
3

Over the past year I have been learning about so many different OpenSource packages it isn’t even funny.  One thing that is available for free, but also has a paid version (which we at using at UniTek Global Services), is JasperServer.  Right now we are on version 4.2.1.  When we first rolled it out, everything seemed to work excellently with the exception of three things:

  1. In the report template, I placed the company logo to the left of two rows:  the report title and the description/generation date.  Whenever I generated the report as an XLS via Jaspersoft iReport Designer, the image would load correctly.  On the other hand, whenever I generated the report as an XLS via the JasperServer, the space for the image was left there without an image.
  2. Once the image issue was resolved (I will describe how soon), the XLS generated via the JasperServer would load the image on the same row as the title but it wouldn’t extend into the cell directly below so that it would appear to the left of the description as well.
  3. XLS reports generated via the JasperServer were not showing borders on any of the cells no matter how large I made the borders in the designer.

If you encounter these issues in your JasperReport template, do the following:

  1. In the Report Inspector pane, click on the top-most element (which is the template).
  2. In the Properties pan, select the Properties row and then select the elipsis at the right of the row.
  3. Show Images In XLS:
    To ensure that images will appear when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.ignore.graphics
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  4. Allow Cells To Span Multiple Rows In XLS:
    To ensure that cells can span multiple rows when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.collapse.row.span
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  5. Show Borders In XLS:
    To ensure that images will appear when exported as XLS, do the following:
    1. Click the Add button in the Properties window.
    2. Enter the following as the Property name:
      net.sf.jasperreports.export.xls.ignore.cell.border
    3. Enter the following as the Property value:
      false
    4. Click OK to close the Add/modify property window.
  1. Pages:
  2. 1
  3. 2
  4. 3