C#导入，导出Excel 数据
新建一张数据表producttable
web服务器端代码
[WebMethod]
public DataSet ExportProduct()
{
try{
//数据库连接字符串
string con = @"Data Source=TALENT;Initial Catalog=MyDb;Integrated Security=True";
SqlConnection scon = new SqlConnection(con);
//打开数据库连接
scon.Open();
//数据库访问指令SQL
string select = "select * from producttable";
SqlDataAdapter sda = new SqlDataAdapter(select, scon);
//数据缓冲集
DataSet ds = new DataSet();
//读取数据并填充到缓冲区
sda.Fill(ds, "product");
scon.Close();
//返回缓冲区中的数据
return ds;
}
catch(Exception ex)
{
return null;
}
}
新建另一个项目，添加web引用
要使用Excel，需加的命名空间
using Microsoft.Office.Interop.Excel;
using System.Reflection;
using System.IO;
需添加引用
新建个窗体
三个按钮单击事件的代码
DataSet ds = new DataSet();
第一个按钮功能，从web服务器端返回的DataSet数据，绑定到dataGridView控件上显示
private void button1_Click(object sender, EventArgs e)
{
try
{
myService1.Service service = new ExportExcel.myService1.Service();
ds = service.ExportProduct();
if (ds != null)
{
dataGridView1.DataSource = ds.Tables[0];
}
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
第二个按钮功能，把DataSet数据导出到Excel表单中
private void button2_Click(object sender, EventArgs e)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
excel.Visible = true;
workbook = excel.Workbooks.Add(XlWBATemplate.xlWBATWorksheet);
worksheet = (Worksheet)workbook.Worksheets[1];
if (ds.Tables[0].Rows.Count > 0)
{
for(int j=0;j<ds.Tables[0].Rows.Count;j++)
for (int i = 0; i < ds.Tables[0].Rows.Count; i++)
{
worksheet.Cells[j + 1, i + 1] = ds.Tables[0].Rows[j][i].ToString();
}
}
}
第三个按钮功能，从Excel表单导入数据到dataGridView控件上显示
private void button3_Click(object sender, EventArgs e)
{
OpenFileDialog ofd = new OpenFileDialog();
ofd.Filter = "Excel Files|*.xlsx";
if (ofd.ShowDialog() == DialogResult.OK)
{
string filename = ofd.FileName;
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
Microsoft.Office.Interop.Excel.Workbook workbook;
Microsoft.Office.Interop.Excel.Worksheet worksheet;
object oMissing = System.Reflection.Missing.Value;
workbook = excel.Workbooks.Open(filename, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing, oMissing);
worksheet = (Worksheet)workbook.Worksheets[1];
int rowCount = worksheet.UsedRange.Rows.Count;
int colCount = worksheet.UsedRange.Columns.Count;
Microsoft.Office.Interop.Excel.Range range1;
System.Data.DataTable dt = new System.Data.DataTable();
for (int i = 0; i < colCount; i++)
{
range1 = worksheet.get_Range(worksheet.Cells[1, i + 1], worksheet.Cells[1, i + 1]);
dt.Columns.Add(range1.Value2.ToString());
}
for (int j = 1; j < rowCount; j++)
{
DataRow dr = dt.NewRow();
for (int i = 0; i < colCount; i++)
{
range1 = worksheet.get_Range(worksheet.Cells[j + 1, i + 1], worksheet.Cells[j + 1, i + 1]);
dr[i] = range1.Value2.ToString();
}
dt.Rows.Add(dr);
}
dataGridView1.DataSource = dt;
excel.Quit();
}
}