DataSet导出到Excel比较完整的解决方案(一)--客户端生成文件(downmoon)
来源:互联网 发布:程序员自嘲段子 编辑:程序博客网 时间:2024/04/28 23:55
有一客户需求:
1、要从SQL Server数据库导出并生成Excel ;
2、用户下载对应的Excel并填写上传再导入到SQL server。
费了将近六个小时,故一定要把过程写下来,希望看到此文的朋友少走些不必要的弯路。
首先,想到的是直接导出到客户端,代码如下:
- public static void DataSetToExcel(DataSet oDS, HttpResponse Response, string fileName)
- {
- if (oDS == null || oDS.Tables[0] == null || oDS.Tables[0].Rows.Count == 0) { return; }
- Response.Clear();
- //Encoding pageEncode = Encoding.GetEncoding(PageEncode);
- HttpContext.Current.Response.Charset = "gb2312";
- //Response.ContentType = "application/vnd-excel";//"application/vnd.ms-excel";
- //Response.ContentType = "application/x-octet-stream";//"application/vnd.ms-excel";
- Response.ContentType = "text/csv";//"application/vnd.ms-excel";
- Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".cvs");
- System.IO.StringWriter oSW = new System.IO.StringWriter();
- HtmlTextWriter oHW = new HtmlTextWriter(oSW);
- DataGrid dg = new DataGrid();
- dg.DataSource = oDS.Tables[0];
- dg.DataBind();
- dg.RenderControl(oHW);
- Response.Write(oSW.ToString());
- Response.Flush();
- Response.Close();
- }
这样生成是生成了! 客户也可以用Excel直接打开并编辑,问题来了! 上传时出错,仔细看看生成的Excel.xls,
用记事本打开,内容大致如下:
- <table cellspacing="0" rules="all" border="1" style="border-collapse:collapse;" mce_style="border-collapse:collapse;">
- <tr>
- <td>品名</td><td>最高价格</td><td>最低价格</td><td>平均价格</td><td>计量单位</td><td>备注</td>
- </tr><tr>
- <td>青菜</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>南瓜</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>瓠子</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>冬春笋</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>雪里蕻</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>樱桃萝卜</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>佛手瓜</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>白菜鼎</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>蒜</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>食用菌</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr><tr>
- <td>黄瓜</td><td> </td><td> </td><td> </td><td>元/公斤</td><td> </td>
- </tr>
- </table>
原来,就是纯粹的html格式,披了件Excel的外衣。这样用户传上来的文件当然不是标准的Excel格式了!
于是, 想到直接生成xml格式的Excel文档,方法如下 :
- /// <summary>
- /// 执行导出
- /// </summary>
- /// <param name="ds">要导出的DataSet</param>
- /// <param name="strExcelFileName">要导出的文件名</param>
- public static void ExportToExcel(DataSet source, string fileName)
- {
- System.IO.StreamWriter excelDoc;
- excelDoc = new System.IO.StreamWriter(fileName);
- const string startExcelXML = "<xml version>/r/n<Workbook " +
- "xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"/r/n" +
- " xmlns:o=/"urn:schemas-microsoft-com:office:office/"/r/n " +
- "xmlns:x=/"urn:schemas- microsoft-com:office:" +
- "excel/"/r/n xmlns:ss=/"urn:schemas-microsoft-com:" +
- "office:spreadsheet/">/r/n <Styles>/r/n " +
- "<Style ss:ID=/"Default/" ss:Name=/"Normal/">/r/n " +
- "<Alignment ss:Vertical=/"Bottom/"/>/r/n <Borders/>" +
- "/r/n <Font/>/r/n <Interior/>/r/n <NumberFormat/>" +
- "/r/n <Protection/>/r/n </Style>/r/n " +
- "<Style ss:ID=/"BoldColumn/">/r/n <Font " +
- "x:Family=/"Swiss/" ss:Bold=/"1/"/>/r/n </Style>/r/n " +
- "<Style ss:ID=/"StringLiteral/">/r/n <NumberFormat" +
- " ss:Format=/"@/"/>/r/n </Style>/r/n <Style " +
- "ss:ID=/"Decimal/">/r/n <NumberFormat " +
- "ss:Format=/"0.0000/"/>/r/n </Style>/r/n " +
- "<Style ss:ID=/"Integer/">/r/n <NumberFormat " +
- "ss:Format=/"0/"/>/r/n </Style>/r/n <Style " +
- "ss:ID=/"DateLiteral/">/r/n <NumberFormat " +
- "ss:Format=/"mm/dd/yyyy;@/"/>/r/n </Style>/r/n " +
- "</Styles>/r/n ";
- const string endExcelXML = "</Workbook>";
- int rowCount = 0;
- int sheetCount = 1;
- /**//*
- <xml version>
- <Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
- xmlns:o="urn:schemas-microsoft-com:office:office"
- xmlns:x="urn:schemas-microsoft-com:office:excel"
- xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
- <Styles>
- <Style ss:ID="Default" ss:Name="Normal">
- <Alignment ss:Vertical="Bottom"/>
- <Borders/>
- <Font/>
- <Interior/>
- <NumberFormat/>
- <Protection/>
- </Style>
- <Style ss:ID="BoldColumn">
- <Font x:Family="Swiss" ss:Bold="1"/>
- </Style>
- <Style ss:ID="StringLiteral">
- <NumberFormat ss:Format="@"/>
- </Style>
- <Style ss:ID="Decimal">
- <NumberFormat ss:Format="0.0000"/>
- </Style>
- <Style ss:ID="Integer">
- <NumberFormat ss:Format="0"/>
- </Style>
- <Style ss:ID="DateLiteral">
- <NumberFormat ss:Format="mm/dd/yyyy;@"/>
- </Style>
- </Styles>
- <Worksheet ss:Name="Sheet1">
- </Worksheet>
- </Workbook>
- */
- excelDoc.Write(startExcelXML);
- excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");
- excelDoc.Write("<Table>");
- excelDoc.Write("<Row>");
- for (int x = 0; x < source.Tables[0].Columns.Count; x++)
- {
- excelDoc.Write("<Cell ss:StyleID=/"BoldColumn/"><Data ss:Type=/"String/">");
- excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
- excelDoc.Write("</Data></Cell>");
- }
- excelDoc.Write("</Row>");
- foreach (DataRow x in source.Tables[0].Rows)
- {
- rowCount++;
- //if the number of rows is > 64000 create a new page to continue output
- if (rowCount == 64000)
- {
- rowCount = 0;
- sheetCount++;
- excelDoc.Write("</Table>");
- excelDoc.Write(" </Worksheet>");
- excelDoc.Write("<Worksheet ss:Name=/"Sheet" + sheetCount + "/">");
- excelDoc.Write("<Table>");
- }
- excelDoc.Write("<Row>"); //ID=" + rowCount + "
- for (int y = 0; y < source.Tables[0].Columns.Count; y++)
- {
- System.Type rowType;
- rowType = x[y].GetType();
- switch (rowType.ToString())
- {
- case "System.String":
- string XMLstring = x[y].ToString();
- XMLstring = XMLstring.Trim();
- XMLstring = XMLstring.Replace("&", "&");
- XMLstring = XMLstring.Replace(">", ">");
- XMLstring = XMLstring.Replace("<", "<");
- excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
- "<Data ss:Type=/"String/">");
- excelDoc.Write(XMLstring);
- excelDoc.Write("</Data></Cell>");
- break;
- case "System.DateTime":
- //Excel has a specific Date Format of YYYY-MM-DD followed by
- //the letter 'T' then hh:mm:sss.lll Example 2005-01-31T24:01:21.000
- //The Following Code puts the date stored in XMLDate
- //to the format above
- DateTime XMLDate = (DateTime)x[y];
- string XMLDatetoString = ""; //Excel Converted Date
- XMLDatetoString = XMLDate.Year.ToString() +
- "-" +
- (XMLDate.Month < 10 ? "0" +
- XMLDate.Month.ToString() : XMLDate.Month.ToString()) +
- "-" +
- (XMLDate.Day < 10 ? "0" +
- XMLDate.Day.ToString() : XMLDate.Day.ToString()) +
- "T" +
- (XMLDate.Hour < 10 ? "0" +
- XMLDate.Hour.ToString() : XMLDate.Hour.ToString()) +
- ":" +
- (XMLDate.Minute < 10 ? "0" +
- XMLDate.Minute.ToString() : XMLDate.Minute.ToString()) +
- ":" +
- (XMLDate.Second < 10 ? "0" +
- XMLDate.Second.ToString() : XMLDate.Second.ToString()) +
- ".000";
- excelDoc.Write("<Cell ss:StyleID=/"DateLiteral/">" +
- "<Data ss:Type=/"DateTime/">");
- excelDoc.Write(XMLDatetoString);
- excelDoc.Write("</Data></Cell>");
- break;
- case "System.Boolean":
- excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
- "<Data ss:Type=/"String/">");
- excelDoc.Write(x[y].ToString());
- excelDoc.Write("</Data></Cell>");
- break;
- case "System.Int16":
- case "System.Int32":
- case "System.Int64":
- case "System.Byte":
- excelDoc.Write("<Cell ss:StyleID=/"Integer/">" +
- "<Data ss:Type=/"Number/">");
- excelDoc.Write(x[y].ToString());
- excelDoc.Write("</Data></Cell>");
- break;
- case "System.Decimal":
- case "System.Double":
- excelDoc.Write("<Cell ss:StyleID=/"Decimal/">" +
- "<Data ss:Type=/"Number/">");
- excelDoc.Write(x[y].ToString());
- excelDoc.Write("</Data></Cell>");
- break;
- case "System.DBNull":
- excelDoc.Write("<Cell ss:StyleID=/"StringLiteral/">" +
- "<Data ss:Type=/"String/">");
- excelDoc.Write("");
- excelDoc.Write("</Data></Cell>");
- break;
- default:
- throw (new Exception(rowType.ToString() + " not handled."));
- }
- }
- excelDoc.Write("</Row>");
- }
- excelDoc.Write("</Table>");
- excelDoc.Write(" </Worksheet>");
- excelDoc.Write(endExcelXML);
- excelDoc.Close();
- }
可惜, 生成的倒是XML格式了,但还是上传格式不正确,考虑到客户端可能是Excel2007/2003-97等等,决定放弃保存Excel到客户端的方式。
第三种尝试方案:用cvs保存,将第一种方法改为:
- Response.ContentType = "text/csv";//"application/vnd.ms-excel";
- Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName + ".cvs");
生成格式大致如下:
- "品名","最高价格","最低价格","平均价格","计量单位","备注"
- "青菜","","","","元/公斤",""
- "南瓜","","","","元/公斤",""
- "瓠子","","","","元/公斤",""
- "冬春笋","","","","元/公斤",""
- "雪里蕻","","","","元/公斤",""
- "樱桃萝卜","","","","元/公斤",""
- "佛手瓜","","","","元/公斤",""
- "白菜鼎","","","","元/公斤",""
- "蒜","","","","元/公斤",""
- "食用菌","","","","元/公斤",""
- "黄瓜","","","","元/公斤",""
导入到SQL Server时有多种方法:
给出简要代码:
- public static DataTable GetDataFromCSV(string filePath,int beginColumn)
- {
- int intColCount = 0;
- bool blnFlag = true;
- DataTable mydt = new DataTable("myTableName");
- DataColumn mydc;
- DataRow mydr;
- string strline;
- string[] aryline;
- System.IO.StreamReader mysr = new System.IO.StreamReader(filePath,System.Text.Encoding.UTF8);
- int FlagFirst =1;
- while ((strline = mysr.ReadLine()) != null)
- {
- //if (beginColumn == FlagFirst) { FlagFirst++; continue; }
- strline = strline.Replace("/n", "");
- strline = strline.Replace("/r", "");
- strline = strline.Replace("/t", "");
- //aryline = strline.Split(new char[] { '|' });
- aryline = strline.Split(new char[] { ',' });
- if (blnFlag)
- {
- blnFlag = false;
- intColCount = aryline.Length;
- for (int i = 0; i < aryline.Length; i++)
- {
- mydc = new DataColumn(aryline[i]);
- mydt.Columns.Add(mydc);
- }
- }
- mydr = mydt.NewRow();
- for (int i = 0; i < intColCount; i++)
- {
- mydr[i] = aryline[i];
- }
- mydt.Rows.Add(mydr);
- FlagFirst++;
- }
- mysr.Close();
- mysr.Dispose();
- return mydt;
- }
也有人给出方案,直接从cvs中读取
- public static string PreFilePath=@"c:/Excel/";
- public static string strconn = @"Driver={Microsoft Text Driver (*.txt; *.csv)};Dbq="+PreFilePath+";Extensions=asc,csv,tab,txt;";
- public static DataSet GetDataSetFromCSV( string filename)
- {
- OdbcConnection objconn = new OdbcConnection(strconn);
- DataSet dscsv = new DataSet();
- try
- {
- string strsql = "select * from " + filename; //filename, for example: 1.csv
- OdbcDataAdapter odbccsvdataadapter = new OdbcDataAdapter(strsql, objconn);
- odbccsvdataadapter.Fill(dscsv);
- return dscsv;
- }
- catch (Exception ex)
- {
- throw ex;
- }
- }
尝试失败!!
Code Project 上有篇文章,
A Fast CSV Reader
也可以试试。
虽然也可以凑合用, 但总觉得CVS出错的可能性更大。
到此,决定放弃客户端生成的方式,请看下篇
DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon)
- DataSet导出到Excel比较完整的解决方案(一)--客户端生成文件(downmoon)
- DataSet导出到Excel比较完整的解决方案(一)--客户端生成文件(downmoon)
- DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon)
- DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件
- DataSet导出到Excel比较完整的解决方案
- DataSet导出到Excel比较完整的解决方案
- 把DATASET里的多个表导出到一个excel文件?
- 将DataSet导出到Excel文件中
- 导出GridView到Excel的关键之处(downmoon)
- Dataset导出到excel
- DataSet导出到Excel
- dataset 导出到 excel
- DataSet导出到Excel,并生成文件(C#实现,可合并行和列)
- 导出DataSet的数据到Excel中
- 导出GridView到Excel中(downmoon)
- dataset导出EXCEL文件
- DataTable导出到Excel的解决方案(.csv)文件
- WinForm导出DataSet到Excel
- Wireless 2200BG破解WEP
- 互联网Windows:Live Mesh助力微软“云计算”
- 360安全卫士 导致MySQL 5.0.24 自动关闭
- 团队管理
- 放假了
- DataSet导出到Excel比较完整的解决方案(一)--客户端生成文件(downmoon)
- boost静态断言
- 1.17
- 所谓的云计算
- Puppy Linux U盘 Linux
- Vista 105个禁止系统功能的注册表条目
- NASM写的DOS程序:Hello World!
- 第一篇日志
- 谨慎程序员创业的思维障碍