作者：乔山办公网日期：
返回目录：excel表格制作
直接在工作表的首行追加2行就可e799bee5baa6e79fa5e98193e78988e69d83364以了啊，你也可以直接选择我下面的函数
Public Function Export_Excel(ByVal dgv As DataGridView) As Boolean
Try
If dgv.Rows.Count <= 0 Then '如果没有记录就退出
MessageBox.Show("没有记录可以导出", "没有可以导出的项目", MessageBoxButtons.OK, MessageBoxIcon.Information)
Return False
End If
Dim ExcelAppliaction As Object
Dim Workbook As Object
ExcelAppliaction = CreateObject("Excel.Application")
Workbook = ExcelAppliaction.workbooks.add()
'For tt = 1 To 30 '30列都是文本格式------这里根据需要是否启用
' Workbook.worksheets(1).Columns(tt).NumberFormatLocal = "@"
'Next
Dim i As Integer = 0, row As Integer = 0, col As Integer = 0
For i = 1 To dgv.Columns.Count '在第三行开始显示表头
Workbook.worksheets(1).cells(3, i) = dgv.Columns(i - 1).HeaderCell.Value '这里的3表示从第3行开始
Next
Dim str(dgv.Rows.Count - 1, dgv.Columns.Count - 1) '定义一个二维数组---目的的快速导入(对于10万行以上效率惊人)
For row = 1 To dgv.Rows.Count '循环行
For col = 1 To dgv.Columns.Count '循环列
str(row - 1, col - 1) = dgv.Item(col - 1, row - 1).Value
Next
Next
Workbook.worksheets(1).range("A4").Resize(dgv.Rows.Count, dgv.Columns.Count).Value = str
Workbook.worksheets(1).Cells.EntireColumn.AutoFit() '自动列宽
ExcelAppliaction.visible = True '显示Excel
Workbook = Nothing
ExcelAppliaction = Nothing
Return True
Catch ex As Exception
MessageBox.Show(ex.Message, "出错了", MessageBoxButtons.OK, MessageBoxIcon.Error)
Return False
End Try
End Function
借助于SQL。代码如下：
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Diagnostics;
using System.IO;
//using Microsoft.Office.Interop.Excel;
using System.Data.OleDb;
using System.ComponentModel;
using System.Data;
using System.Drawing;
namespace ClothingSales_0._5
{
public class Import
{
//private string fileName;
/// <summary>
/// 将DataGridView中数据导入到Excel
/// </summary>
/// <param name="dgv">DataGridView的名称</param>
public bool getExcel(DataGridView dgv,Label lbl)
{
bool fflag = true;
OpenFileDialog open = new OpenFileDialog();
open.Title = "请选择要导入的Excel文件";
open.Filter = "Excel文件(*.xls)|*.xls";
if (open.ShowDialog() == DialogResult.OK)
{
string fileName = open.FileName;
//根据路径打开一个Excel文件并将数据填充到DataSet中
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source = " + fileName + ";Extended Properties ='Excel 8.0;HDR=NO;IMEX=1'";//导入时包含Excel中的第一行数据，并且将数字和字符混合的单元格视为文本进行导入
OleDbConnection conn = new OleDbConnection(strConn);
conn.Open();
string strExcel = "select * from [sheet1$]";
OleDbDataAdapter comm = new OleDbDataAdapter(strExcel, strConn);
DataSet ds = new DataSet();
try
{
comm.Fill(ds, "table1");
}
catch
{
MessageBox.Show("错误信息：009", "错误");
}
comm.Fill(ds, "table1");
//根据DataGridView的列构造一个新的DataTable
DataTable dt = new DataTable();
DataTable dt2 = new DataTable();
foreach (DataGridViewColumn dgvc in dgv.Columns)
{
if (dgvc.Visible)
{
DataColumn dc = new DataColumn();
dc.ColumnName = dgvc.DataPropertyName;
dt.Columns.Add(dc);
DataColumn dc2 = new DataColumn();
dc2.ColumnName = dgvc.DataPropertyName;
dt2.Columns.Add(dc2);
if (dgvc.CellType == typeof(DataGridViewCheckBoxCell))
{
dc2.DataType = Type.GetType("System.Boolean");
}
}
}
//根据Excel的行逐一对上面构造的DataTable的列进行赋值
foreach (DataRow excelRow in ds.Tables[0].Rows)
{
int i = 0;
DataRow dr = dt.NewRow();
foreach (DataColumn dc in dt.Columns)
{
dr[dc] = excelRow[i];
i++;
}
dt.Rows.Add(dr);
}
//判断Excel的格式是否正确
int n = 0;
for (int j = 0; j < dt.Columns.Count; j++)
{
bool flag = false;
for (int k = n; k < dgv.ColumnCount; k++)
{
if (dgv.Columns[k].Visible) //隐藏的列
{
if (dgv.Columns[k].HeaderText.Trim().ToString() == dt.Rows[0][j].ToString())
{
if (dgv.Columns[k].CellType == typeof(DataGridViewCheckBoxCell))
{
//list.Add(j);
//num++;
}
flag = true;
n = k + 1;
break;
}
}
}
if (flag == false)
{
MessageBox.Show("导入的Excel的格式错7a64e58685e5aeb9361误", "提示");
fflag = false;
return fflag;
}
}
//删除多余的行
int rowCount = (dt.Rows.Count) / 2;
for (int i = 0; i <= rowCount; i++)
{
dt.Rows.RemoveAt(0);
}
//处理Boolean类型的数据
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt2.NewRow();
for (int j = 0; j < dt.Columns.Count; j++)
{
try
{
dr[j] = dt.Rows[i][j];
}
catch
{
dr[j] = false;
}
}
dt2.Rows.Add(dr);
}
//导入到dataGridView
dgv.DataSource = dt2;
lbl.Text = dgv.RowCount.ToString();
}
else
{
fflag = false;
}
return fflag;
}
}
}
Public Shared Function ExportExceldr(ByVal Drv As DataTable, ByVal ECode As String) As Boolean
Dim BOO As Boolean = False '定义执行结果状态
If Drv Is Nothing OrElse Drv.Rows.Count = 0 Then '没有数据直接返回
Return BOO
End If
Dim SaveFileDialog1 As New SaveFileDialog '实例化文件保存提示框
SaveFileDialog1.Filter = "文件(*.xls)|*.xls" '设置保存文件类型
SaveFileDialog1.FileName = ECode '默认文件名
SaveFileDialog1.FilterIndex = 1 '默认筛选
SaveFileDialog1.InitialDirectory = Application.StartupPath '默认当前路径
If SaveFileDialog1.ShowDialog() = Windows.Forms.DialogResult.OK Then '用户点了确认
Try
Dim m_image As String = SaveFileDialog1.FileName '保存Excel路径和文件名称
Dim xlApp As Excel.Application = CreateObject("Excel.Application") '定义Excel对象
Dim xlBook As Excel.Workbook = xlApp.Workbooks.Add() '新增工作簿
Dim sheet As Excel.Worksheet = xlBook.Worksheets(1) '定义工作表
Dim col As Integer = 0 '定义列
Dim row As Integer = 0 '定义行
Dim satData(,) As Object = New Object(Drv.Rows.Count, col) {} '定义二维数组给工作表赋值
For Each dl As DataColumn In Drv.Columns '按列开始循环赋值
row = 0 '行每次从0开始
ReDim Preserve satData(Drv.Rows.Count, col) '重新定义数据
satData(row, col) = dl.ColumnName '赋值列标题
For Each dr As DataRow In Drv.Rows '按行循环
row += 1 '下一行
satData(row, col) = dr(dl.ColumnName) '赋值表内容
Next
col += 1 '下一列
Next
row += 1 '再增加一行，因为包含标题行
sheet.Range(sheet.Cells(1, 1), sheet.Cells(row, col)).Value2 = satData '数组赋值到工作表区域
sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, col)).Font.Bold = True '标题行字体加粗
With sheet.Range(sheet.Cells(1, 1), sheet.Cells(1, col))
.Interior.ColorIndex = 34 '标题行加颜色
.Borders.LineStyle = 1 '加边框
.HorizontalAlignment = 3 '水平居中
.Font.Size = 10 '字体大小
.EntireColumn.AutoFit() '自动调整列宽
End With
sheet.Cells(2, 2).Activate() '激活单元格
sheet.Cells(2, 2).Application.ActiveWindow.FreezePanes = True '固定窗口
If xlBook.Sheets.Count > 1 Then
For i = xlBook.Sheets.Count To 2 Step -1
xlApp.DisplayAlerts = False
CType(xlBook.Worksheets(i), Excel.Worksheet).Delete() '删除多余工作表
Next
End If
xlBook.SaveAs(Filename:=m_image) '保存工作簿
xlApp.Visible = True '可以显示出来了
xlBook = Nothing '释放e69da5e6ba90e799bee5baa6339xlBook
xlApp = Nothing '释放xlApp对象
GC.Collect() '垃圾回收
Catch ex As Exception
MessageBox.Show(ex.ToString)
BOO = True
End Try
GC.Collect()
End If
Return BOO
End Function
这叫导入吗？
直接连接读取不就行了