数据库和Excel

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using NPOI.HSSF.UserModel;
using NPOI.HPSF;
using NPOI.POIFS.FileSystem;
using System.IO;
using NPOI.SS.UserModel;
using System.Xml;
using System.Reflection;
using System.Configuration;

namespace ConsoleApp1
{
class Program
{
public static object[] getProperties<T>(T t,int Count)
{
object[] tStr = new object[Count];
if (t == null)
{
return tStr;
}
PropertyInfo[] properties = t.GetType().GetProperties(BindingFlags.Instance | BindingFlags.Public);

if (properties.Length <= 0)
{
return tStr;
}
else
{
tStr[0] = properties.Length;
}
int i = 1;
foreach (PropertyInfo item in properties)
{
string name = item.Name;
object value = item.GetValue(t, null);

if (item.PropertyType.IsValueType || item.PropertyType.Name.StartsWith("String"))
{
tStr[0] += "," + name;
tStr[i] = value;
i++;
}
else
{
getProperties(value,Count);
}
}
return tStr;
}
static void Main(string[] args)
{
#region 自学
////创建Workbook和Sheet
//HSSFWorkbook hssf = new HSSFWorkbook();
//ISheet sheet1 = hssf.CreateSheet("Sheet1"); //至少创建一个Sheet
////ISheet sheet2 = hssf.CreateSheet("Sheet2");
////ISheet sheet3 = hssf.CreateSheet("Sheet3");

////行在Excel里是从1开始的，但是NPOI内部是从0开始的；列在Excel里面是用字母表示的，而NPOI中也是用从0开始的数字表示的
//#region 创建单元格（标准）
////HSSFRow row1 = (HSSFRow)sheet1.CreateRow(0);
////row1.CreateCell(0); //创建第一行第一列 A1
////row1.CreateCell(1); //创建第一行第二列 B1
////row1.GetRow(0).GetCell(0).SetCellValue("A1"); //为第一行第一列赋值“A1”
//#endregion

//#region 创建单元格（简写）
//IRow row0 = sheet1.CreateRow(0); //创建第一行
// row0.CreateCell(0).SetCellValue("A1"); //创建第一行第一列，并赋值“A1”
// row0.CreateCell(1).SetCellValue("B1"); //当该行已被创建，创建列时，应用GetRow来获取，重建会覆盖之前列的数据
// IRow row1 = sheet1.CreateRow(1); //创建第二行
// row1.CreateCell(0).SetCellValue("A2");
// row1.CreateCell(1).SetCellValue("B2");
//#endregion

////创建DocumentSummaryInformation和SummaryInformation
//DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
////设置属性
//dsi.Company = "公司";
//SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
//si.Subject = "主题";

////把创建好的对象赋给Workbook
//hssf.DocumentSummaryInformation = dsi;
//hssf.SummaryInformation = si;

////把Workbook通过FileStream写入文件
//FileStream file = new FileStream(@"First.xls", FileMode.Create);
//hssf.Write(file);

////关闭文档流
//file.Close();
#endregion
//数据库导出到Excel
try
{
HSSFWorkbook hssf = new HSSFWorkbook();
ISheet sheet1 = hssf.CreateSheet("Sheet1");
Class1 cla = new Class1();
List<OrderAddr> list = cla.GetAll();

XmlDocument doc = new XmlDocument();
doc.Load(@"XMLFile1.xml"); //加载Xml文件
XmlNode node = doc.SelectSingleNode("SetCount/add");
int Count = int.Parse(node.InnerText);

//配置文件获取
//int Count = int.Parse(ConfigurationManager.AppSettings["pageNum"]);

int AllNum = cla.GetNum(); //获取所有数据的数量
int pageNum = 0; //获取分批处理次数
if ((AllNum % Count) == 0)
{
pageNum = AllNum / Count;
}
else
{
pageNum = (AllNum / Count) + 1;
}

int i = 0;

for (int f = 0; f < pageNum; f++) //分批导出数据
{
foreach (var item in list.Skip(f*Count).Take(Count))
{
IRow row1 = sheet1.CreateRow(i + 1);
object[] tStr = getProperties(item,Count);
object[] title = tStr[0].ToString().Split(',');
if (i == 0)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < int.Parse(title[0].ToString()); j++)
{
row.CreateCell(j).SetCellValue(title[j + 1].ToString());
row1.CreateCell(j).SetCellValue(tStr[j + 1].ToString());
}
}
else
{
for (int j = 0; j < int.Parse(title[0].ToString()); j++)
{
row1.CreateCell(j).SetCellValue(tStr[j + 1].ToString());
}
}
i++;
}
}

//cell.SetCellFormula("$A1+$B1"); //用函数赋值
//HSSFFormulaEvaluator formula = new HSSFFormulaEvaluator(hssf);
//数据 = formula.EvaluateInCell(数据); 将函数转为具体数值

//操作文件流，写入到Excel中
using (Stream stream = new FileStream(@"OrderAddr.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
hssf.Write(stream);
}
Console.WriteLine("导出成功");
Console.ReadLine();
using (Stream stream = new FileStream(@"OrderAddr.xls", FileMode.Open, FileAccess.Read))
{
int num = 0;
for (int j = 1; j <= sheet1.LastRowNum ; j++)
{
string[] array = new string[sheet1.GetRow(j).LastCellNum];
for (int ii = 0; ii < sheet1.GetRow(j).LastCellNum; ii++)
{
array[ii] = sheet1.GetRow(j).GetCell(ii).ToString();
}
num += cla.Add(array[1], array[2], array[3], array[4], array[5], array[6], array[7], int.Parse(array[8]));
Console.WriteLine("已导入{0}行",num);
}
}
Console.ReadLine();
}
catch (Exception ex)
{
Console.WriteLine("错误：" + ex.Message);
Console.ReadLine();
}
}
}
}