Creating, writing to, and reading from a new file type

Question

Hello I have a WinForm app that generates a ton of data. Currently I have this savable and open-able in an Excel .xls file format. However I'd like to move away from this so that the user can't open the file up in Excel and modify the contents freely.

The solution I've come up with is encoding this as a new file type, example as an ".por" file. However I also don't want this to be a simple rename of the extension so that a user could rename back to .xls and being editing. How can I do this?

Furthermore I'm programmitically taking a DataTable and row by row writing this out to the Excel file. Would it help to save the DataTable contents using a different method (not SQL)?

Have you seen any articles on generating Excel files with Linq to XML, with an unknown extension (&/or mime type) that would help to camouflage the files. — Jeremy Thompson, Jun 23 '12 at 01:02
Side note: If you want user not to open your files - don't create them (half kidding). If you use commonly accessible file format (i.e. Excel's, some XML, CSV...) you have 2 benefits - users are assured that data will not disappear because you desided to drop old file format and you can easily confirm that data is reasonable by using another tool for the same file format. While custom format is fun idea to start with it also come with custom versioning, migration, transformation to other formats. Depending on if you care about users it may cost you too much efforts... — Alexei Levenkov, Jun 23 '12 at 04:58
I know you said "not SQL", but have you looked at using SQL CE. It a light version of SQL that does not have to be installed, and it works with all the built in APIs. Here is a great post covering a basic [how to](http://weblogs.asp.net/scottgu/archive/2011/01/11/vs-2010-sp1-and-sql-ce.aspx) by ScottGu. — Zachary, Jun 23 '12 at 05:05

score 6 · Accepted Answer · answered Jun 23 '12 at 00:14

6

A simple way to do this would be to put your DataTable into a DataSet, call the DataSet's WriteXml(...) method (which will save your data in XML format to a file) and then encrypt the file using File.Encrypt.

To read from a file, you reverse the process, using File.Decrypt to turn the file back into a plain-XML file, and then load it into a DataSet using the DataSet's ReadXml(...) method.

answered Jun 23 '12 at 00:14

MusiGenesis

74,184
40
190
334

Well, you use a hammer to drive nails, a wrench to turn bolts etc. – MusiGenesis Jun 23 '12 at 00:48
but then I saw your website:( – Jeremy Thompson Jun 23 '12 at 12:18
1

@JeremyThompson: aw, c'mon, you're not supposed to look at that crap. I did that in Notepad, for pete's sake. – MusiGenesis Jun 23 '12 at 13:09

score 0 · Answer 2 · answered Jun 22 '12 at 23:53

0

Just serialize your object and write it to disk. Use binary if you want to make it harder for your user to just open in a text editor or something.

http://msdn.microsoft.com/en-us/library/ms233843.aspx

answered Jun 22 '12 at 23:53

nickgroenke

1,472
9
12

score -1 · Answer 3 · edited May 23 '17 at 12:27

Better yet, make a MIME Type so that when users double click ".por" files it triggers an exe to tell users what to do. Rather than open it in Excel. Users wont know that por files are Excel files if you dont tell them.

Since xls's are binary files - if the extension of the files is changed users won't know the MIME type unless they used something like this: Determining MIME Type of MAC upload stream file

Regarding taking a DataTable and row by row writing this out to the Excel file.

You could easily take the data into a 2d array:

string[,] TwoDimensional = new string[dt.Rows.Count, dt.Columns.Count];
for (int i = 0; i < dt.Rows.Count; i++)
{
    for (int j = 0; j < dt.Columns.Count; i++)
    {
        TwoDimensional[i, j] = dt.Rows[i][j].ToString();
    }
}

And in one swoop set them in Excel:

using (var targetRangeHeader = _excelApp.Range["A1"].WithComCleanup())
using (var targetRangeFirstDataCell = targetRangeHeader.Resource.Offset[1, 0].WithComCleanup())
using (var targetRange = targetRangeFirstDataCell.Resource.Resize[LengthOfArray, 1].WithComCleanup())
{
....
targetRange.Resource.Value2 = TwoDimensional;

Note I'm using VSTO Contrib for the finalistic determination - ie the using statements.

Dont forget this (record the current calculation mode and restore it rather than assuming the user is using automatic):

   public static void TurnOffApplicationSettings(Excel.Application xlApp)
        {
            xlApp.ScreenUpdating = false;
            xlApp.DisplayAlerts = false;
            xlApp.Calculation = XlCalculation.xlCalculationManual;
            xlApp.UserControl = false;
            xlApp.EnableEvents = false;
        }

        public static void TurnOnApplicationSettings(Excel.Application xlApp)
        {
            xlApp.ScreenUpdating = true;
            xlApp.DisplayAlerts = true;
            xlApp.Calculation = XlCalculation.xlCalculationAutomatic;
            xlApp.UserControl = true;
            xlApp.EnableEvents = true;
        }

Creating, writing to, and reading from a new file type

3 Answers3