返回目录：excel表格制作
1.先去导入dll库：Microsoft.Office.Interop.Excel.dl
2.然后再去写代码：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using Microsoft.Office.Interop;
using Microsoft.Office.Interop.Excel;
using System.Windows.Forms;
using Excel = Microsoft.Office.Interop.Excel;
public class ExprotToExcel
{
public void DataToExcel(DataGridView dgv,ToolStripProgressBar tempProgressBar,ToolStripStatusLabel toolstrip)
{
if (dgv.Rows.Count == 0)
{
MessageBox.Show("无数据e68a84e799bee5baa6333"); return;
}
MessageBox.Show("开始生成要导出的数据", "导出提示", MessageBoxButtons.OK, MessageBoxIcon.Information);
Excel.Application excel = new Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Visible = false;
for (int i = 0; i < dgv.ColumnCount; i++)
excel.Cells[1, i + 1] = dgv.Columns[i].HeaderText;
tempProgressBar.Visible = true;
tempProgressBar.Minimum = 1;
tempProgressBar.Maximum = dgv.RowCount;
tempProgressBar.Step = 1;
toolstrip.Visible = true;
for (int i = 0; i < dgv.RowCount; i++)
{
for (int j = 0; j < dgv.ColumnCount; j++)
{
if (dgv[j, i].ValueType == typeof(string))
{
excel.Cells[i + 2, j + 1] = "'" + dgv[j, i].Value.ToString();
}
else
{
excel.Cells[i + 2, j + 1] = dgv[j, i].Value.ToString();
}
}
toolstrip.Text = "|| 状态：正在生成第 "+i+"/"+dgv.RowCount+" 个";
tempProgressBar.Value = i + 1;
}
toolstrip.Text = "|| 状态：生成成功！";
MessageBox.Show("生成成功，请保存。","生成提示",MessageBoxButtons.OK,MessageBoxIcon.Information);
excel.Visible = true;
}
}
3.打开后，效果如下：
public DataSet ReadExcel_ds(string FilePath) { string subfile = FilePath.Substring(FilePath.LastIndexOf(".") + 1); DataSet ds = new DataSet(); string strCon = ""; if (subfile.ToUpper() == "XLS") { strCon = "Provider=Microsoft.Jet.OLEDB.4.0;Extended Properties=Excel 8.0;data source=" + FilePath; } if (subfile.ToUpper() == "XLSX")//excel2007读取 { strCon = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + FilePath + ";Extended Properties=\"Excel 12.0;HDR=YES\""; } System.Data.OleDb.OleDbConnection Conn = new System.Data.OleDb.OleDbConnection(strCon); try { Conn.Open(); System.Data.DataTable sTable = Conn.GetOleDbSchemaTable(System.Data.OleDb.OleDbSchemaGuid.Tables, null);//新添加7a686964616fe58685e5aeb9337 2010-10-14 string tableName = sTable.Rows[0][2].ToString().Trim();//新添加 2010-10-14 获取工作表名称 string strCom = "SELECT * FROM [" + tableName + "] "; System.Data.OleDb.OleDbDataAdapter myCommand = new System.Data.OleDb.OleDbDataAdapter(strCom, Conn); myCommand.Fill(ds, "[" + tableName + "]"); } catch (Exception Ex) { MessageBox.Show(Ex.Message); } finally { Conn.Close(); } return ds; }
using System; using System.Reflection; // 引用这个来才源能使用Missing字段百 using Excel=Microsoft.Office.Interop.Excel; namespace CExcel1 { class Class1 { [STAThread] static void Main(string[] args) { //创建Application对象度 Excel.Appl...
参考代码如下
/// <summary>
/// Excel数据导入方法
/// 作者:lhxhappy
/// </summary>
/// <param name="filePath"></param>
/// <param name="dgv"></param>
public void EcxelToDataGridView(string filePath,DataGridView dgv)
{
//根据7a64e78988e69d83365路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + filePath + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "";
OleDbDataAdapter myCommand = null;
DataSet ds = null;
strExcel = "select * from [sheet1$]";
myCommand = new OleDbDataAdapter(strExcel, strConn);
ds = new DataSet();
myCommand.Fill(ds, "table1");
//根据DataGridView的列构造一个新的DataTable
DataTable tb = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible && dgvc.CellType != typeof(DataGridViewCheckBoxCell))
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
//dc.DataType = dgvc.ValueType;//若需要限制导入时的数据类型则取消注释，前提是DataGridView必须先绑定一个数据源那怕是空的DataTable
tb.Columns.Add(dc);
}
}
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = tb.NewRow();
foreach (DataColumn dc in tb.Columns)
{
dr[dc] = excelRow[i];
i++;
}
tb.Rows.Add(dr);
}
//在DataGridView中显示导入的数据
dgv.DataSource = tb;
}