首页 > 代码库 > Excel表格的导入导出
Excel表格的导入导出
excel.Application app = new excel.Application();
app.SheetsInNewWorkbook = 2;
app.Workbooks.Add();
Worksheet sheet1 = (Worksheet)app.ActiveWorkbook.Worksheets[1];
sheet1.Name = "Hello Word";
sheet1.Cells[1, 1] = "Welcome To Excel";
Worksheet sheet2 = (Worksheet)app.ActiveWorkbook.Worksheets[2];
sheet2.Name = "Hello JiaHua";
sheet2.Cells[1, 1] = "Welcome To JiaHua";
app.ActiveWorkbook.SaveAs("e:\\test.xls");
app.ActiveWorkbook.Close();
app.Quit();
导出到Excel
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.SheetsInNewWorkbook = 1;
app.Workbooks.Add();
//生成列头
for (int i = 0; i < dataGridView1.Columns.Count; i++)
{
app.Cells[1, i + 1] = dataGridView1.Columns[i].HeaderText;
}
//生成内容
for (int i = 0; i < dataGridView1.Rows.Count; i++)
{
for (int j = 0; j < dataGridView1.Columns.Count; j++)
{
app.Cells[i + 2, j + 1] = dataGridView1.Rows[i].Cells[j].Value;
}
}
//设置列头的样式
Range range = app.Range[app.Cells[1, 1], app.Cells[1, dataGridView1.Columns.Count]];
//Range range = app.get_Range(app.Cells[1, 1], app.Cells[1, dataGridView1.Columns.Count]);
range.Font.Bold = true;
range.Font.Color = Color.Red;
range.Interior.ColorIndex = 15;
range.Borders.LineStyle = XlLineStyle.xlContinuous;
if (saveFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filename = saveFileDialog1.FileName;
app.ActiveWorkbook.SaveAs(filename);
app.ActiveWorkbook.Close();
app.Quit();
}
//导入到DataGridView
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filename = openFileDialog1.FileName;
Microsoft.Office.Interop.Excel.Application app = new Microsoft.Office.Interop.Excel.Application();
app.Workbooks.Open(filename);
if (app.ActiveWorkbook.Worksheets[1] != null)
{
Worksheet sheet = app.ActiveWorkbook.Worksheets[1];
int row = 2;
//生成DataTable
System.Data.DataTable dt = new System.Data.DataTable();
dt.Columns.Add("StudentNo", typeof(int));
dt.Columns.Add("StudentName", typeof(string));
dt.Columns.Add("GradeName", typeof(string));
dt.Columns.Add("Sex", typeof(string));
dt.Columns.Add("Phone", typeof(string));
dt.Columns.Add("Address", typeof(string));
while (true)
{
DataRow newrow = dt.NewRow();
Range range1 = sheet.Cells[row, 1];
if (range1.Text == "")
{
break;
}
for (int i = 0; i < 6; i++)
{
Range range2 = sheet.Cells[row, i + 1];
if (range2.Text == "")
{
break;
}
else
{
newrow[i] = range2.Text;
}
}
dt.Rows.Add(newrow);
row++;
}
app.ActiveWorkbook.Close();
app.Quit();
dataGridView1.DataSource = dt;
}
}
//只能操作规则的数据表(通过查询的方式)
if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK)
{
string filename = openFileDialog1.FileName;
string conString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filename + ";Extended Properties=‘Excel 8.0;HDR=YES;IMEX=1‘";
OleDbConnection con = new OleDbConnection(conString);
string sql = "select * from [Sheet1$]";
OleDbDataAdapter da = new OleDbDataAdapter(sql, con);
System.Data.DataTable dt = new System.Data.DataTable();
da.Fill(dt);
dataGridView1.DataSource = dt;
}
本文出自 “张志鹏” 博客，请务必保留此出处http://zhangzhipeng.blog.51cto.com/9115459/1571061
Excel表格的导入导出