1. 将list中的数据到出到Excel中：
//Excel写入 【导出】
private void button1_Click(object sender, EventArgs e)
{
List<Person> list=new List<Person>()
{
new Person(){Name="张珊",Age=19,Email="zs@163.com"},
new Person(){Name="张珊",Age=19,Email="zs@163.com"},
new Person(){Name="张珊",Age=19,Email="zs@163.com"},
new Person(){Name="张珊",Age=19,Email="zs@163.com"},
new Person(){Name="张珊",Age=19,Email="zs@163.com"}
};
//Excel
//1. 创建workbook
IWorkbook wk=new HSSFWorkbook();
//2. 创建工作表
ISheet sheet = wk.CreateSheet("Person");
//向工作表中写入行
for (int i = 0; i < list.Count; i++)
{
//对于List集合中的没一条数据，创建一行
IRow row = sheet.CreateRow(i);
//在行中创建单元格
row.CreateCell(0).SetCellValue(list[i].Name);
row.CreateCell(1).SetCellValue(list[i].Age);
row.CreateCell(2).SetCellValue(list[i].Email);
}
using (FileStream fsWrite = File.OpenWrite("person.xls"))
{
wk.Write(fsWrite);
}
MessageBox.Show("ok");
}
2. 将Excel中的数据输出到"输出面板"中：
//读取Excle文件【读取】
private void button2_Click(object sender, EventArgs e)
{
using (FileStream fs = File.OpenRead("ReadExcel.xls"))
{
IWorkbook wb = new HSSFWorkbook(fs);
for (int i = 0; i < wb.NumberOfSheets; i++)
{
ISheet st = wb.GetSheetAt(i);
Console.WriteLine("==============={0}============", st.SheetName);
for (int s = 0; s <= st.LastRowNum; s++)
{
IRow rw = st.GetRow(s);
if (rw != null)
{
for (int r = 0; r < rw.LastCellNum; r++)
{
ICell cl = rw.GetCell(r);
if (cl != null)//当单元格中没有任何数据，并且没有格式的时候（未使用的时候）
{
Console.Write(cl.ToString() + " ");
}
}
Console.WriteLine();
}
}
}
}
}
3. 把T_Seats中的数据导入到Excel：
//把T_Seats中的数据导入到Excel 【T_Seats读取到Excel】
private void button3_Click(object sender, EventArgs e)
{
//1. 读取
string sql = "select * from T_Seats";
using (SqlDataReader reader = SqlHelper.ExecuteDataReader(sql, CommandType.Text))
{
if (reader.HasRows)
{
//创建workbook
IWorkbook wk=new HSSFWorkbook();
//创建Sheet
ISheet sheet = wk.CreateSheet("T_Seats");
int rowIndex = 0;
#region 读取并创建每一行
//读取每一条数据
while (reader.Read())
{
//CC_AutoId, CC_LoginId, CC_LoginPassword, CC_UserName, CC_ErrorTimes, CC_LockDateTime, CC_TestInt
int autoId = reader.GetInt32(0);
string uid = reader.GetString(1);
string pwd = reader.GetString(2);
string name = reader.GetString(3);
int errorTimes = reader.GetInt32(4);
DateTime? lockDate = reader.IsDBNull(5) ? null : (DateTime?)reader.GetDateTime(5);
int? testInt = reader.IsDBNull(6) ? null : (int?)reader.GetInt32(6);
IRow row = sheet.CreateRow(rowIndex);
rowIndex++;
//向行中创建单元格
row.CreateCell(0).SetCellValue(autoId);
row.CreateCell(1).SetCellValue(uid);
row.CreateCell(2).SetCellValue(pwd);
row.CreateCell(3).SetCellValue(name);
row.CreateCell(4).SetCellValue(errorTimes);
//对于数据库中的空值，向单元格中插入空内容。
ICell cellLockDate = row.CreateCell(5);
if (lockDate == null)
{
//设置单元格的数据类型为Blank，表示空单元格
cellLockDate.SetCellType(CellType.BLANK);
}
else
{
cellLockDate.SetCellValue((DateTime)lockDate);
//创建一个单元格格式对象
ICellStyle cellStyle = wk.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("m/d/yy h:mm");
//设置当前日期这个单元格的是cellStyle属性
cellLockDate.CellStyle = cellStyle;
}
ICell cellTesInt = row.CreateCell(6);
if (testInt == null)
{
cellTesInt.SetCellType(CellType.BLANK);
}
else
{
cellTesInt.SetCellValue((int)testInt);
}
}
#endregion
//将Excel写入文件
using (FileStream fsWrite = File.OpenWrite("tseats.xls"))
{
wk.Write(fsWrite);
}
}
}
MessageBox.Show("操作完毕！");
}
4. 把Excel中的内容导入到数据库表T_Seats
//把Excel中的内容导入到数据库表T_Seats【Excel到T_Seats】
private void button4_Click(object sender, EventArgs e)
{
using (FileStream fsRead = File.OpenRead("tseats.xls"))
{
//1. 读取Excel
IWorkbook wk = new HSSFWorkbook(fsRead);
ISheet sheet = wk.GetSheetAt(0);
string sql_insert = "insert into T_Seats values(@uid,@pwd,@uname,@errorTimes,@lockDate,@testint)";
//读取sheet中的每一行
for (int r = 0; r <= sheet.LastRowNum; r++)
{
//读取每行的每个单元格
IRow row = sheet.GetRow(r);
//读取除了第一列的其他列
string loginId= row.GetCell(1).StringCellValue;
string password = row.GetCell(2).StringCellValue;
string username = row.GetCell(3).StringCellValue;
int errorTimes = (int) row.GetCell(4).NumericCellValue;
double? lockDate = null;
ICell cellLockDate = row.GetCell(5);
if (cellLockDate != null && cellLockDate.CellType != CellType.BLANK)
{
lockDate = row.GetCell(5).NumericCellValue;
}
else
{
//lockDate=null;
}
int? testInt = null;
ICell cellTestInt = row.GetCell(6);
if (cellTestInt != null && cellTestInt.CellType != CellType.BLANK)
{
testInt = (int)row.GetCell(6).NumericCellValue;
}
else
{
//testInt = null;
}
SqlParameter[] pms = new SqlParameter[]
{
new SqlParameter("@uid",loginId),
new SqlParameter("@pwd",password),
new SqlParameter("@uname",username),
new SqlParameter("errorTimes",errorTimes),
new SqlParameter("lockDate",lockDate==null?DBNull.Value:(object)DateTime.FromOADate((double)lockDate)),
new SqlParameter("testint",testInt==null?DBNull.Value:(object)testInt)
};
//2. 向表T_Seats进行insert语句
//执行插入操作
SqlHelper.ExcuteNonQuery(sql_insert, CommandType.Text, pms);
}
}
MessageBox.Show("ok");
}
}
internal class Person
{
public string Name { get; set; }
public int Age { get; set; }
public string Email { get; set; }
}