NpoiHelper

NpoiHelper

using DBUtility;
using NPOI.HSSF.UserModel;
using NPOI.SS.UserModel;
using NPOI.XSSF.UserModel;
using System;
using System.Collections.Generic;
using System.Data;
using System.IO;
namespace NpoiHelper
{
	internal class NpoiHelper
	{
		public static DataTable ExcelToTable(string file)
		{
			DataTable dataTable = new DataTable();
			string a = Path.GetExtension(file).ToLower();
			DataTable result;
			using (FileStream fileStream = new FileStream(file, FileMode.Open, FileAccess.Read))
			{
				bool flag = a == ".xlsx";
				IWorkbook workbook;
				if (flag)
				{
					workbook = new XSSFWorkbook(fileStream);
				}
				else
				{
					bool flag2 = a == ".xls";
					if (flag2)
					{
						workbook = new HSSFWorkbook(fileStream);
					}
					else
					{
						workbook = null;
					}
				}
				bool flag3 = workbook == null;
				if (flag3)
				{
					result = null;
					return result;
				}
				ISheet sheetAt = workbook.GetSheetAt(0);
				IRow row = sheetAt.GetRow(sheetAt.FirstRowNum);
				List<int> list = new List<int>();
				for (int i = 0; i < (int)row.LastCellNum; i++)
				{
					object valueType = NpoiHelper.GetValueType(row.GetCell(i));
					bool flag4 = valueType == null || valueType.ToString() == string.Empty;
					if (flag4)
					{
						dataTable.Columns.Add(new DataColumn("Columns" + i.ToString()));
					}
					else
					{
						dataTable.Columns.Add(new DataColumn(valueType.ToString()));
					}
					list.Add(i);
				}
				for (int j = sheetAt.FirstRowNum + 1; j <= sheetAt.LastRowNum; j++)
				{
					DataRow dataRow = dataTable.NewRow();
					bool flag5 = false;
					foreach (int current in list)
					{
						dataRow[current] = NpoiHelper.GetValueType(sheetAt.GetRow(j).GetCell(current));
						bool flag6 = dataRow[current] != null && dataRow[current].ToString() != string.Empty;
						if (flag6)
						{
							flag5 = true;
						}
					}
					bool flag7 = flag5;
					if (flag7)
					{
						dataTable.Rows.Add(dataRow);
					}
				}
			}
			result = dataTable;
			return result;
		}
		public static void TableToExcel(DataTable dt, string file)
		{
			string a = Path.GetExtension(file).ToLower();
			bool flag = a == ".xlsx";
			IWorkbook workbook;
			if (flag)
			{
				workbook = new XSSFWorkbook();
			}
			else
			{
				bool flag2 = a == ".xls";
				if (flag2)
				{
					workbook = new HSSFWorkbook();
				}
				else
				{
					workbook = null;
				}
			}
			bool flag3 = workbook == null;
			if (!flag3)
			{
				ISheet sheet = string.IsNullOrEmpty(dt.TableName) ? workbook.CreateSheet("Sheet1") : workbook.CreateSheet(dt.TableName);
				IRow row = sheet.CreateRow(0);
				for (int i = 0; i < dt.Columns.Count; i++)
				{
					ICell cell = row.CreateCell(i);
					cell.SetCellValue(dt.Columns[i].ColumnName);
				}
				for (int j = 0; j < dt.Rows.Count; j++)
				{
					IRow row2 = sheet.CreateRow(j + 1);
					for (int k = 0; k < dt.Columns.Count; k++)
					{
						ICell cell2 = row2.CreateCell(k);
						bool flag4 = PageValidate.IsNumber(dt.Rows[j][k].ToString());
						if (flag4)
						{
							cell2.SetCellValue((double)Convert.ToInt32(dt.Rows[j][k].ToString()));
						}
						else
						{
							bool flag5 = PageValidate.IsDecimal(dt.Rows[j][k].ToString());
							if (flag5)
							{
								cell2.SetCellValue(Convert.ToDouble(dt.Rows[j][k].ToString()));
							}
							else
							{
								cell2.SetCellValue(dt.Rows[j][k].ToString());
							}
						}
					}
				}
				MemoryStream memoryStream = new MemoryStream();
				workbook.Write(memoryStream);
				byte[] array = memoryStream.ToArray();
				using (FileStream fileStream = new FileStream(file, FileMode.Create, FileAccess.Write))
				{
					fileStream.Write(array, 0, array.Length);
					fileStream.Flush();
				}
			}
		}
		private static object GetValueType(ICell cell)
		{
			bool flag = cell == null;
			object result;
			if (flag)
			{
				result = null;
			}
			else
			{
				switch (cell.CellType)
				{
				case CellType.Numeric:
					result = cell.NumericCellValue;
					return result;
				case CellType.String:
					result = cell.StringCellValue;
					return result;
				case CellType.Blank:
					result = null;
					return result;
				case CellType.Boolean:
					result = cell.BooleanCellValue;
					return result;
				case CellType.Error:
					result = cell.ErrorCellValue;
					return result;
				}
				result = "=" + cell.CellFormula;
			}
			return result;
		}
	}
}