多个DataTable,处理的每张表的列名我把它加到list里
List<string> lis = new List<string>
{
"地市名称",
"地市公司运营机构简称",
"地市"
};
lis内的值只对应一个表的一个列，
就是表A的某列可能叫"地市名称",或者 "地市公司运营机构简称",
表B的某列可能叫 "地市公司运营机构简称",或者 "地市"
反正所有可能叫的名都存在lis里了，
问题1：怎么进行正确匹配 知道表A的某列是 "地市名称", 或者 "地市公司运营机构简称", 或者 "地市"；
问题2：我要输出处理完成的表 以excel格式保存到指定路径下。
DataTable有columns属性，提取出来和lis对比就知道了
1： datatable to list
#region 扩展类 DateTable转List
///
///
///
public static List List(this DataTable dt)
{
var list = new List();
//Type t = typeof(T);
var plist = new List(typeof(T).GetProperties());
foreach (DataRow item in dt.Rows)
{
T s = Activator.CreateInstance<T>();
for (int i = 0; i < dt.Columns.Count; i++)
{
PropertyInfo info = plist.Find(p => p.Name.ToUpper() == dt.Columns[i].ColumnName.ToUpper());
if (info != null)
{
if (!Convert.IsDBNull(item[i]))
{
info.SetValue(s, item[i], null);
}
}
}
list.Add(s);
}
return list;
}
#endregion
然后使用循环方式使用excel组件功能导出到excel
2：使用Nopi导出excel
先定义导出的列头
NPOIHelper.ListColumnsName = new SortedList(new NoSort());
NPOIHelper.ListColumnsName.Add("ModelNo", "型号");
NPOIHelper.ListColumnsName.Add("DrawingNumber", "图号");
然后导出
ExportTable 你导出的table
NPOIHelper.ExportExcel(ExportTable, fileName);
导出类
public class NPOIHelper
{
///
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="filePath"></param>
public static void ExportExcel(DataTable dtSource, string filePath)
{
if (ListColumnsName == null || ListColumnsName.Count == 0)
throw (new Exception("请对ListColumnsName设置要导出的列明！"));
HSSFWorkbook excelWorkbook = CreateExcelFile();
InsertRow(dtSource, excelWorkbook);
SaveExcelFile(excelWorkbook, filePath);
}
/// <summary>
/// 导出Excel
/// </summary>
/// <param name="dgv"></param>
/// <param name="filePath"></param>
public static byte[] ExportExcel(DataTable dtSource)
{
if (ListColumnsName == null || ListColumnsName.Count == 0)
throw (new Exception("请对ListColumnsName设置要导出的列明！"));
HSSFWorkbook excelWorkbook = CreateExcelFile();
InsertRow(dtSource, excelWorkbook);
MemoryStream excelStream = new MemoryStream();
excelWorkbook.Write(excelStream);
byte[] byContent = excelStream.ToArray();
excelStream.Close();
//excelStream.Dispose();
return byContent;
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="excelWorkBook"></param>
/// <param name="filePath"></param>
protected static void SaveExcelFile(HSSFWorkbook excelWorkBook, string filePath)
{
FileStream file = null;
try
{
file = new FileStream(filePath, FileMode.Create);
excelWorkBook.Write(file);
}
finally
{
if (file != null)
{
file.Close();
}
}
}
/// <summary>
/// 保存Excel文件
/// </summary>
/// <param name="excelWorkBook"></param>
/// <param name="filePath"></param>
protected static MemoryStream SaveExcelFile(HSSFWorkbook excelWorkBook)
{
try
{
MemoryStream excelStream = new MemoryStream();
excelWorkBook.Write(excelStream);
return excelStream;
}
finally
{
}
}
/// <summary>
/// 创建Excel文件
/// </summary>
/// <param name="filePath"></param>
protected static HSSFWorkbook CreateExcelFile()
{
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
return hssfworkbook;
}
/// <summary>
/// 创建excel表头
/// </summary>
/// <param name="dgv"></param>
/// <param name="excelSheet"></param>
protected static void CreateHeader(HSSFSheet excelSheet)
{
int cellIndex = 0;
HSSFRow newRow = (HSSFRow)excelSheet.CreateRow(0);
//循环导出列
foreach (DictionaryEntry de in ListColumnsName)
{
HSSFCell newCell = (HSSFCell)newRow.CreateCell(cellIndex);
newCell.SetCellValue(de.Value.ToString());
cellIndex++;
}
}
/// <summary>
/// 插入数据行
/// </summary>
protected static void InsertRow(DataTable dtSource, HSSFWorkbook excelWorkbook)
{
int rowCount = 0;
int sheetCount = 1;
HSSFSheet newsheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
//循环数据源导出数据集
CreateHeader(newsheet);
foreach (DataRow dr in dtSource.Rows)
{
rowCount++;
//超出65535条数据 创建新的工作簿
if (rowCount == 65535)
{
rowCount = 1;
sheetCount++;
newsheet = (HSSFSheet)excelWorkbook.CreateSheet("Sheet" + sheetCount);
CreateHeader(newsheet);
}
HSSFRow newRow = (HSSFRow)newsheet.CreateRow(rowCount);
InsertCell(dr, newRow, excelWorkbook);
}
}
/// <summary>
/// 导出数据行
/// </summary>
/// <param name="drSource"></param>
/// <param name="currentExcelRow"></param>
/// <param name="excelWorkBook"></param>
protected static void InsertCell(DataRow drSource, HSSFRow currentExcelRow, HSSFWorkbook excelWorkBook)
{
for (int cellIndex = 0; cellIndex < ListColumnsName.Count; cellIndex++)
{
//列名称
string columnsName = ListColumnsName.GetKey(cellIndex).ToString();
HSSFCell newCell = null;
//Type rowType = drSource[cellIndex].GetType();
//string drValue = drSource[cellIndex].ToString().Trim();
Type rowType = drSource[columnsName].GetType();
string drValue = drSource[columnsName].ToString().Trim();
switch (rowType.ToString())
{
case "System.String"://字符串类型
drValue = drValue.Replace("&", "&");
drValue = drValue.Replace(">", ">");
drValue = drValue.Replace("<", "<");
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(dateV);
//格式化显示
HSSFCellStyle cellStyle = (HSSFCellStyle)excelWorkBook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)excelWorkBook.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("yyyy-mm-dd hh:mm:ss");
newCell.CellStyle = cellStyle;
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(intV.ToString());
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell = (HSSFCell)currentExcelRow.CreateCell(cellIndex);
newCell.SetCellValue("");
break;
default:
throw (new Exception(rowType + "：类型数据无法处理!"));
}
}
}
}