excel文件在日常生活中的作用毋庸置疑,面对小数据量,我们通常采用一一录入的方式,但是面对大量的数据的时候,我们可能就需要采用其他的办法。举个最近的例子:我们毕业设计的时候,论文选题没有使用教学管理系统,而是班委把纸质的题目集拿到班级,然后随机的抽取后汇总电子档交给院系,120多人,大量的数据需要录入,想必没有人愿意这样做~我想采用的做法是用ASP.NET制作一个网页,然后让同学登录网页,随机抽取题目,并将抽取的结果显示在页面上,等所有的同学抽取完毕,班委直接下载生成的excel表格即可。
下面是winform的演示结果
![ContractedBlock.gif](https://i-blog.csdnimg.cn/blog_migrate/8f900a89c6347c561fdf2122f13be562.gif)
![ExpandedBlockStart.gif](https://i-blog.csdnimg.cn/blog_migrate/961ddebeb323a10fe0623af514929fc1.gif)
using
System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace download
{
public partial class Form1 : Form
{
public void con()
{
SqlConnection downcon = new SqlConnection();
downcon.ConnectionString = @" Initial Catalog=stuinfo;Data Source=(local);Integrated Security=SSPI; User Instance = false " ;
downcon.Open();
SqlCommand downcom = new SqlCommand( "" ,downcon);
downcom.CommandText = @" select * from stucard " ;
SqlDataAdapter downda = new SqlDataAdapter(downcom);
DataSet downset = new DataSet();
downda.Fill(downset, " stucard " );
dataGridView1.DataSource = downset.Tables[ " stucard " ];
downcon.Close();
}
public Form1()
{
InitializeComponent();
con();
}
private void button1_Click( object sender, EventArgs e)
{
SaveFileDialog newdlg = new SaveFileDialog();
newdlg.InitialDirectory = @" C:\Documents and Settings\Administrator\桌面 " ;
newdlg.Filter = " Execl files (*.xls)|*.xls " ;
newdlg.ShowDialog();
Stream myStream;
myStream = newdlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding( - 0 ));
string str = "" ;
try
{
for ( int i = 0 ; i < dataGridView1.ColumnCount; i ++ )
{
if (i > 0 )
str += " \t " ;
str += dataGridView1.Columns[i].HeaderText;
}
sw.WriteLine(str);
for ( int j = 0 ; j < dataGridView1.Rows.Count - 1 ; j ++ )
{
string contentstr = "" ;
for ( int k = 0 ; k < dataGridView1.Columns.Count; k ++ )
{
if (k > 0 )
{
contentstr += " \t " ;
}
contentstr += dataGridView1.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(contentstr);
}
sw.Close();
myStream.Close();
}
catch (Exception g)
{
MessageBox.Show(g.Message);
}
finally
{
sw.Close();
myStream.Close();
}
}
}
}
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.IO;
namespace download
{
public partial class Form1 : Form
{
public void con()
{
SqlConnection downcon = new SqlConnection();
downcon.ConnectionString = @" Initial Catalog=stuinfo;Data Source=(local);Integrated Security=SSPI; User Instance = false " ;
downcon.Open();
SqlCommand downcom = new SqlCommand( "" ,downcon);
downcom.CommandText = @" select * from stucard " ;
SqlDataAdapter downda = new SqlDataAdapter(downcom);
DataSet downset = new DataSet();
downda.Fill(downset, " stucard " );
dataGridView1.DataSource = downset.Tables[ " stucard " ];
downcon.Close();
}
public Form1()
{
InitializeComponent();
con();
}
private void button1_Click( object sender, EventArgs e)
{
SaveFileDialog newdlg = new SaveFileDialog();
newdlg.InitialDirectory = @" C:\Documents and Settings\Administrator\桌面 " ;
newdlg.Filter = " Execl files (*.xls)|*.xls " ;
newdlg.ShowDialog();
Stream myStream;
myStream = newdlg.OpenFile();
StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding( - 0 ));
string str = "" ;
try
{
for ( int i = 0 ; i < dataGridView1.ColumnCount; i ++ )
{
if (i > 0 )
str += " \t " ;
str += dataGridView1.Columns[i].HeaderText;
}
sw.WriteLine(str);
for ( int j = 0 ; j < dataGridView1.Rows.Count - 1 ; j ++ )
{
string contentstr = "" ;
for ( int k = 0 ; k < dataGridView1.Columns.Count; k ++ )
{
if (k > 0 )
{
contentstr += " \t " ;
}
contentstr += dataGridView1.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(contentstr);
}
sw.Close();
myStream.Close();
}
catch (Exception g)
{
MessageBox.Show(g.Message);
}
finally
{
sw.Close();
myStream.Close();
}
}
}
}
for (int i = 0; i < dataGridView1.ColumnCount; i++)
{
if (i > 0)
str += "\t";
str += dataGridView1.Columns[i].HeaderText;
}
上述代码是excel的列名,也是数据库的字段名称的生成。由于二者要对应,而且显示在excel的对应列上,这是\t是关键。
for (int j = 0; j < dataGridView1.Rows.Count-1; j++)
{
string contentstr = "";
for (int k = 0; k < dataGridView1.Columns.Count; k++)
{
if (k > 0)
{
contentstr += "\t";
}
contentstr += dataGridView1.Rows[j].Cells[k].Value.ToString();
}
sw.WriteLine(contentstr);
}
值得注意的一点是j的上限,也就是数据的行数,在mdf数据库文件中,最后一行是全NULL,如果上限是dataGridView1.Rows.Count,而不是dataGridView1.Rows.Count-1的话,编译器检测到全NULL的数据时,会提示错误:未将对象引用设置到对象的实例。