public static int QuickWrite(ExcelSheet excelSheet, string filePath, ExcelVersions version = ExcelVersions.Xls)
{
using (FileStream fs = File.Open(filePath, FileMode.OpenOrCreate, FileAccess.ReadWrite))
{
IWorkbook workbook = OpenWrite(version);
ISheet sheet = workbook.CreateSheet(string.IsNullOrEmpty(excelSheet.Name) ? "sheet1" : excelSheet.Name);
for (int i = 0; i < excelSheet.Rows.Count; i++)
{
IRow row = sheet.CreateRow(i);
ExcelRow excelRow = excelSheet.Rows[i];
for (int j = 0; j < excelRow.Cells.Count; j++)
{
ExcelCell excelCell = excelRow.GetCell(j);
ICell cell = CreateCell(row, j, excelCell);
}
}
workbook.Write(fs);
fs.Close();
return(DotNETCode.SUCCESS);
}
}
private static IWorkbook OpenWrite(ExcelVersions version)
{
switch (version)
{
case ExcelVersions.Xls:
{
return(new HSSFWorkbook());
}
case ExcelVersions.Xlsx:
{
return(new XSSFWorkbook());
}
default:
throw new NotSupportedException("不支持的Excel格式");
}
}
/// <summary>
/// 把ExcelSheet对象保存成Excel文件
/// </summary>
/// <param name="excelSheet"></param>
/// <param name="filePath"></param>
/// <param name="version"></param>
/// <returns></returns>
public static int Write <TRow>(string filePath, IList <TRow> dataList, ExcelVersions version = ExcelVersions.Xls) where TRow : IExcelRow
{
List <PropertyAttribute <ExcelColumnAttribute> > properties = Reflections.GetPropertyAttribute <ExcelColumnAttribute>(typeof(TRow));
if (properties.Count == 0)
{
return(DotNETCode.SUCCESS);
}
ExcelSheet sheet = new ExcelSheet();
// 先写第一行，相当于是标题
ExcelRow titleRow = new ExcelRow();
IEnumerable <ExcelColumnAttribute> columns = properties.Select(v => v.Attribute);
foreach (ExcelColumnAttribute column in columns)
{
titleRow.AddCell(column.Name, ExcelCellTypes.String);
}
sheet.AddRow(titleRow);
foreach (TRow row in dataList)
{
ExcelRow excelRow = new ExcelRow();
foreach (PropertyAttribute <ExcelColumnAttribute> property in properties)
{
// 当前要写入的列
object value = property.Property.GetValue(row, null);
excelRow.AddCell(value, property.Attribute.Type);
}
sheet.AddRow(excelRow);
}
return(QuickWrite(sheet, filePath, version));
}
/// <summary>
/// 读取excel文件获得DataReader
/// </summary>
/// <param name="strExcelFileName">Excel文件</param>
/// <param name="strSheetName">工作薄的名字</param>
/// <param name="exVersions">版本</param>
/// <returns></returns>
public static OleDbDataReader ExcelToDataReader(string strExcelFileName, string strSheetName, ExcelVersions exVersions)
{
string ConnectString = string.Empty;
switch (exVersions)
{
case ExcelVersions.Excel8:
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
break;
case ExcelVersions.Excel12:
ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
break;
default:
break;
}
string strExcel = "select * from [" + strSheetName + "$]";
OleDbConnection conn = new OleDbConnection(ConnectString);
OleDbCommand cmd = new OleDbCommand(strExcel, conn);
try
{
conn.Open();
OleDbDataReader reader = cmd.ExecuteReader(CommandBehavior.CloseConnection);
return(reader);
}
catch (System.Data.OleDb.OleDbException e)
{
throw new Exception(e.Message);
}
}
/// <summary>
/// 读取excel文件获得DataTable
/// </summary>
/// <param name="strExcelFileName">目标Excel文件完全路径</param>
/// <param name="strSheetName">工作表的名字</param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string strExcelFileName, string strSheetName, ExcelVersions exVersions)
{
string ConnectString = string.Empty;
switch (exVersions)
{
case ExcelVersions.Excel8:
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 8.0;HDR=YES;IMEX=1';";
break;
case ExcelVersions.Excel12:
ConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + strExcelFileName + ";" + "Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1';";
break;
default:
break;
}
string strExcel = "select * from [" + strSheetName + "$]";
//DataSet ds = new DataSet();
DataTable dt = new DataTable();
using (OleDbConnection conn = new OleDbConnection(ConnectString))
{
conn.Open();
OleDbDataAdapter adapter = new OleDbDataAdapter(strExcel, ConnectString);
//adapter.Fill(ds, strSheetName);
//return ds.Tables[strSheetName];
adapter.Fill(dt);
dt.TableName = strSheetName;
return(dt);
}
#region MyRegion
//很简单的代码，但是问题就出在连接字符串上面，后面一定要加上Extended Properties='Excel 8.0;HDR=NO;IMEX=1'，HDR和IMEX也一定要配合使用，
//哈哈,老实说,我也不知道为什么,这样配合的效果最好,这是我艰苦调试的结果.IMEX=1应该是将所有的列全部视为文本,我也有点忘记了.
//至于HDR本来只是说是否要出现一行标题头而已,但是结果却会导致某些字段值丢失,所以其实我至今也搞不明白为什么,很可能是驱动的问题...
//IMEX=1 解决数字与字符混合时,识别不正常的情况.
#endregion
}
