在C#中实现vb中的createobject方法，将dataset导出excel文件

将dataset导出excel文件
//参数dt是数据表，fileName是生成Excel的名字
private void CreateExcel(DataTable dt, string fileName)
{ HttpResponse resp;
resp = Page.Response;
resp.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");
resp.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
string colHeaders = "", ls_item = ""; ////定义表对象与行对象，同时用DataSet对其值进行初始化
//DataTable dt = ds.Tables[0];
DataRow[] myRow = dt.Select();//可以类似dt.Select("id>10")之形式达到数据筛选目的
int i = 0;
int cl = dt.Columns.Count; //取得数据表各列标题，各标题之间以t分割，最后一个列标题后加回车符
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加n
{
colHeaders += dt.Columns[i].Caption.ToString() + "\n";
}
else
{
colHeaders += dt.Columns[i].Caption.ToString() + "\t";
} }
resp.Write(colHeaders);
//向HTTP输出流中写入取得的数据信息 //逐行处理数据
foreach (DataRow row in myRow)
{
//当前行数据写入HTTP输出流，并且置空ls_item以便下行数据
for (i = 0; i < cl; i++)
{
if (i == (cl - 1))//最后一列，加n
{
ls_item += row[i].ToString() + "\n";
}
else
{
ls_item += row[i].ToString() + "\t";
} }
resp.Write(ls_item);
ls_item = ""; }
resp.End();
} //生成xml
protected void Button3_Click(object sender, EventArgs e)
{
DataSet ds = new DataSet();
SqlConnection conn = new SqlConnection("database=库;server=.;uid=sa;pwd=;");
string sql1 = "select * from 表";
SqlDataAdapter da = new SqlDataAdapter(sql1, conn);
da.Fill(ds);
DataTable dt = ds.Tables[0].Copy();
CreateExcel(dt, "table");
}

将excel文件导入datasetfilePath为文件的路径public static DataTable Import(string filePath)
 {
 DataTable rs = new DataTable(); bool canOpen = false; OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;" +
 "Data Source=" + filePath + ";" +
 "Extended Properties=\"Excel 8.0;\""); try//尝试数据连接是否可用
 {
 conn.Open();
 conn.Close();
 canOpen = true;
 }
 catch { } if (canOpen)
 { //得到工作表的名称
 DataTable dt = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null); string tablename = dt.Rows[0][2].ToString().Trim(); if (tablename.Length > 0)
 {
 OleDbCommand myOleDbCommand = new OleDbCommand("SELECT * FROM [" + tablename + "]", conn);
 OleDbDataAdapter myData = new OleDbDataAdapter(myOleDbCommand);
 myData.Fill(rs);
 conn.Close();
 }
 }
 else//如何Excel的格式不是标准的需要转换一下( 只针对<table><tr><td></td></tr>)
 {
 System.IO.StreamReader tmpStream = File.OpenText(filePath);
 string tmpStr = tmpStream.ReadToEnd();
 tmpStream.Close();
 rs = GetDataTableFromString(tmpStr);
 tmpStr = "";
 }
 return rs;
 } //此方法来处理Excel的格式为表格（有tr、td）
 private static DataTable GetDataTableFromString(string tmpHtml)
 {
 string tmpStr=tmpHtml;
 DataTable TB=new DataTable();
 //先处理一下这个字符串，删除第一个<tr>之前合最后一个</tr>之后的部分
 int index=tmpStr.IndexOf("<tr");
 if(index>-1)
 tmpStr=tmpStr.Substring(index);
 else
 return TB; index=tmpStr.LastIndexOf("</tr>");
 if(index>-1)
 tmpStr=tmpStr.Substring(0,index+5);
 else
 return TB; bool existsSparator=false;
 char Separator=Convert.ToChar("^"); //如果原字符串中包含分隔符“^”则先把它替换掉
 if(tmpStr.IndexOf(Separator.ToString())>-1)
 {
 existsSparator=true;
 tmpStr=tmpStr.Replace("^","^$&^");
 } //先根据“</tr>”分拆
 string[] tmpRow=tmpStr.Replace("</tr>","^").Split(Separator);

 for(int i=0;i<tmpRow.Length-1;i++)
 {
 DataRow newRow=TB.NewRow(); string tmpStrI=tmpRow[i];
 if(tmpStrI.IndexOf("<tr")>-1)
 {
 tmpStrI=tmpStrI.Substring(tmpStrI.IndexOf("<tr"));
 if(tmpStrI.IndexOf("display:none")<0||tmpStrI.IndexOf("display:none")>tmpStrI.IndexOf(">"))
 {
 if (i == 0)
 tmpStrI = tmpStrI.Replace("</th>", "^");//取表头
 else
 tmpStrI=tmpStrI.Replace("</td>","^");
 string[] tmpField=tmpStrI.Split(Separator); for(int j=0;j<tmpField.Length-1;j++)
 {
 tmpField[j]=RemoveString(tmpField[j],"");
 index=tmpField[j].LastIndexOf(">")+1;
 if(index>0)
 {
 string field=tmpField[j].Substring(index,tmpField[j].Length-index);
 if(existsSparator)
 field=field.Replace("^$&^","^");
 if(i==0)
 {
 string tmpFieldName=field;
 int sn=1;
 while(TB.Columns.Contains(tmpFieldName))
 {
 tmpFieldName=field+sn.ToString();
 sn+=1;
 }
 TB.Columns.Add(tmpFieldName);
 }
 else
 {
 newRow[j]=field;
 }
 }//end of if(index>0)
 } if(i>0)
 TB.Rows.Add(newRow);
 }
 }
 } TB.AcceptChanges();
 return TB;
 } /// <summary>
 /// 从指定Html字符串中剔除指定的对象
 /// </summary>
 /// <param name="tmpHtml">Html字符串</param>
 /// <param name="remove">需要剔除的对象--例如输入""则剔除""和">"</param>
 /// <returns></returns>
 public static string RemoveString(string tmpHtml, string remove)
 {
 tmpHtml = tmpHtml.Replace(remove.Replace("<", "</"), "");
 tmpHtml = RemoveStringHead(tmpHtml, remove);
 return tmpHtml;
 } /// <summary>
 /// 只供方法RemoveString()使用
 /// </summary>
 /// <returns></returns>
 private static string RemoveStringHead(string tmpHtml, string remove)
 {
 //为了方便注释，假设输入参数remove=""
 if (remove.Length < 1) return tmpHtml;//参数remove为空：不处理返回
 if ((remove.Substring(0, 1) != "<") || (remove.Substring(remove.Length - 1) != ">")) return tmpHtml;//参数remove不是<?????>：不处理返回 int IndexS = tmpHtml.IndexOf(remove.Replace(">", ""));//查找“<font”的位置
 int IndexE = -1;
 if (IndexS > -1)
 {
 string tmpRight = tmpHtml.Substring(IndexS, tmpHtml.Length - IndexS);
 IndexE = tmpRight.IndexOf(">");
 if (IndexE > -1)
 tmpHtml = tmpHtml.Substring(0, IndexS) + tmpHtml.Substring(IndexS + IndexE + 1);
 if (tmpHtml.IndexOf(remove.Replace(">", "")) > -1)
 tmpHtml = RemoveStringHead(tmpHtml, remove);
 }
 return tmpHtml;
 }private static int GetSheetIndex(byte[] FindTarget, byte[] FindItem)
 {
 int index = -1; int FindItemLength = FindItem.Length;
 if (FindItemLength < 1) return -1;
 int FindTargetLength = FindTarget.Length;
 if ((FindTargetLength - 1) < FindItemLength) return -1; for (int i = FindTargetLength - FindItemLength - 1; i > -1; i--)
 {
 System.Collections.ArrayList tmpList = new System.Collections.ArrayList();
 int find = 0;
 for (int j = 0; j < FindItemLength; j++)
 {
 if (FindTarget[i + j] == FindItem[j]) find += 1;
 }
 if (find == FindItemLength)
 {
 index = i;
 break;
 }
 }
 return index;
 }

你这个是在web系统中的吧！我说的是winform程序

    public void CreateExcel2(DataTable dt){
        Excel.Application excel = new Excel.Application();
        int rowIndex = 1;
        int colIndex = 0;
        excel.Application.Workbooks.Add(true);

        foreach(DataColumn col in dt.Columns){
            colIndex++;
            excel.Cells[1, colIndex] = col.ColumnName;        }        foreach(DataRow row in dt.Rows){
            rowIndex++;
            colIndex = 0;
            foreach (DataColumn col in dt.Columns)
            {
                colIndex++;
                excel.Cells[rowIndex, colIndex] = row[col.ColumnName].ToString();
            }        }
        excel.Visible = false;
    //    excel.Sheets[0] = "sss";
        excel.ActiveWorkbook.SaveAs("D:/Hyena/MyExcel.xls", Excel.XlFileFormat.xlExcel9795, null, null, false, false, Excel.XlSaveAsAccessMode.xlNoChange, null, null, null, null, null);        excel.Quit();
        excel = null;        GC.Collect();
    }

google "npoi"
http://www.google.com.hk/search?client=pub-5434506002917399&prog=aff&channel=2000052003&q=npoi

调试易

在C#中实现vb中的createobject方法，将dataset导出excel文件

解决方案 »