`
webcode
  • 浏览: 5942751 次
  • 性别: Icon_minigender_1
  • 来自: 上海
文章分类
社区版块
存档分类
最新评论

DataSet导出到Excel比较完整的解决方案(一)--客户端生成文件(downmoon)

 
阅读更多

有一客户需求：

1、要从SQL Server数据库导出并生成Excel ;

2、用户下载对应的Excel并填写上传再导入到SQL server。

费了将近六个小时，故一定要把过程写下来，希望看到此文的朋友少走些不必要的弯路。

首先，想到的是直接导出到客户端，代码如下：

view plaincopy to clipboardprint?
  1. publicstaticvoidDataSetToExcel(DataSetoDS,HttpResponseResponse,stringfileName)
  2. {
  3. if(oDS==null||oDS.Tables[0]==null||oDS.Tables[0].Rows.Count==0){return;}
  4. Response.Clear();
  5. //EncodingpageEncode=Encoding.GetEncoding(PageEncode);
  6. HttpContext.Current.Response.Charset="gb2312";
  7. //Response.ContentType="application/vnd-excel";//"application/vnd.ms-excel";
  8. //Response.ContentType="application/x-octet-stream";//"application/vnd.ms-excel";
  9. Response.ContentType="text/csv";//"application/vnd.ms-excel";
  10. Response.AppendHeader("Content-Disposition","attachment;filename="+fileName+".cvs");
  11. System.IO.StringWriteroSW=newSystem.IO.StringWriter();
  12. HtmlTextWriteroHW=newHtmlTextWriter(oSW);
  13. DataGriddg=newDataGrid();
  14. dg.DataSource=oDS.Tables[0];
  15. dg.DataBind();
  16. dg.RenderControl(oHW);
  17. Response.Write(oSW.ToString());
  18. Response.Flush();
  19. Response.Close();
  20. }

这样生成是生成了！ 客户也可以用Excel直接打开并编辑，问题来了！ 上传时出错，仔细看看生成的Excel.xls,

用记事本打开，内容大致如下：

view plaincopy to clipboardprint?
  1. <tablecellspacing="0"rules="all"border="1"style="border-collapse:collapse;"mce_style="border-collapse:collapse;">
  2. <tr>
  3. <td>品名</td><td>最高价格</td><td>最低价格</td><td>平均价格</td><td>计量单位</td><td>备注</td>
  4. </tr><tr>
  5. <td>青菜</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  6. </tr><tr>
  7. <td>南瓜</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  8. </tr><tr>
  9. <td>瓠子</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  10. </tr><tr>
  11. <td>冬春笋</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  12. </tr><tr>
  13. <td>雪里蕻</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  14. </tr><tr>
  15. <td>樱桃萝卜</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  16. </tr><tr>
  17. <td>佛手瓜</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  18. </tr><tr>
  19. <td>白菜鼎</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  20. </tr><tr>
  21. <td>蒜</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  22. </tr><tr>
  23. <td>食用菌</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  24. </tr><tr>
  25. <td>黄瓜</td><td></td><td></td><td></td><td>元/公斤</td><td></td>
  26. </tr>
  27. </table>

原来，就是纯粹的html格式，披了件Excel的外衣。这样用户传上来的文件当然不是标准的Excel格式了！

于是， 想到直接生成xml格式的Excel文档，方法如下 ：

view plaincopy to clipboardprint?
  1. ///<summary>
  2. ///执行导出
  3. ///</summary>
  4. ///<paramname="ds">要导出的DataSet</param>
  5. ///<paramname="strExcelFileName">要导出的文件名</param>
  6. publicstaticvoidExportToExcel(DataSetsource,stringfileName)
  7. {
  8. System.IO.StreamWriterexcelDoc;
  9. excelDoc=newSystem.IO.StreamWriter(fileName);
  10. conststringstartExcelXML="<xmlversion>/r/n<Workbook"+
  11. "xmlns=/"urn:schemas-microsoft-com:office:spreadsheet/"/r/n"+
  12. "xmlns:o=/"urn:schemas-microsoft-com:office:office/"/r/n"+
  13. "xmlns:x=/"urn:schemas-microsoft-com:office:"+
  14. "excel/"/r/nxmlns:ss=/"urn:schemas-microsoft-com:"+
  15. "office:spreadsheet/">/r/n<Styles>/r/n"+
  16. "<Styless:ID=/"Default/"ss:Name=/"Normal/">/r/n"+
  17. "<Alignmentss:Vertical=/"Bottom/"/>/r/n<Borders/>"+
  18. "/r/n<Font/>/r/n<Interior/>/r/n<NumberFormat/>"+
  19. "/r/n<Protection/>/r/n</Style>/r/n"+
  20. "<Styless:ID=/"BoldColumn/">/r/n<Font"+
  21. "x:Family=/"Swiss/"ss:Bold=/"1/"/>/r/n</Style>/r/n"+
  22. "<Styless:ID=/"StringLiteral/">/r/n<NumberFormat"+
  23. "ss:Format=/"@/"/>/r/n</Style>/r/n<Style"+
  24. "ss:ID=/"Decimal/">/r/n<NumberFormat"+
  25. "ss:Format=/"0.0000/"/>/r/n</Style>/r/n"+
  26. "<Styless:ID=/"Integer/">/r/n<NumberFormat"+
  27. "ss:Format=/"0/"/>/r/n</Style>/r/n<Style"+
  28. "ss:ID=/"DateLiteral/">/r/n<NumberFormat"+
  29. "ss:Format=/"mm/dd/yyyy;@/"/>/r/n</Style>/r/n"+
  30. "</Styles>/r/n";
  31. conststringendExcelXML="</Workbook>";
  32. introwCount=0;
  33. intsheetCount=1;
  34. /**//*
  35. <xmlversion>
  36. <Workbookxmlns="urn:schemas-microsoft-com:office:spreadsheet"
  37. xmlns:o="urn:schemas-microsoft-com:office:office"
  38. xmlns:x="urn:schemas-microsoft-com:office:excel"
  39. xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet">
  40. <Styles>
  41. <Styless:ID="Default"ss:Name="Normal">
  42. <Alignmentss:Vertical="Bottom"/>
  43. <Borders/>
  44. <Font/>
  45. <Interior/>
  46. <NumberFormat/>
  47. <Protection/>
  48. </Style>
  49. <Styless:ID="BoldColumn">
  50. <Fontx:Family="Swiss"ss:Bold="1"/>
  51. </Style>
  52. <Styless:ID="StringLiteral">
  53. <NumberFormatss:Format="@"/>
  54. </Style>
  55. <Styless:ID="Decimal">
  56. <NumberFormatss:Format="0.0000"/>
  57. </Style>
  58. <Styless:ID="Integer">
  59. <NumberFormatss:Format="0"/>
  60. </Style>
  61. <Styless:ID="DateLiteral">
  62. <NumberFormatss:Format="mm/dd/yyyy;@"/>
  63. </Style>
  64. </Styles>
  65. <Worksheetss:Name="Sheet1">
  66. </Worksheet>
  67. </Workbook>
  68. */
  69. excelDoc.Write(startExcelXML);
  70. excelDoc.Write("<Worksheetss:Name=/"Sheet"+sheetCount+"/">");
  71. excelDoc.Write("<Table>");
  72. excelDoc.Write("<Row>");
  73. for(intx=0;x<source.Tables[0].Columns.Count;x++)
  74. {
  75. excelDoc.Write("<Cellss:StyleID=/"BoldColumn/"><Datass:Type=/"String/">");
  76. excelDoc.Write(source.Tables[0].Columns[x].ColumnName);
  77. excelDoc.Write("</Data></Cell>");
  78. }
  79. excelDoc.Write("</Row>");
  80. foreach(DataRowxinsource.Tables[0].Rows)
  81. {
  82. rowCount++;
  83. //ifthenumberofrowsis>64000createanewpagetocontinueoutput
  84. if(rowCount==64000)
  85. {
  86. rowCount=0;
  87. sheetCount++;
  88. excelDoc.Write("</Table>");
  89. excelDoc.Write("</Worksheet>");
  90. excelDoc.Write("<Worksheetss:Name=/"Sheet"+sheetCount+"/">");
  91. excelDoc.Write("<Table>");
  92. }
  93. excelDoc.Write("<Row>");//ID="+rowCount+"
  94. for(inty=0;y<source.Tables[0].Columns.Count;y++)
  95. {
  96. System.TyperowType;
  97. rowType=x[y].GetType();
  98. switch(rowType.ToString())
  99. {
  100. case"System.String":
  101. stringXMLstring=x[y].ToString();
  102. XMLstring=XMLstring.Trim();
  103. XMLstring=XMLstring.Replace("&","&");
  104. XMLstring=XMLstring.Replace(">",">");
  105. XMLstring=XMLstring.Replace("<","<");
  106. excelDoc.Write("<Cellss:StyleID=/"StringLiteral/">"+
  107. "<Datass:Type=/"String/">");
  108. excelDoc.Write(XMLstring);
  109. excelDoc.Write("</Data></Cell>");
  110. break;
  111. case"System.DateTime":
  112. //ExcelhasaspecificDateFormatofYYYY-MM-DDfollowedby
  113. //theletter'T'thenhh:mm:sss.lllExample2005-01-31T24:01:21.000
  114. //TheFollowingCodeputsthedatestoredinXMLDate
  115. //totheformatabove
  116. DateTimeXMLDate=(DateTime)x[y];
  117. stringXMLDatetoString="";//ExcelConvertedDate
  118. XMLDatetoString=XMLDate.Year.ToString()+
  119. "-"+
  120. (XMLDate.Month<10?"0"+
  121. XMLDate.Month.ToString():XMLDate.Month.ToString())+
  122. "-"+
  123. (XMLDate.Day<10?"0"+
  124. XMLDate.Day.ToString():XMLDate.Day.ToString())+
  125. "T"+
  126. (XMLDate.Hour<10?"0"+
  127. XMLDate.Hour.ToString():XMLDate.Hour.ToString())+
  128. ":"+
  129. (XMLDate.Minute<10?"0"+
  130. XMLDate.Minute.ToString():XMLDate.Minute.ToString())+
  131. ":"+
  132. (XMLDate.Second<10?"0"+
  133. XMLDate.Second.ToString():XMLDate.Second.ToString())+
  134. ".000";
  135. excelDoc.Write("<Cellss:StyleID=/"DateLiteral/">"+
  136. "<Datass:Type=/"DateTime/">");
  137. excelDoc.Write(XMLDatetoString);
  138. excelDoc.Write("</Data></Cell>");
  139. break;
  140. case"System.Boolean":
  141. excelDoc.Write("<Cellss:StyleID=/"StringLiteral/">"+
  142. "<Datass:Type=/"String/">");
  143. excelDoc.Write(x[y].ToString());
  144. excelDoc.Write("</Data></Cell>");
  145. break;
  146. case"System.Int16":
  147. case"System.Int32":
  148. case"System.Int64":
  149. case"System.Byte":
  150. excelDoc.Write("<Cellss:StyleID=/"Integer/">"+
  151. "<Datass:Type=/"Number/">");
  152. excelDoc.Write(x[y].ToString());
  153. excelDoc.Write("</Data></Cell>");
  154. break;
  155. case"System.Decimal":
  156. case"System.Double":
  157. excelDoc.Write("<Cellss:StyleID=/"Decimal/">"+
  158. "<Datass:Type=/"Number/">");
  159. excelDoc.Write(x[y].ToString());
  160. excelDoc.Write("</Data></Cell>");
  161. break;
  162. case"System.DBNull":
  163. excelDoc.Write("<Cellss:StyleID=/"StringLiteral/">"+
  164. "<Datass:Type=/"String/">");
  165. excelDoc.Write("");
  166. excelDoc.Write("</Data></Cell>");
  167. break;
  168. default:
  169. throw(newException(rowType.ToString()+"nothandled."));
  170. }
  171. }
  172. excelDoc.Write("</Row>");
  173. }
  174. excelDoc.Write("</Table>");
  175. excelDoc.Write("</Worksheet>");
  176. excelDoc.Write(endExcelXML);
  177. excelDoc.Close();
  178. }

可惜， 生成的倒是XML格式了，但还是上传格式不正确，考虑到客户端可能是Excel2007/2003-97等等，决定放弃保存Excel到客户端的方式。

第三种尝试方案：用cvs保存，将第一种方法改为：

  1. Response.ContentType="text/csv";//"application/vnd.ms-excel";
  2. Response.AppendHeader("Content-Disposition","attachment;filename="+fileName+".cvs");



生成格式大致如下：

view plaincopy to clipboardprint?
  1. "品名","最高价格","最低价格","平均价格","计量单位","备注"
  2. "青菜","","","","元/公斤",""
  3. "南瓜","","","","元/公斤",""
  4. "瓠子","","","","元/公斤",""
  5. "冬春笋","","","","元/公斤",""
  6. "雪里蕻","","","","元/公斤",""
  7. "樱桃萝卜","","","","元/公斤",""
  8. "佛手瓜","","","","元/公斤",""
  9. "白菜鼎","","","","元/公斤",""
  10. "蒜","","","","元/公斤",""
  11. "食用菌","","","","元/公斤",""
  12. "黄瓜","","","","元/公斤",""

导入到SQL Server时有多种方法：

给出简要代码：

view plaincopy to clipboardprint?
  1. publicstaticDataTableGetDataFromCSV(stringfilePath,intbeginColumn)
  2. {
  3. intintColCount=0;
  4. boolblnFlag=true;
  5. DataTablemydt=newDataTable("myTableName");
  6. DataColumnmydc;
  7. DataRowmydr;
  9. stringstrline;
  10. string[]aryline;
  11. System.IO.StreamReadermysr=newSystem.IO.StreamReader(filePath,System.Text.Encoding.UTF8);
  12. intFlagFirst=1;
  13. while((strline=mysr.ReadLine())!=null)
  14. {
  15. //if(beginColumn==FlagFirst){FlagFirst++;continue;}
  16. strline=strline.Replace("/n","");
  17. strline=strline.Replace("/r","");
  18. strline=strline.Replace("/t","");
  19. //aryline=strline.Split(newchar[]{'|'});
  20. aryline=strline.Split(newchar[]{','});
  21. if(blnFlag)
  22. {
  23. blnFlag=false;
  24. intColCount=aryline.Length;
  25. for(inti=0;i<aryline.Length;i++)
  26. {
  27. mydc=newDataColumn(aryline[i]);
  28. mydt.Columns.Add(mydc);
  29. }
  30. }
  31. mydr=mydt.NewRow();
  32. for(inti=0;i<intColCount;i++)
  33. {
  34. mydr[i]=aryline[i];
  35. }
  36. mydt.Rows.Add(mydr);
  37. FlagFirst++;
  38. }
  40. mysr.Close();
  41. mysr.Dispose();
  42. returnmydt;
  43. }

也有人给出方案，直接从cvs中读取

view plaincopy to clipboardprint?
  1. publicstaticstringPreFilePath=@"c:/Excel/";
  2. publicstaticstringstrconn=@"Driver={MicrosoftTextDriver(*.txt;*.csv)};Dbq="+PreFilePath+";Extensions=asc,csv,tab,txt;";
  3. publicstaticDataSetGetDataSetFromCSV(stringfilename)
  4. {
  5. OdbcConnectionobjconn=newOdbcConnection(strconn);
  6. DataSetdscsv=newDataSet();
  7. try
  8. {
  9. stringstrsql="select*from"+filename;//filename,forexample:1.csv
  10. OdbcDataAdapterodbccsvdataadapter=newOdbcDataAdapter(strsql,objconn);
  11. odbccsvdataadapter.Fill(dscsv);
  12. returndscsv;
  13. }
  14. catch(Exceptionex)
  15. {
  16. throwex;
  17. }
  18. }

尝试失败！！

Code Project 上有篇文章，

A Fast CSV Reader

也可以试试。

虽然也可以凑合用， 但总觉得CVS出错的可能性更大。

到此，决定放弃客户端生成的方式,请看下篇

DataSet导出到Excel比较完整的解决方案(二)--服务器端生成文件(downmoon)


分享到：
| 剖析PHP中的输出缓冲 flush之类
评论
发表评论

您还没有登录,请您登录后再发表评论

相关推荐

Magicbox
Global site tag (gtag.js) - Google Analytics