excel数据导入 数据库 菜鸟求指导
最近在学习操作excel,有没有相对好用简单些的方法>? 同事用的是HSSF来操作的,有没有好的代码>?贴上来参考下,新人看网上的有点头晕...谢谢了
[解决办法]
你可以用micro的Excel操作程序
public class ExcelHandle
{
#region 将成绩信息导入Excel中并设置Excel基本样式
/// <summary>
/// 将成绩信息导入Excel中并设置Excel基本样式
/// </summary>
public void GetResultDataToExcel(DataGridView dgvResult)
{
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
//设置Excel标题
excel.Caption = "学生成绩";
//设置Excel列名
excel.Cells[1, 1] = "学生姓名";
excel.Cells[1, 2] = "科目";
excel.Cells[1, 3] = "成绩";
excel.Cells[1, 4] = "考试时间";
//设置Excel字体加粗
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]).Font.Bold = true;
//设置Excel字体颜色
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]).Font.ColorIndex = 0;
//设置Excel边框样式
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]).Borders.LineStyle = XlLineStyle.xlContinuous;
//循环将DataGridView中的数据赋值到Excel中
for (int m = 0; m < dgvResult.Rows.Count; m++)
{
DataGridViewComboBoxCell dgvCbo = (DataGridViewComboBoxCell)dgvResult.Rows[m].Cells["StudentNo"];
excel.Cells[m + 2, 1] = dgvCbo.FormattedValue.ToString();//设置学生姓名
}
int i;
for (i = 0; i < dgvResult.Rows.Count; i++)
{
excel.Cells[i + 2, 2] = dgvResult.Rows[i].Cells["SubjectName"].Value.ToString();
excel.Cells[i + 2, 3] = dgvResult.Rows[i].Cells["StudentResult"].Value.ToString();
excel.Cells[i + 2, 4] = dgvResult.Rows[i].Cells["ExamDate"].Value.ToString();
}
//设置Excel水平对齐方式
excel.get_Range(excel.Cells[1, 1], excel.Cells[i + 2, 4]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
//显示当前窗口
excel.Visible = true;
}
#endregion
#region 将学生信息导入Excel中并设置Excel基本样式
/// <summary>
/// 将学生信息导入Excel中并设置Excel基本样式
/// </summary>
public void GetStudentInfoDataToExcel(DataGridView dgvStuInfor,int ype)
{
Microsoft.Office.Interop.Excel.Application excel =
new Microsoft.Office.Interop.Excel.Application();
excel.SheetsInNewWorkbook = 1;
excel.Workbooks.Add();
//设置Excel列名
excel.Cells[1, 1] = "学号";
excel.Cells[1, 2] = "密码";
excel.Cells[1, 3] = "姓名";
excel.Cells[1, 4] = "性别";
excel.Cells[1, 5] = "年级";
excel.Cells[1, 6] = "电话";
excel.Cells[1, 7] = "地址";
excel.Cells[1, 8] = "出生年月日";
excel.Cells[1, 9] = "邮箱";
excel.Cells[1, 10] = "身份证号";
//获取标题行的单元格，即Range
Range range = excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 10]);
//设字体加粗
range.Font.Bold = true;
//设置字体颜色
range.Font.ColorIndex = 0;
//设置背景颜色
range.Interior.ColorIndex = 15;
//设置边框样式
range.Borders.LineStyle = XlLineStyle.xlContinuous;
//循环将DataGridView中的数据赋值到Excel中
int i = 0, j = 0;
for (i = 0; i < dgvStuInfor.Rows.Count; i++)
{
for (j = 0; j < 3; j++)
{
excel.Cells[i + 2, j + 1] = dgvStuInfor.Rows[i].Cells[j].Value.ToString();
}
//设置性别
excel.Cells[i + 2, 4] =
dgvStuInfor.Rows[i].Cells["Gender"].Value.ToString() == "False" ? "男" : "女";
//设置显示的学生年级
DataGridViewComboBoxCell dgvCbo =
(DataGridViewComboBoxCell)dgvStuInfor.Rows[i].Cells["GradeId"];
excel.Cells[i + 2, 5] = dgvCbo.FormattedValue.ToString();
for (j = 5; j < 10; j++)
{
excel.Cells[i + 2, j + 1] = dgvStuInfor.Rows[i].Cells[j].Value.ToString();
}
}
//设置出生年月日的格式
excel.get_Range(excel.Cells[2, 8], excel.Cells[i + 2, 8]).NumberFormat = "yyyy-m-d";
//设置身份证号的格式
excel.get_Range(excel.Cells[2, 10], excel.Cells[i + 2, 10]).NumberFormatLocal = "0";
//设置Excel水平对齐方式
excel.get_Range(excel.Cells[1, 1], excel.Cells[i + 2, j + 2]).HorizontalAlignment
= XlHAlign.xlHAlignLeft;
//显示当前窗口
excel.Visible = true;
}
/// <summary>
/// 将学生信息导入Excel中-使用Excel 模板
/// </summary>
public void GetStudentInfoDataToExcel(DataGridView dgvStuInfor)
{
//创建Excel对象
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
string fileName = Environment.CurrentDirectory + "/template/stuResult.xls";
excel.Application.Workbooks.Open(fileName);
//循环将DataGridView中的数据赋值到Excel中
int i = 0, j = 0;
for (i = 0; i < dgvStuInfor.Rows.Count; i++)
{
for (j = 0; j < 3; j++)
{
excel.Cells[i + 3, j + 1] = dgvStuInfor.Rows[i].Cells[j].Value.ToString();
}
//设置性别
excel.Cells[i + 3, 4] =
dgvStuInfor.Rows[i].Cells["Gender"].Value.ToString() == "False" ? "男" : "女";
//设置显示的学生年级
DataGridViewComboBoxCell dgvCbo =
(DataGridViewComboBoxCell)dgvStuInfor.Rows[i].Cells["GradeId"];
excel.Cells[i + 3, 5] = dgvCbo.FormattedValue.ToString();
for (j = 5; j < 10; j++)
{
excel.Cells[i + 3, j + 1] = dgvStuInfor.Rows[i].Cells[j].Value.ToString();
}
}
string newFileName = Environment.CurrentDirectory + "/test.xls";
//保存当前活动的WorkBook
excel.ActiveWorkbook.SaveAs(newFileName, XlFileFormat.xlWorkbookNormal);
//关闭当前活动的WorkBook
excel.ActiveWorkbook.Close();
//退出Excel应用程序
excel.Quit();
}
#endregion
#region 从Excel中导入学生信息
/// <summary>
/// 从Excel中导出学生信息
/// </summary>
public System.Data.DataTable GetStudentInfoDataFromExcel(string filePath, string sheetName, bool useADONET)
{
string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";"
+ "Extended Properties='Excel 8.0;HDR=YES;IMEX=1'";
string strExcel = string.Format("select * from [{0}$] where [姓名] is not null", sheetName);
System.Data.DataTable dt = new System.Data.DataTable();
using (OleDbConnection conn = new OleDbConnection(strConn))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, conn);
adapter.Fill(dt);
}
//处理性别列
foreach (DataRow dr in dt.Rows)
{
dr[2] = dr[2].ToString() == "男" ? "1" : "0";
}
return dt;
}
/// <summary>
/// 从Excel 导入数据到DataTable
/// </summary>
/// <param name="filePath">h带完整路径的Excel文件名</param>
/// <param name="sheetName">工作薄名字</param>
/// <returns>DataTable</returns>
public System.Data.DataTable GetStudentInfoDataFromExcel(string filePath, string sheetName)
{
System.Data.DataTable dt = new System.Data.DataTable();
//添加9个列
for (int i = 0; i < 9; i++)
{
dt.Columns.Add(new DataColumn());
}
Microsoft.Office.Interop.Excel.Application excel =
new Microsoft.Office.Interop.Excel.Application();
excel.Workbooks.Open(filePath);//打开Excel 文件
Worksheet sheet = null;
foreach (Worksheet wsheet in excel.ActiveWorkbook.Sheets)
{
if (wsheet.Name == sheetName)
{
sheet = wsheet;
break;
}
}
//读取单元格数据
if (sheet != null)
{
int row = 2;//当前行号
while (true)
{
//如果碰到姓名为空的行，则停止读取数据
Range rName = sheet.Cells[row, 2] as Range;
if (rName.Text.ToString().Trim().Length == 0)
{
break;
}
DataRow dr = dt.NewRow();
for (int i = 0; i < 9; i++)
{
//将行中的每列赋值
Range rContent = sheet.Cells[row, i + 1] as Range;
dr[i] = rContent.Text;
if (i == 2)//性别列处理为布尔值
{
dr[i] = dr[i].ToString() == "男" ? "1" : "0";
}
}
dt.Rows.Add(dr);
row += 1;
}
}
excel.ActiveWorkbook.Close();
excel.Quit();
return dt;
}
#endregion
}
[解决办法]
晕 这个问题 你在这里听人说 也说不懂啊 有专门讲这个的excel数据导入的视频啊. 要的话我发给你啊. ！
