62,046
社区成员
发帖
与我相关
我的任务
分享
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 MySchool.Models;
using System.Data.OleDb;
using System.Data;
namespace MySchool.Excel
{
public class ExcelHandle
{
#region 将员工信息导入Excel中并设置Excel基本样式
/// <summary>
/// 将员工信息导入Excel中并设置Excel基本样式
/// </summary>
public void GetResultDataToExcel(DataGridView dgvResult)
{
Microsoft.Office.Interop.Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
excel.Application.Workbooks.Add(true);
excel.Caption = "XXXX";
//设置Excel列名
excel.Cells[1, 1] = "姓名";
excel.Cells[1, 2] = "名称";
excel.Cells[1, 3] = "result";
excel.Cells[1, 4] = "时间";
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]).Font.Bold = true;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]).Font.ColorIndex = 0;
excel.get_Range(excel.Cells[1, 1], excel.Cells[1, 4]).Borders.LineStyle = XlLineStyle.xlContinuous;
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["Result"].Value.ToString();
excel.Cells[i + 2, 4] = dgvResult.Rows[i].Cells["Date"].Value.ToString();
}
excel.get_Range(excel.Cells[1, 1], excel.Cells[i + 2, 4]).HorizontalAlignment = XlHAlign.xlHAlignLeft;
excel.Visible = true;
}
#endregion
/// <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 = 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;
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.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);
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
}
}