.NET中用C#将DataTable中的数据集导出为EXCEL或CSV文件

.NET中用C#将DataTable中的数据集导出为EXCEL 或CSV文件

有的时候客户需要将筛选后的数据从服务器上以EXCEL或CSV文件格式保存到本地，

比如：用户需要将服务器存储的客户邮箱信息下载到本地，然后导入到126邮箱。

进行处理：

CSV

DataTable dt = DBHelper.GetTable(CommandType.Text, cmdText);

if (dt.Rows.Count != 0)

{

int rows = dt.Rows.Count;

Response.ClearHeaders();

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("数据导出文件" + DateTime.Now.Ticks + ".csv"));

System.IO.StreamWriter objWriter = new

System.IO.StreamWriter(Response.OutputStream,

System.Text.Encoding.UTF8);

StringBuilder result = new StringBuilder();

result.Append("联系组,");

result.Append("姓名,");

result.Append("邮件地址,");

result.Append("联系地址,");

result.Append("邮政编码,");

result.Append("联系电话,");

result.Append("移动电话,");

result.Append("公司,");

result.Append("部门,");

result.Append("职位,");

objWriter.WriteLine(result.ToString()); //输出标题行CSV

文件中以“，”隔开，当拼凑到一行时，用WriteLine输出，而不是Write

for (int i = 0; i < rows; i++)

{

result = new StringBuilder();

result.Append("邮箱联系组,");

result.Append(dt.Rows[i]["lianxiren"].ToString() + ",");

result.Append(dt.Rows[i]["email"].ToString() + ","); result.Append(dt.Rows[i]["addr"].ToString() + ",");

result.Append(dt.Rows[i]["postcode"].ToString() + ","); result.Append(dt.Rows[i]["tel"].ToString() + ",");

result.Append(dt.Rows[i]["mobile"].ToString() + ","); result.Append(dt.Rows[i]["compname"].ToString() + ","); result.Append(dt.Rows[i]["dpt"].ToString() + ",");

result.Append(dt.Rows[i]["position"].ToString() + ","); objWriter.WriteLine(result.ToString());

}

objWriter.Close();

Response.End();

}

EXCEL

DataTable dt = DBHelper.GetTable(CommandType.Text, cmdText);

if (dt.Rows.Count != 0)

{

int rows = dt.Rows.Count;

Response.ContentType = "application/vnd.ms-excel";

Response.Charset = "";

Response.AppendHeader("Content-Disposition", "attachment;filename=" + Server.UrlEncode("导出文件.xls"));

System.IO.StreamWriter objWriter = new

System.IO.StreamWriter(Response.OutputStream,

System.Text.Encoding.UTF8);

objWriter.WriteLine("");

objWriter.WriteLine("

progid=\"Excel.Sheet\"?>");

objWriter.WriteLine("

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\"

xmlns:html=\"https://www.doczj.com/doc/de1081358.html,/TR/REC-html40\">");

objWriter.WriteLine("");

objWriter.WriteLine("

objWriter.WriteLine("

ss:Width=\"33\"/>");

objWriter.WriteLine("

ss:Width=\"250\"/>");

objWriter.WriteLine("

ss:Width=\"250\"/>");

objWriter.WriteLine("

ss:Width=\"250\"/>");

objWriter.WriteLine("

ss:Width=\"130\"/>");

objWriter.WriteLine("");

objWriter.WriteLine("编号");

objWriter.WriteLine("第一列");

objWriter.WriteLine("第二列");

objWriter.WriteLine("第三列");

objWriter.WriteLine("第四列");

objWriter.WriteLine("");

for (int i = 0; i < rows; i++) //注：一直用WriteLine进行输出，因为CSV文件是以“，”为分割的简单文件格式。而EXCEL是记录行的 {

objWriter.WriteLine("");

objWriter.WriteLine("" + (i + 1).ToString() + "");

objWriter.WriteLine("" + dt.Rows[i]["row1"].ToString() + " " + "");

objWriter.WriteLine("" + dt.Rows[i]["row2"].ToString() + " " + "");

objWriter.WriteLine("" + dt.Rows[i]["row3"].ToString() + "");

objWriter.WriteLine("" + dt.Rows[i]["row4"].ToString() + "");

objWriter.WriteLine("");

}

objWriter.WriteLine("

objWriter.WriteLine("");

objWriter.WriteLine("");

objWriter.Close();

Response.End();

}

FROM : jhfcjl

导出数据到EXCEL

Private Sub Command1_Click() Dim 文件名称As String 文件名称= "C:\Documents and Settings\Administrator\桌面\Hellod.Doc " Dim uObjDoc As Word.Document Set uObjWord = New Word.Application uObjWord.Documents.Add "Normal", IIf(iMode = 1, True, False) '1、根据"Normal"模板添加一个新的文档[1-创建一个模板，0-创建一个文件] uObjWord.Documents.Save NoPrompt:=True, OriginalFormat:=wdWordDocument '2、设置Word对象为自动保存模式 uObjWord.ActiveDocument.SaveAs 文件名称'3、保存Word文档到文件名称 Set uObjDoc = Nothing End Sub EXCEL用法 Public Function ExporToExcel(strOpen As String) '********************************************************* '* 名称：ExporToExcel '* 功能：导出数据到EXCEL '* 用法：ExporToExcel(sql查询字符串) '********************************************************* Dim Rs_Data As New ADODB.Recordset Dim Irowcount As Integer Dim Icolcount As Integer Dim xlApp As New Excel.Application Dim xlBook As Excel.Workbook Dim xlSheet As Excel.Worksheet Dim xlQuery As Excel.QueryTable With Rs_Data If .State = adStateOpen Then .Close End If .ActiveConnection = Cn .CursorLocation = adUseClient .CursorType = adOpenStatic .LockType = adLockReadOnly .Source = strOpen .Open End With

excel导入导出通用方法

1.package https://www.doczj.com/doc/de1081358.html,mon.utils; 2.import java.io.OutputStream; 3.import java.util.List; 4.import javax.servlet.http.HttpServletResponse; 5.import org.apache.struts2.ServletActionContext; 6.import https://www.doczj.com/doc/de1081358.html,ng.reflect.Field; 7. 8.import jxl.Workbook; 9.import jxl.format.Alignment; 10.import jxl.format.Border; 11.import jxl.format.BorderLineStyle; 12.import jxl.format.VerticalAlignment; 13.import https://www.doczj.com/doc/de1081358.html,bel; 14.import jxl.write.WritableCellFormat; 15.import jxl.write.WritableFont; 16.import jxl.write.WritableSheet; 17.import jxl.write.WritableWorkbook; 18./*** 19. * @author lsf 20. */ 21.public class ExportExcel { 22./*************************************************************************** 23. * @param fileName EXCEL文件名称 24. * @param listTitle EXCEL文件第一行列标题集合 25. * @param listContent EXCEL文件正文数据集合 26. * @return 27. */ 28.public final static String exportExcel(String fileName,String[] Title, List