On the other hand, using Excel automation to populate a file takes an eternity and doesn't work well on a server. So here's a nice middleground - I can save the file to an XML/HTML format that Excel supports natively.
This strategy solves a few problems:
1) Performance - This format is just as fast to create as CSV.
2) Dependence - Does not require using any Excel libraries.
3) Compatibility - Excel opens the file and it looks properly formatted and usable.
4) Data Integrity - CSV files tend to damage numeric or date values if they are formatted in a way Excel does not expect; this format converts everything to text which means the values are represented exactly.
You also have the option of using some of Microsoft Excel's number formatting tools. These are surprisingly hard to find documented anywhere! But, here they are:
http://www.ozgrid.com/Excel/CustomFormats.htm
Without further ado, here's the code:
/// <summary>
/// Save a DataTable to an XML file
/// </summary>
/// <param name="filename"></param>
/// <param name="dt"></param>
public static void SaveDataTableToExcelCompatibleFile(string filename, DataTable dt)
{
StreamWriter sw = null;
try {
// Create the file and write the header
sw = new StreamWriter(filename);
sw.WriteLine(@"<head><META http-equiv=""Content-Type"" content=""text/html; charset=UTF-8""></head><table>");
// Write column names
sw.Write("<tr>");
for (int i = 0; i < dt.Columns.Count; i++) {
sw.Write("<th filter=\"all\">");
sw.Write(dt.Columns[ i ].ColumnName);
sw.Write("</th>");
}
sw.Write("</tr>");
// Write data
for (int i = 0; i < dt.Rows.Count; i++) {
DataRow dr = dt.Rows[ i ];
sw.Write("<tr>");
for (int j = 0; j < dt.Columns.Count; j++) {
sw.Write("<td style=\"vnd.ms-excel.numberformat:@\">");
sw.Write(dr[j].ToString());
sw.Write("</td>");
}
sw.Write("</tr>");
}
// Write footer
sw.WriteLine(@"</table><br>
<br>Confidential Information - Do Not Distribute");
} finally {
if (sw != null) sw.Close();
}
}
| permalink | related link |
( 3 / 201 )Has anyone noticed Firefox slowing down? Turns out a whole bunch of applications install firefox plugins without telling you. Here’s how to disable them.
1) From the main menu, select Tools | Add-Ons
2) Click the “Extensions” tab. Disable “Microsoft.NET Framework Assistant”.
3) Click the “Plugins” tab. Disable everything. None of them are needed.
4) Restart firefox. Your browser will be fast again!
I'm surprised a little to see a "Microsoft Office" plugin for Firefox. I wouldn't be surprised however to discover if that plugin slowed Firefox down a fair amount.
| permalink | related link |
( 3 / 189 )Since I've been in the web industry for so long, I can remember when enabling web site compression was complex and fraught with caching challenges. Apparently, compression is a performance win in pretty much every circumstance nowadays.
I created this script and called it "enable-compression.cmd":
cd c:\inetpub\adminscripts
cscript adsutil.vbs set w3svc/filters/compression/parameters/HcDoDynamicCompression true
cscript adsutil.vbs set w3svc/filters/compression/parameters/HcDoStaticCompression true
cscript adsutil.vbs set w3svc/Filters/Compression/GZIP/HcFileExtensions "txt" "css" "html" "htm" "js"
cscript adsutil.vbs set w3svc/Filters/Compression/GZIP/HcScriptFileExtensions "aspx" "asp"
iisreset /restart
Just in case any problems happened, I created "disable-compression.cmd":
cd c:\inetpub\adminscripts
cscript adsutil.vbs set w3svc/filters/compression/parameters/HcDoDynamicCompression false
cscript adsutil.vbs set w3svc/filters/compression/parameters/HcDoStaticCompression false
iisreset /RESTART
After you run these scripts, you restart IIS and watch as the performance benefits roll in.
To read more, Microsoft's Compression Article for IIS
| permalink | related link |
( 2.9 / 261 )Hi everyone,
One of my most heavily used bits of code is my CSVFile logic. I've updated this to handle a few unusual cases I've seen in the recent few months.
The code now allows you to provide the delimiter and text qualifier directly. Usually, the delimiter is the comma and the qualifier is the double-quote; but some files use different characters.
public static string[] ParseLine(string s, char delimiter, char text_qualifier)
{
List<string> list = new List<string>();
StringBuilder work = new StringBuilder();
for (int i = 0; i < s.Length; i++) {
char c = s[ i ];
// If we are starting a new field, is this field text qualified?
if ((c == text_qualifier) && (work.Length == 0)) {
int p2;
while (true) {
p2 = s.IndexOf(text_qualifier, i + 1);
// for some reason, this text qualifier is broken
if (p2 < 0) {
work.Append(s.Substring(i + 1));
i = s.Length;
break;
}
// Append this qualified string
work.Append(s.Substring(i + 1, p2 - i - 1));
i = p2;
// If this is a double quote, keep going!
if (((p2 + 1) < s.Length) && (s[p2 + 1] == text_qualifier)) {
work.Append(text_qualifier);
i++;
// otherwise, this is a single qualifier, we're done
} else {
break;
}
}
// Does this start a new field?
} else if (c == delimiter) {
list.Add(work.ToString());
work.Length = 0;
} else {
work.Append(c);
}
}
list.Add(work.ToString());
return list.ToArray();
}
| permalink | related link |
( 3 / 246 )Found this link which is amazingly useful for creating nifty tools.
http://www.rootsilver.com/2007/08/how-t ... solewindow
| permalink | related link |
( 2.9 / 266 )Back Next
Calendar
