public MemoryStream CreateMemoryStream <T>(IEnumerable <T> sources)
{
HSSFWorkbook workbook = null;
try
{
ColumnInfo[] cols = ColumnInfoContainer.GetColumnInfos(typeof(T));
return(CreateMemoryStream(sources, cols, out workbook));
}
finally
{
workbook?.Close();
}
}
public MemoryStream CreateMemoryStream <T>(IEnumerable <T> sources, Dictionary <string, string> exportFieldsWithName)
{
HSSFWorkbook workbook = null;
try
{
ColumnInfo[] cols = ColumnInfoContainer.GetColumnInfos(typeof(T), exportFieldsWithName);
return(CreateMemoryStream(sources, cols, out workbook));
}
finally
{
workbook?.Close();
}
}
public static void Close()
{
workbook.Close();
sheetFS.Close();
}
private List <T> CreateExel(List <T> list, ISheet sheet = null, HSSFWorkbook hssfWorkbook = null, XSSFWorkbook xssWorkbook = null)
{
IRow columnRow = sheet.GetRow(0); // 第一行为字段名
Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>();
for (int j = 0; j < columnRow.LastCellNum; j++)
{
ICell cell = columnRow.GetCell(j);
PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
if (propertyInfo != null)
{
mapPropertyInfoDict.Add(j, propertyInfo);
}
}
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
T entity = new T();
for (int j = row.FirstCellNum; j <= columnRow.LastCellNum; j++)
{
if (mapPropertyInfoDict.ContainsKey(j))
{
if (row.GetCell(j) != null)
{
PropertyInfo propertyInfo = mapPropertyInfoDict[j];
switch (propertyInfo.PropertyType.ToString())
{
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int16":
case "System.Nullable`1[System.Int16]":
mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int32":
case "System.Nullable`1[System.Int32]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
break;
default:
case "System.String":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
break;
}
}
}
}
list.Add(entity);
}
hssfWorkbook?.Close();
xssWorkbook?.Close();
return(list);
}
/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">数据源</param>
/// <param name="sHeaderText">表头文本</param>
/// <param name="columns">需要导出的属性</param>
private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = ReflectionHelper.GetProperties(type, columns);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
//单元格填充循环外设定单元格格式，避免4000行异常
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[properties.Length];
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
//GBK对应的code page是CP936
arrColWidth[columnIndex] = properties[columnIndex].Name.Length;
}
#endregion
for (int rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
// 类属性如果有Description就用Description当做列名
DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute));
string description = properties[columnIndex].Name;
if (customAttribute != null)
{
description = customAttribute.Description;
}
headerRow.CreateCell(columnIndex).SetCellValue(description);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//根据表头设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
}
}
#endregion
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
newCell.CellStyle = contentStyle;
string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString();
//根据单元格内容设定列宽
int length = Math.Min(253, Encoding.UTF8.GetBytes(drValue).Length + 1) * 256;
if (sheet.GetColumnWidth(columnIndex) < length && !drValue.IsEmpty())
{
sheet.SetColumnWidth(columnIndex, length);
}
switch (properties[columnIndex].PropertyType.ToString())
{
case "System.String":
newCell.SetCellValue(drValue);
break;
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
newCell.SetCellValue(drValue.ParseToDateTime());
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
newCell.SetCellValue(drValue.ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
case "System.Int16":
case "System.Nullable`1[System.Int16]":
case "System.Int32":
case "System.Nullable`1[System.Int32]":
newCell.SetCellValue(drValue.ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
newCell.SetCellValue(drValue.ParseToString());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Single":
case "System.Nullable`1[System.Single]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.DBNull":
newCell.SetCellValue(string.Empty);
break;
default:
newCell.SetCellValue(string.Empty);
break;
}
}
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
workbook.Close();
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 依据参数，选择生成SQL语言的方法。
/// </summary>
/// <param name="filesPath">文件完整的路径名,如D:\\test.xls</param>
/// <param name="filesTypes">文件类型，如*.xls将会引用NPOI组件</param>
/// <param name="sqlLangTypes">SQL语言类别，如Insert、Update、Delete、Up-Only</param>
/// <returns></returns>
public static void npoiPrintSQLLangTypesAndMethods(string filesPath, int filesTypes, int sqlLangTypes)
{
if (filesTypes == 2003)
{
#region //xls文件的处理
try
{
FileStream fs = new FileStream(filesPath, FileMode.Open);
HSSFWorkbook HBook = new HSSFWorkbook(fs);
ISheet isheet = HBook.GetSheetAt(FormMain.defaultTables);
#region //回传当前读取的Sheet表名！
FormMain.selectTableName = isheet.SheetName;
#endregion
switch (sqlLangTypes)
{
case 1:
npoiPrintSQLLangInsertMulti(isheet);
break;
case 2:
npoiPrintSQLLangDelete(isheet);
break;
case 3:
npoiPrintSQLLangUpdate(isheet);
break;
case 4:
npoiPrintSQLLangUpdateOnly(isheet);
break;
case 5:
npoiPrintSQLLangInsertEachLineASentence(isheet);
break;
default:
break;
}
//释放过程中使用的资源！
HBook.Close();
fs.Close();
FormMain.isSqlLangCreatedSuccessful = true;
}
catch (Exception ex)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
#endregion
}
else if (filesTypes == 2007) //*.XLSX
{
try
{
ExcelPackage excelPackage;
FileInfo newFile = new FileInfo(filesPath);
excelPackage = new ExcelPackage(newFile);
ExcelWorkbook myWorkbook = excelPackage.Workbook;
ExcelWorksheet myWorksheet = myWorkbook.Worksheets[FormMain.defaultTables + 1];
#region //回传当前读取的Sheet表名！
FormMain.selectTableName = myWorksheet.Name;
#endregion
switch (sqlLangTypes)
{
case 1:
excelPackagePrintSQLLangInsertMulti(myWorksheet);
break;
case 2:
excelPackagePrintSQLLangDelete(myWorksheet);
break;
case 3:
excelPackagePrintSQLLangUpdate(myWorksheet);
break;
case 4:
excelPackagePrintSQLLangUpdateOnly(myWorksheet);
break;
case 5:
excelPackagePrintSQLLangInsertEachLineASentence(myWorksheet);
break;
default:
break;
}
//貌似很有必要释放内存，不然没法连续执行，不关掉程序文档打不开。
excelPackage.Dispose();
FormMain.isSqlLangCreatedSuccessful = true;
}
catch (Exception ex)
{
FormMain.isSqlLangCreatedSuccessful = false;
MessageBox.Show("过程出现异常错误" + ex.ToString(), "重要提示",
MessageBoxButtons.OK, MessageBoxIcon.Information);
return;
}
}
}
public int SearchImei(string imei)
{
int ret = 0;
//获取excel文件信息
FileInfo file = new FileInfo(excelFullName);
//检测文件是否存在
if (!File.Exists(excelFullName))
{
if (CreatExcel() != 0)
{
return(ret);
}
}
else
{
file.IsReadOnly = false;
}
HSSFWorkbook workbook = null;
try
{
using (FileStream fs1 = File.OpenRead(excelFullName))
{
workbook = new HSSFWorkbook(fs1); //实例化Excel工作薄类
fs1.Close();
ISheet sheet = workbook.GetSheet("Sheet1");
//ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中
//cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//设序号
int coloumNum = sheet.GetRow(0).PhysicalNumberOfCells;
int rowCount = sheet.LastRowNum;
//string strCell = sheet.GetRow(0).GetCell(0).ToString();
for (int k = 1; k < rowCount + 1; k++)
{
//for (var j = 0; j < coloumNum; j++)
//{
string strCell = sheet.GetRow(k).GetCell(1).ToString();
//sheet.RemoveRow()
if (strCell == imei)
{
ret = -1;
break;
}
//}
}
//设定值
//using (FileStream fs2 = File.OpenWrite(excelFullName))
//{
// workbook.Write(fs2);
// workbook.Close();
//}
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
ret = -1;
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
return(ret);
}
/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">需要导出的泛型List</param>
/// <param name="strHeaderText">第一行标题头</param>
/// <param name="titleDictionaries">列名称字典映射</param>
/// <param name="title">todo: describe title parameter on ListToExcel</param>
/// <param name="titleDic">todo: describe titleDic parameter on ListToExcel</param>
/// <returns>内存流</returns>
private static MemoryStream ListToExcel <T>(List <T> list, string strHeaderText = null,
Dictionary <string, string> titleDic = null)
{
HSSFWorkbook workbook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet();
//设置文件属性
SetFileSummary(strHeaderText, workbook);
//获取列宽
int[] arrColWidth = GetColumnWidth(titleDic);
//日期风格
ICellStyle dateStyle = GetDateStyle(workbook);
//Excel标题风格
HSSFCellStyle headStyle = GetHeadStyle(workbook);
/*在第一行创建标题行*/
CreateHeadRow(titleDic, sheet, arrColWidth, headStyle);
//通过反射得到对象的属性集合
Type type = null;
if (list != null && list.Count > 0)
{
type = list.First().GetType();
for (int row = 0; row < list.Count; row++)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(row + 1);
int cellIndex = 0;
foreach (var dicItem in titleDic)
{
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(cellIndex);
string drValue = string.Empty;
PropertyInfo propInfo = type.GetProperty(dicItem.Key);
var propValue = type.GetProperty(dicItem.Key).GetValue(list[row]);
if (propValue != null)
{
drValue = propValue.ToString();
}
SetCellValueByType(newCell, drValue, propInfo, dateStyle);
cellIndex = cellIndex + 1;
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook.Close();
return(ms);
}
}
/// <summary>
/// 由DataTable导出Excel
/// </summary>
/// <param name="sourceTable">要导出数据的DataTable</param>
/// <param name="sheetName">工作薄名称,可选</param>
/// <param name="colNames">需要导出的列名,可选</param>
/// <param name="colAliasNames">导出的列名重命名,可选</param>
/// <param name="colDataFormats">列格式化集合,可选</param>
/// <param name="sheetSize">指定每个工作薄显示的记录数，可选（不指定或指定小于0，则表示只生成一个工作薄）</param>
/// <returns></returns>
public static MemoryStream ToExcel(DataTable sourceTable, string sheetName = "sheet", string[] colNames = null,
IDictionary <string, string> colAliasNames = null, Dictionary <string, string> colDataFormats = null, int sheetSize = 0)
{
if (sourceTable.Rows.Count <= 0)
{
return(null);
}
//创建Excel文件的对象
HSSFWorkbook workbook = new HSSFWorkbook();
ICellStyle headerCellStyle = GetCellStyle(workbook, true);
if (colNames == null || colNames.Length <= 0)
{
colNames = sourceTable.Columns.Cast <DataColumn>().OrderBy(c => c.Ordinal).Select(c => c.ColumnName).ToArray();
}
IEnumerable <DataRow> batchDataRows, dataRows = sourceTable.Rows.Cast <DataRow>();
int sheetCount = 0;
if (sheetSize <= 0)
{
sheetSize = sourceTable.Rows.Count;
}
while ((batchDataRows = dataRows.Take(sheetSize)).Count() > 0)
{
Dictionary <int, ICellStyle> colStyles = new Dictionary <int, ICellStyle>();
ISheet sheet = workbook.CreateSheet(sheetName + (++sheetCount).ToString());
IRow headerRow = sheet.CreateRow(0);
// handling header.
for (int i = 0; i < colNames.Length; i++)
{
ICell headerCell = headerRow.CreateCell(i);
if (colAliasNames != null && colAliasNames.ContainsKey(colNames[i]))
{
headerCell.SetCellValue(colAliasNames[colNames[i]]);
}
else
{
headerCell.SetCellValue(colNames[i]);
}
headerCell.CellStyle = headerCellStyle;
sheet.AutoSizeColumn(headerCell.ColumnIndex);
if (colDataFormats != null && colDataFormats.ContainsKey(colNames[i]))
{
colStyles[headerCell.ColumnIndex] = GetCellStyleWithDataFormat(workbook, colDataFormats[colNames[i]]);
}
else
{
colStyles[headerCell.ColumnIndex] = GetCellStyle(workbook);
}
}
// handling value.
int rowIndex = 1;
foreach (DataRow row in batchDataRows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
for (int i = 0; i < colNames.Length; i++)
{
ICell cell = dataRow.CreateCell(i);
SetCellValue(cell, (row[colNames[i]] ?? "").ToString(), sourceTable.Columns[colNames[i]].DataType, colStyles);
ReSizeColumnWidth(sheet, cell);
}
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
dataRows = dataRows.Skip(sheetSize);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
workbook.Close();
ms.Seek(0, SeekOrigin.Begin);
return(ms);
}
/// <summary>
/// 保存所有数据表格
/// </summary>
/// <param name="dataTables"></param>
/// <param name="path"></param>
private void SaveAllDataTable(List <DataTable> dataTables, string path)
{
IWorkbook workbook = new HSSFWorkbook();//创建一个工作簿
for (int n = 0; n < dataTables.Count; n++)
{
ISheet sheet = workbook.CreateSheet(dataTables[n].TableName);//创建一个 sheet 表
//设置列宽
sheet.SetColumnWidth(1, 30 * 256); //名称
sheet.SetColumnWidth(2, 30 * 256); //功能
sheet.SetColumnWidth(7, 30 * 256); //时间
sheet.SetColumnWidth(3, 15 * 256); //下限
sheet.SetColumnWidth(4, 15 * 256); //上限
sheet.SetColumnWidth(5, 15 * 256); //测量值
IRow rowH = sheet.CreateRow(0); //创建一行
ICell cell = null; //创建一个单元格
ICellStyle cellStyle = workbook.CreateCellStyle(); //创建单元格样式
IDataFormat dataFormat = workbook.CreateDataFormat(); //创建格式
cellStyle.DataFormat = dataFormat.GetFormat("@"); //设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
//设置列名
foreach (DataColumn col in dataTables[n].Columns)
{
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption); //创建单元格并设置单元格内容
rowH.Cells[col.Ordinal].CellStyle = cellStyle; //设置单元格格式
}
//表格样式
IFont font = workbook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 12;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
//写入数据
for (int i = 0; i < dataTables[n].Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 1);
row.Height = 350;
for (int j = 0; j < dataTables[n].Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dataTables[n].Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
sheet = null;
}
//创建文件
FileStream file = new FileStream(path, FileMode.Create, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
workbook = null;
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <returns></returns>
public MemoryStream ExportExcelFromDataTable(DataTable dtSource, string strHeaderText = null)
{
var workbook = new HSSFWorkbook();
var sheet = workbook.CreateSheet();
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
var arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
var headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
var headerRow = sheet.CreateRow(1);
var headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
var font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
try
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
catch { }
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
var dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
var newCell = dataRow.CreateCell(column.Ordinal);
var drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
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.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (var ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook.Close();
return(ms);
}
}
public void ExportExcelFile()
{
if (this.m_Result.Count == 0)
{
throw new NotSupportedException("Result is empty.");
}
FileInfo fileInfo = new FileInfo(this.m_ResultFilePath);
FileStream fs = new FileStream(fileInfo.FullName, FileMode.Create);
HSSFWorkbook workBook = new HSSFWorkbook();
HSSFSheet sheet = (HSSFSheet)workBook.CreateSheet(fileInfo.Name);
#region fontStyle
ICellStyle style = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
style.SetFont(font);
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
#endregion
Dictionary <string, int> columnNameMapIndex = new Dictionary <string, int>();
#region Add Sheet Titles
sheet.CreateRow(0);
HSSFRow row = (HSSFRow)sheet.GetRow(0);
row.Height = 17 * 20;
HSSFCell cell = (HSSFCell)row.CreateCell(0);
cell.CellStyle = style;
int columnCount = 1;
foreach (string markingName in this.m_Result.First().Value.Keys.ToList())
{
cell = (HSSFCell)row.CreateCell(columnCount);
cell.CellStyle = style;
cell.SetCellValue(markingName);
columnNameMapIndex.Add(markingName, columnCount);
columnCount++;
}
#endregion
int rowCount = 1;
foreach (KeyValuePair <string, Dictionary <string, string> > item in this.m_Result)
{
row = (HSSFRow)sheet.CreateRow(rowCount);
cell = (HSSFCell)row.CreateCell(0);
cell.CellStyle = style;
cell.SetCellValue(item.Key);
foreach (KeyValuePair <string, string> subItem in item.Value)
{
cell = (HSSFCell)row.CreateCell(columnNameMapIndex[subItem.Key]);
cell.CellStyle = style;
cell.SetCellValue(Convert.ToInt32(Convert.ToDouble(subItem.Value)).ToString());
}
rowCount++;
}
workBook.Write(fs);
workBook.Close();
fs.Close();
}
/// <summary>
/// 渲染到xls
/// </summary>
/// <param name="isOnlyOneSheet"></param>
/// <returns></returns>
public MemoryStream RenderToXls(bool isOnlyOneSheet = true)
{
using (var ms = new MemoryStream())
{
HSSFWorkbook workbook = new HSSFWorkbook();
int rowIndex = 0;
HSSFSheet sheet = null;
foreach (var table in listTable)
{
int columnCount = table.ColumnCount;
var title = table.Title == null ? new ExcelTitle() : table.Title;
var header = table.Header == null ? new ExcelHeader() : table.Header;
var tableheader = table.TableHeader == null ? new ExcelTableHeader() : table.TableHeader; //table.Rows.FirstOrDefault(r => r.IsRowHead);
var tablebody = table.TableBody == null ? new ExcelTableBody() : table.TableBody; //table.Rows.Where(r => !r.IsRowHead).ToList();
var tablefooter = table.TableFooter == null ? new ExcelTableFooter() : table.TableFooter;
var footer = table.Footer == null ? new ExcelFooter() : table.Footer;
if (rowIndex == 0)
{
sheet = title.IsNullOrWhiteSpace() ? workbook.CreateSheet() as HSSFSheet : workbook.CreateSheet(title.TableTitle) as HSSFSheet;
PrintSetup(sheet, table.Landscape);
if (table.ColumnWidths != null && table.ColumnWidths.Length == columnCount)
{
for (var i = 0; i < columnCount; i++)
{
sheet.SetColumnWidth(i, table.ColumnWidths[i] * 256 + 200);
}
}
else
{
if (!tableheader.IsNull())
{
for (var i = 0; i < columnCount; i++)
{
int columnWidth = Encoding.Default.GetBytes(tableheader.Rows[0].Cells[i].Value.ToString()).Length;
sheet.SetColumnWidth(i, columnWidth * 256 + 200);
}
}
}
}
//标题
if (!title.IsNullOrWhiteSpace())
{
IRow rowTitle = sheet.CreateRow(rowIndex);
rowTitle.Height = short.Parse(title.TitleHeight * 20 + "");
ICell headerCell = rowTitle.CreateCell(0);
headerCell.SetCellValue(title.TableTitle);
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, columnCount - 1));
var defaultTitleCellStyle = string.IsNullOrWhiteSpace(title.DefaultTitleCellStyle) ?
workbook.GetDefaultTitleCellStyle() : workbook.GetUserDirCellStyle(title.DefaultTitleCellStyle);
headerCell.Style(defaultTitleCellStyle);
//ExcelCellSetter.SetDefaultTitleCellStyle(workbook, headerCell);
rowIndex++;
}
//页头
if (!header.IsNull())
{
ICellStyle defaultHeaderCellStyle = string.IsNullOrWhiteSpace(header.DefaultHeaderCellStyle) ?
workbook.GetDefaultHeaderCellStyle() : workbook.GetUserDirCellStyle(header.DefaultHeaderCellStyle);//ExcelCellSetter.GetDefaultHeaderCellStyle(workbook);
for (var rindex = 0; rindex < header.Rows.Count; rindex++)
{
var headerRow = sheet.CreateRow(rowIndex);
var row = header.Rows[rindex];
if (row.HaveRowBreak)
{
SetRowBreak(rowIndex, sheet);
}
headerRow.Height = short.Parse(row.Height * 20 + "");
int colindex = 0;
for (var cindex = 0; cindex < row.Cells.Count; cindex++)
{
int colspan = row.Cells[cindex].Colspan;
CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, colindex, colindex + colspan - 1);
sheet.AddMergedRegion(address);
headerRow.CreateCell(colindex).SetCellValue(row.Cells[cindex].Value);
if (row.Cells[cindex].CellStyle != null)
{
headerRow.Cells[colindex].Style(row.Cells[cindex].CellStyle);
//ExcelCellSetter.SetCellStyle(headerRow.Cells[colindex], row.Cells[cindex].CellStyle);
}
else if (row.Cells[cindex].Style != null)
{
if (row.Cells[cindex].IsBasedOnDefaultStyle)
{
headerRow.Cells[colindex].Style(defaultHeaderCellStyle);
}
headerRow.Cells[colindex].Style(row.Cells[cindex].Style);
}
else
{
headerRow.Cells[colindex].Style(defaultHeaderCellStyle);
//ExcelCellSetter.SetCellStyle(headerRow.Cells[colindex], defaultHeaderCellStyle);
}
colindex = colindex + colspan;
}
rowIndex++;
}
}
//表头
if (!tableheader.IsNull())
{
ICellStyle defaultTableHeaderCellStyle =
string.IsNullOrWhiteSpace(
tableheader.DefaultTableHeaderCellStyle) ?
workbook.GetDefaultTableHeaderCellStyle() :
workbook.GetUserDirCellStyle(tableheader.DefaultTableHeaderCellStyle);//ExcelCellSetter.GetDefaultTableHeaderCellStyle(workbook);
foreach (var hr in tableheader.Rows)
{
var headerRow = sheet.CreateRow(rowIndex++);
headerRow.Height = short.Parse(hr.Height * 20 + "");
for (var i = 0; i < columnCount; i++)
{
ExcelCell cell = (ExcelCell)hr.Cells[i];
headerRow.CreateCell(i).SetCellValue(cell.Value);
if (cell.CellStyle != null)
{
headerRow.Cells[i].Style(cell.CellStyle);
//ExcelCellSetter.SetCellStyle(headerRow.Cells[i], cell.CellStyle);
}
else if (cell.Style != null)
{
if (cell.IsBasedOnDefaultStyle)
{
headerRow.Cells[i].Style(defaultTableHeaderCellStyle);
}
headerRow.Cells[i].Style(cell.Style);
}
else
{
headerRow.Cells[i].Style(defaultTableHeaderCellStyle);
//ExcelCellSetter.SetCellStyle(headerRow.Cells[i], defaultTableHeaderCellStyle);
}
}
}
}
//表体
if (!tablebody.IsNull())
{
ICellStyle defaultcellstyle = string.IsNullOrWhiteSpace(tablebody.DefaultTableBodyCellStyle) ?
workbook.GetDefaultTableBodyCellStyle() : workbook.GetUserDirCellStyle(tablebody.DefaultTableBodyCellStyle);//ExcelCellSetter.GetDefaultCellStyle(workbook);
//表体
foreach (var row in tablebody.Rows)
{
var dataRow = sheet.CreateRow(rowIndex++);
if (row.HaveRowBreak)
{
SetRowBreak(rowIndex, sheet);
}
dataRow.Height = short.Parse(row.Height * 20 + "");
for (var i = 0; i < columnCount; i++)
{
ExcelCell cell = (ExcelCell)row.Cells[i];
var cellval = cell.Value == null ? "" : cell.Value;
dataRow.CreateCell(i, (CellType)cell.CellType).SetCellValue(cellval);
if (cell.CellStyle != null)
{
dataRow.Cells[i].Style(cell.CellStyle);
//ExcelCellSetter.SetCellStyle(dataRow.Cells[i], cell.CellStyle);
}
else if (cell.Style != null)
{
if (cell.IsBasedOnDefaultStyle)
{
dataRow.Cells[i].Style(defaultcellstyle);
}
dataRow.Cells[i].Style(cell.Style);
}
else
{
dataRow.Cells[i].Style(defaultcellstyle);
//ExcelCellSetter.SetCellStyle(dataRow.Cells[i], defaultcellstyle);
}
}
}
}
//表尾
if (!tablefooter.IsNull())
{
ICellStyle defaultTableFooterCellStyle =
string.IsNullOrWhiteSpace(
tablefooter.DefaultTableFooterCellStyle) ?
workbook.GetDefaultTableFooterCellStyle() :
workbook.GetUserDirCellStyle(tablefooter.DefaultTableFooterCellStyle);//ExcelCellSetter.GetDefaultTableHeaderCellStyle(workbook);
foreach (var hr in tablefooter.Rows)
{
var footerRow = sheet.CreateRow(rowIndex++);
footerRow.Height = short.Parse(hr.Height * 20 + "");
for (var i = 0; i < columnCount; i++)
{
ExcelCell cell = (ExcelCell)hr.Cells[i];
footerRow.CreateCell(i).SetCellValue(cell.Value);
if (cell.CellStyle != null)
{
footerRow.Cells[i].Style(cell.CellStyle);
}
else if (cell.Style != null)
{
if (cell.IsBasedOnDefaultStyle)
{
footerRow.Cells[i].Style(defaultTableFooterCellStyle);
}
footerRow.Cells[i].Style(cell.Style);
}
else
{
footerRow.Cells[i].Style(defaultTableFooterCellStyle);
}
}
}
}
//页尾
if (!footer.IsNull())
{
ICellStyle defaultFooterCellStyle = string.IsNullOrWhiteSpace(footer.DefaultFooterCellStyle) ?
workbook.GetDefaultFooterCellStyle() : workbook.GetUserDirCellStyle(footer.DefaultFooterCellStyle);//ExcelCellSetter.GetDefaultFooterCellStyle(workbook);
for (var rindex = 0; rindex < footer.Rows.Count; rindex++)
{
var footerRow = sheet.CreateRow(rowIndex);
var row = footer.Rows[rindex];
if (row.HaveRowBreak)
{
SetRowBreak(rowIndex, sheet);
}
//1 px = 0.75 point
footerRow.Height = short.Parse(row.Height * 0.75 * 20 + "");
int colindex = 0;
for (var cindex = 0; cindex < row.Cells.Count; cindex++)
{
int colspan = row.Cells[cindex].Colspan;
CellRangeAddress address = new CellRangeAddress(rowIndex, rowIndex, colindex, colindex + colspan - 1);
sheet.AddMergedRegion(address);
if (row.Cells[cindex].CellType == Common.CellTypes.Image)
{
footerRow.CreateCell(colindex);
byte[] imgbyte = Convert.FromBase64String(row.Cells[cindex].Value);
int pictureIdx = workbook.AddPicture(imgbyte, PictureType.PNG);
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 1, 0, 0, 0, rowIndex, colspan, rowIndex + 1);
anchor.AnchorType = AnchorType.DontMoveAndResize;
HSSFPicture pict = (HSSFPicture)patriarch.CreatePicture(anchor, pictureIdx);
pict.Resize(0.99, 0.99);
//pict.Resize();
}
else
{
footerRow.CreateCell(colindex).SetCellValue(row.Cells[cindex].Value);
}
if (row.Cells[cindex].CellStyle != null)
{
footerRow.Cells[colindex].Style(row.Cells[cindex].CellStyle);
//ExcelCellSetter.SetCellStyle(footerRow.Cells[colindex], row.Cells[cindex].CellStyle);
}
else if (row.Cells[cindex].Style != null)
{
if (row.Cells[cindex].IsBasedOnDefaultStyle)
{
footerRow.Cells[colindex].Style(defaultFooterCellStyle);
}
footerRow.Cells[colindex].Style(row.Cells[cindex].Style);
}
else
{
footerRow.Cells[colindex].Style(defaultFooterCellStyle);
//if (defaultFooterCellStyle != null)
//ExcelCellSetter.SetCellStyle(footerRow.Cells[colindex], defaultFooterCellStyle);
}
colindex = colindex + colspan;
}
rowIndex++;
}
}
if (!isOnlyOneSheet)
{
rowIndex = 0;
}
}
workbook.Write(ms);
ms.Position = 0;
workbook.Close();
return(ms);
}
}
public static List <List <string> > RetrieveDataFromExcel_XLS(string wholefn, string sheetname, int columns = 101)
{
var ret = new List <List <string> >();
HSSFWorkbook hssfwb = null;
try
{
using (var fs = File.OpenRead(wholefn))
{
hssfwb = new HSSFWorkbook(fs);
HSSFFormulaEvaluator formula = new HSSFFormulaEvaluator(hssfwb);
ISheet targetsheet = null;
if (!string.IsNullOrEmpty(sheetname))
{
targetsheet = hssfwb.GetSheet(sheetname);
}
else
{
targetsheet = hssfwb.GetSheetAt(0);
}
if (targetsheet == null)
{
return(ret);
}
var rownum = targetsheet.LastRowNum;
for (var ridx = 0; ridx <= rownum; ridx++)
{
var row = targetsheet.GetRow(ridx);
var cells = row.Cells;
var line = new List <string>();
var cidx = cells[cells.Count - 1].ColumnIndex + 1;
for (var i = 0; i <= cidx; i++)
{
line.Add("");
}
foreach (var c in cells)
{
if (c == null)
{
//line.Add("");
}
else
{
switch (c.CellType)
{
case NPOI.SS.UserModel.CellType.String:
line[c.ColumnIndex] = c.StringCellValue.Replace("'", "").Replace("\"", "").Trim();
break;
case NPOI.SS.UserModel.CellType.Numeric:
if (DateUtil.IsCellDateFormatted(c))
{
line[c.ColumnIndex] = c.DateCellValue.ToString("yyyy-MM-dd HH:mm:ss").Replace("'", "").Replace("\"", "").Trim();
}
else
{
line[c.ColumnIndex] = c.NumericCellValue.ToString().Replace("'", "").Replace("\"", "").Trim();
}
break;
case NPOI.SS.UserModel.CellType.Boolean:
line[c.ColumnIndex] = c.StringCellValue.Replace("'", "").Replace("\"", "").Trim();
break;
case NPOI.SS.UserModel.CellType.Blank:
//line.Add("");
break;
case NPOI.SS.UserModel.CellType.Formula:
line[c.ColumnIndex] = GetFormulaVal(formula, c).Replace("'", "").Replace("\"", "").Trim();
break;
default:
//line.Add("");
break;
}
}
//if (line.Count > columns)
//{ break; }
}
if (WholeLineEmpty(line))
{
continue;
}
ret.Add(line);
}
hssfwb.Close();
}
}
catch (Exception ex)
{
if (hssfwb != null)
{
hssfwb.Close();
}
if (ex.Message.ToUpper().Contains("BIFF5"))
{
ret.Clear();
var line = new List <string>();
line.Add(ex.Message);
ret.Add(line);
}
else
{
logthdinfo(DateTime.Now.ToString() + " Exception on " + wholefn + " :" + ex.Message + "\r\n\r\n");
}
}
return(ret);
}
/// <summary>
///
/// </summary>
/// <param name="fileName"></param>
/// <param name="sheetName"></param>
/// <param name="isFirstRowColumn"></param>
/// <param name="columnTemplate"></param>
/// <param name="requireColumns"></param>
/// <param name="maxRows"></param>
/// <returns></returns>
public static DataTable ExcelToDataTable(string fileName, string sheetName, bool isFirstRowColumn, Dictionary <string, string[]> columnTemplate = null, string[] requireColumns = null, int?maxRows = null)
{
ISheet sheet = null;
DataTable data = new DataTable();
IWorkbook workbook = null;
int startRow = 0;
try
{
using (var fs = new FileStream(fileName, FileMode.Open, FileAccess.Read))
{
try
{
workbook = new XSSFWorkbook(fs);
}
catch
{
workbook = new HSSFWorkbook(fs);
}
}
if (sheetName != null)
{
if (workbook != null)
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
}
else
{
if (workbook != null)
{
sheet = workbook.GetSheetAt(0);
}
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
string cellValue = cell?.StringCellValue?.Trim();
if (!string.IsNullOrWhiteSpace(cellValue))//列名正确性验证
{
if (columnTemplate != null && !columnTemplate.First().Value.Contains(cellValue))
{
throw new Exception($"{columnTemplate.First().Key}不存在列名：{cellValue}！正确列名为：{string.Join(",", columnTemplate.First().Value)}");
}
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
if (maxRows != null)
{
if (rowCount > maxRows)
{
throw new Exception($"请拆分文件，一次最多支持{maxRows}条数据");
}
}
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.Cells.Count == 0 || row.FirstCellNum == -1 || row.Cells.All(d => d.CellType == CellType.Blank))
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
var cellvalue = row.GetCell(j);
if (cellvalue == null || (cellvalue.ToString().Trim() == "0"))
{
if (requireColumns != null && requireColumns.Contains(data.Columns[j].ColumnName))
{
//throw new Exception($"第{i}行，第{j}列,【{data.Columns[j].ColumnName}】不能为空或0，必须填写！");
}
}
if (cellvalue != null)
{
dataRow[j] = cellvalue.ToString().Trim();
}
else
{
dataRow[j] = ""; //string.Empty;
}
}
data.Rows.Add(dataRow);
}
}
workbook?.Close();
return(data);
}
catch (Exception ex)
{
workbook?.Close();
throw new Exception(ex.Message);
}
}
/// <summary>
/// 输出Excel内存流
/// </summary>
/// <param name="dts">数据表集合</param>
/// <returns>内存流</returns>
public static MemoryStream ToExcelStream(this DataTable[] dts)
{
if (dts.IsNullOrLength0())
{
return(new MemoryStream());
}
MemoryStream ms = null;
IWorkbook workbook = null;
try
{
//创建一个工作簿
workbook = new HSSFWorkbook();
for (int k = 0; k < dts.Length; k++)
{
DataTable dt = dts[k];
string sheetName = string.IsNullOrWhiteSpace(dt.TableName) ? $"sheet{k + 1}" : dt.TableName;
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(sheetName);
//创建一行
IRow rowH = sheet.CreateRow(0);
//创建一个单元格
ICell cell = null;
//创建列标题单元格样式
ICellStyle cellTitleStyle = workbook.CreateCellStyle();
cellTitleStyle.Alignment = HorizontalAlignment.Center;
cellTitleStyle.VerticalAlignment = VerticalAlignment.Center;
IFont f = workbook.CreateFont();
//f.Boldweight = (short)FontBoldWeight.Bold;
cellTitleStyle.SetFont(f);
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
//cellTitleStyle.DataFormat = dataFormat.GetFormat("@");
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowH.Cells[col.Ordinal].CellStyle = cellTitleStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
}
//创建一个 IO 流
ms = new MemoryStream();
//写入到流
workbook.Write(ms);
return(ms);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
}
static void Main(string[] args)
{
List <UI> add = new List <UI>();
List <TIME> KX = new List <TIME>();
int k = 0;
Console.Write("数据正在初始化中请稍等..................");
for (int i = 0; i < Properties.Settings.Default.SUM; i += 10)
{
Console.SetCursorPosition(0, 1);//设置光标位置,参数为第几列和第几
Console.Write("当前进度为" + (i / 9000.0).ToString("F5") + "%");
System.Net.ServicePointManager.SecurityProtocol = SecurityProtocolType.Tls12;
string retString = "";
if (Properties.Settings.Default.URL == "Lung")
{
retString = httpGet("https://clinicaltrials.gov/ct2/results/rpc/Ci0yqihHSdCLRi7xUnhgkdNgzwJ?start=" + i + "&length=10");//总共9005
}
else if (Properties.Settings.Default.URL == "Breast")
{
retString = httpGet("https://clinicaltrials.gov/ct2/results/rpc/7i0yqihHSdCLoB1gWwNz0nhgkdNgzwJ?start=" + i + "&length=10");
}
else if (Properties.Settings.Default.URL == "Gastric")
{
retString = httpGet("https://clinicaltrials.gov/ct2/results/rpc/Hi0yqihHSdCLYwNHkw-VJBcGuBcHS?start=" + i + "&length=10");//总共13000
}
else
{
return;
}
if (retString == "")
{
continue;
}
try
{
var QDATA = (JObject)JsonConvert.DeserializeObject(retString);
var DATA = QDATA["data"].Children().ToList();
DATA.ForEach(t =>
{
UI UL = new UI();
UL.TIME = t.Children().ToList()[22].ToString();
UL.NAME = Properties.Settings.Default.URL + " Cancer";//Breast cancer或者Gastric Cancer
add.Add(UL);
Console.WriteLine("正在写入第 " + k++ + "条数据");
});
}
catch
{
continue;
}
}
Console.Write("初始化完成!");
for (int i = 0; i < add.Count; i++)
{
if (add[i].TIME.Contains("January"))
{
add[i].TIME = add[i].TIME.Replace("January", "1").Split(',')[1] + "-" + add[i].TIME.Replace("January", "1").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("January", "1").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("February"))
{
add[i].TIME = add[i].TIME.Replace("February", "2").Split(',')[1] + "-" + add[i].TIME.Replace("February", "2").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("February", "2").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("March"))
{
add[i].TIME = add[i].TIME.Replace("March", "3").Split(',')[1] + "-" + add[i].TIME.Replace("March", "3").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("March", "3").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("April"))
{
add[i].TIME = add[i].TIME.Replace("April", "4").Split(',')[1] + "-" + add[i].TIME.Replace("April", "4").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("April", "4").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("May"))
{
add[i].TIME = add[i].TIME.Replace("May", "5").Split(',')[1] + "-" + add[i].TIME.Replace("May", "5").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("May", "5").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("June"))
{
add[i].TIME = add[i].TIME.Replace("June", "6").Split(',')[1] + "-" + add[i].TIME.Replace("June", "6").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("June", "6").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("July"))
{
add[i].TIME = add[i].TIME.Replace("July", "7").Split(',')[1] + "-" + add[i].TIME.Replace("July", "7").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("July", "7").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("August"))
{
add[i].TIME = add[i].TIME.Replace("August", "8").Split(',')[1] + "-" + add[i].TIME.Replace("August", "8").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("August", "8").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("September"))
{
add[i].TIME = add[i].TIME.Replace("September", "9").Split(',')[1] + "-" + add[i].TIME.Replace("September", "9").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("September", "9").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("October"))
{
add[i].TIME = add[i].TIME.Replace("October", "10").Split(',')[1] + "-" + add[i].TIME.Replace("October", "10").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("October", "10").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("November"))
{
add[i].TIME = add[i].TIME.Replace("November", "11").Split(',')[1] + "-" + add[i].TIME.Replace("November", "11").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("November", "11").Split(',')[0].ToString().Split(' ')[1];
}
if (add[i].TIME.Contains("December"))
{
add[i].TIME = add[i].TIME.Replace("December", "12").Split(',')[1] + "-" + add[i].TIME.Replace("December", "12").Split(',')[0].ToString().Split(' ')[0] + "-" + add[i].TIME.Replace("December", "12").Split(',')[0].ToString().Split(' ')[1];
}
}
string path = AppDomain.CurrentDomain.BaseDirectory + Properties.Settings.Default.Excel;
using (FileStream fs = File.OpenRead(path))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
var TotalIndex = 0;
var TotalBaseIndex = 2;
var CurrentIndex = TotalBaseIndex - 1;
var Sheet = workbook.GetSheetAt(0);
add.ToList().ForEach(t =>
{
TIME TI = new TIME();
TI.YEAR = DateTime.Parse(t.TIME.Replace(" ", "")).Year;
TI.MONTH = DateTime.Parse(t.TIME.Replace(" ", "")).Month;
TI.JD = (TI.MONTH == 1 || TI.MONTH == 2 || TI.MONTH == 3) ? 1 : ((TI.MONTH == 4 || TI.MONTH == 5 || TI.MONTH == 6) ? 2 : ((TI.MONTH == 7 || TI.MONTH == 8 || TI.MONTH == 9) ? 3 : 4));
KX.Add(TI);
CurrentIndex = TotalBaseIndex + TotalIndex++;
Sheet.GetRow(CurrentIndex).CopyRowTo(CurrentIndex + 1);
Sheet.GetRow(CurrentIndex + 1).Height = Sheet.GetRow(CurrentIndex).Height;
Sheet.GetRow(CurrentIndex).Cells[0].SetCellValue(TotalIndex);
Sheet.GetRow(CurrentIndex).Cells[1].SetCellValue(t.NAME);
Sheet.GetRow(CurrentIndex).Cells[2].SetCellValue(t.TIME);
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Green;
Console.WriteLine("正在处理第" + TotalIndex + "条数据，总计" + add.Count + ".");
});
Sheet.RemoveRow(Sheet.GetRow(++CurrentIndex));
var Path = /*AppDomain.CurrentDomain.BaseDirectory*/ @"F:\BASE\" + DateTime.Now.ToLongDateString() + Properties.Settings.Default.URL + "数据表.xls";
using (var FS = File.Create(Path))
{
workbook.Write(FS);
workbook.Close();
}
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("生成文件：" + Path);
}
string path1 = AppDomain.CurrentDomain.BaseDirectory + Properties.Settings.Default.Excel1;
using (FileStream fs = File.OpenRead(path1))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
var TotalIndex = 0;
var TotalBaseIndex = 2;
var CurrentIndex = TotalBaseIndex - 1;
var Sheet = workbook.GetSheetAt(0);
KX.GroupBy(X => X.YEAR).Select(T => new
{
YEAR = T.Key,
JD = T.Key,
COUNT = T.Count()
}).OrderByDescending(x => x.YEAR).ToList().ForEach(t =>
{
CurrentIndex = TotalBaseIndex + TotalIndex++;
Sheet.GetRow(CurrentIndex).CopyRowTo(CurrentIndex + 1);
Sheet.GetRow(CurrentIndex + 1).Height = Sheet.GetRow(CurrentIndex).Height;
Sheet.GetRow(CurrentIndex).Cells[0].SetCellValue(TotalIndex);
Sheet.GetRow(CurrentIndex).Cells[1].SetCellValue(Properties.Settings.Default.URL + " Cancer");
Sheet.GetRow(CurrentIndex).Cells[2].SetCellValue(t.YEAR);
Sheet.GetRow(CurrentIndex).Cells[3].SetCellValue("");
Sheet.GetRow(CurrentIndex).Cells[4].SetCellValue("");
Sheet.GetRow(CurrentIndex).Cells[5].SetCellValue(t.COUNT);
});
Sheet.RemoveRow(Sheet.GetRow(++CurrentIndex));
var Path = /*AppDomain.CurrentDomain.BaseDirectory*/ @"F:\BASE\" + DateTime.Now.ToLongDateString() + Properties.Settings.Default.URL + "年统计.xls";
using (var FS = File.Create(Path))
{
workbook.Write(FS);
workbook.Close();
}
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("生成统计文件：" + Path);
}
using (FileStream fs = File.OpenRead(path1))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
var TotalIndex = 0;
var TotalBaseIndex = 2;
var CurrentIndex = TotalBaseIndex - 1;
var Sheet = workbook.GetSheetAt(0);
KX.GroupBy(X => X.JD).Select(T1 => new
{
JD = T1.Key,
COUNT = T1.Count(),
}).OrderByDescending(x => x.JD).ToList().ForEach(t =>
{
CurrentIndex = TotalBaseIndex + TotalIndex++;
Sheet.GetRow(CurrentIndex).CopyRowTo(CurrentIndex + 1);
Sheet.GetRow(CurrentIndex + 1).Height = Sheet.GetRow(CurrentIndex).Height;
Sheet.GetRow(CurrentIndex).Cells[0].SetCellValue(TotalIndex);
Sheet.GetRow(CurrentIndex).Cells[1].SetCellValue(Properties.Settings.Default.URL + " Cancer");
Sheet.GetRow(CurrentIndex).Cells[2].SetCellValue("");
Sheet.GetRow(CurrentIndex).Cells[3].SetCellValue(t.JD);
Sheet.GetRow(CurrentIndex).Cells[4].SetCellValue("");
Sheet.GetRow(CurrentIndex).Cells[5].SetCellValue(t.COUNT);
});
Sheet.RemoveRow(Sheet.GetRow(++CurrentIndex));
var Path = /*AppDomain.CurrentDomain.BaseDirectory*/ @"F:\BASE\" + DateTime.Now.ToLongDateString() + Properties.Settings.Default.URL + "季度统计.xls";
using (var FS = File.Create(Path))
{
workbook.Write(FS);
workbook.Close();
}
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("生成统计文件：" + Path);
}
using (FileStream fs = File.OpenRead(path1))
{
HSSFWorkbook workbook = new HSSFWorkbook(fs);
var TotalIndex = 0;
var TotalBaseIndex = 2;
var CurrentIndex = TotalBaseIndex - 1;
var Sheet = workbook.GetSheetAt(0);
KX.GroupBy(X => X.MONTH).Select(T2 => new
{
MONTH = T2.Key,
COUNT = T2.Count(),
}).OrderByDescending(x => x.MONTH).ToList().ForEach(t =>
{
CurrentIndex = TotalBaseIndex + TotalIndex++;
Sheet.GetRow(CurrentIndex).CopyRowTo(CurrentIndex + 1);
Sheet.GetRow(CurrentIndex + 1).Height = Sheet.GetRow(CurrentIndex).Height;
Sheet.GetRow(CurrentIndex).Cells[0].SetCellValue(TotalIndex);
Sheet.GetRow(CurrentIndex).Cells[1].SetCellValue(Properties.Settings.Default.URL + " Cancer");
Sheet.GetRow(CurrentIndex).Cells[2].SetCellValue("");
Sheet.GetRow(CurrentIndex).Cells[3].SetCellValue("");
Sheet.GetRow(CurrentIndex).Cells[4].SetCellValue(t.MONTH);
Sheet.GetRow(CurrentIndex).Cells[5].SetCellValue(t.COUNT);
});
Sheet.RemoveRow(Sheet.GetRow(++CurrentIndex));
var Path = /*AppDomain.CurrentDomain.BaseDirectory*/ @"F:\BASE\" + DateTime.Now.ToLongDateString() + Properties.Settings.Default.URL + "月统计.xls";
using (var FS = File.Create(Path))
{
workbook.Write(FS);
workbook.Close();
}
Console.ResetColor();
Console.ForegroundColor = ConsoleColor.Yellow;
Console.WriteLine("生成统计文件：" + Path);
}
Console.ReadKey();
}
/// <summary>
/// 导出EXCEL文件
/// </summary>
/// <param name="xlsName">文件名</param>
/// <param name="tableName">工作薄名称</param>
/// <param name="data">导出的数据源,数据源类在定义时必须有描述特性</param>
public static void ExportExcels <T>(string xlsName, string tableName, List <T> data)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(tableName);
var row = sheet.CreateRow(sheet.LastRowNum);
//创建标题行
int i = 0;
PropertyInfo[] propertyInfos = typeof(T).GetProperties();
foreach (var property in propertyInfos)
{
object[] objs = property.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objs.Length > 0)
{
//创建一个列并且赋值
row.CreateCell(i).SetCellValue(((DescriptionAttribute)objs[0]).Description);
i++;
}
}
//将数据填充到表格当中
int j = sheet.LastRowNum + 1;
foreach (var item in data)
{
int n = 0;
row = sheet.CreateRow(j++);
//获取一项的所有属性
var itemProps = item.GetType().GetProperties();
foreach (var itemPropSub in itemProps)
{
var objs = itemPropSub.GetCustomAttributes(typeof(DescriptionAttribute), true);
if (objs.Length > 0)
{
if (itemPropSub.PropertyType == typeof(System.DateTime))
{
string DateStr = itemPropSub.GetValue(item, null).ToString();
if (DateTime.TryParse(DateStr, out DateTime theDate))
{
row.CreateCell(n).SetCellValue(theDate.ToString("yyyy-MM-dd"));
}
}
else
{
row.CreateCell(n).SetCellValue(itemPropSub.GetValue(item, null) == null ? "" :
itemPropSub.GetValue(item, null).ToString());
}
n++;
}
}
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
FileStream fileStream = new FileStream(xlsName, FileMode.Create, FileAccess.ReadWrite, FileShare.ReadWrite);
ms.WriteTo(fileStream);
ms.Close();
ms.Dispose();
fileStream.Close();
workbook.Close();
}
public int ExportExcelOneByOne(TrayInfoInExcel trayInfoInExcel)
{
int ret = -1;
//获取excel文件信息
FileInfo file = new FileInfo(excelFullName);
//检测文件是否存在
if (!File.Exists(excelFullName))
{
if (CreatExcel() != 0)
{
return(ret);
}
}
else
{
file.IsReadOnly = false;
}
HSSFWorkbook workbook = null;
try
{
using (FileStream fs1 = File.OpenRead(excelFullName))
{
workbook = new HSSFWorkbook(fs1); //实例化Excel工作薄类
ISheet sheet = workbook.GetSheet("Sheet1");
ICellStyle cellstyle = workbook.CreateCellStyle(); //设置垂直居中
cellstyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
//SetValueByRow中自动设序号 +1
//int rowCount = sheet.LastRowNum;
//trayInfoInExcel.num = rowCount;
//设定值
SetValueByRow(ref sheet, trayInfoInExcel, cellstyle);
SetSheetAutoSize(ref sheet);
fs1.Close();
using (FileStream fs2 = File.OpenWrite(excelFullName))
{
workbook.Write(fs2);
workbook.Close();
}
ret = 0;
}
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
finally
{
if (workbook != null)
{
workbook.Close();
}
file.IsReadOnly = false;
}
return(ret);
}
// TODO : Param: send file address
// Apply NPOI library
private static string ExportExcelOperation(DataTable dt, string filePath)
{
string savePath = "";
if (dt != null)
{
try
{
//创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(dt.TableName);
//创建一行
IRow rowH = sheet.CreateRow(0);
//创建一个单元格
ICell cell = null;
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowH.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowH.Cells[col.Ordinal].CellStyle = cellStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 1);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//设置当前路径
// string path = System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName;
//设置新建文件路径及名称
//savePath = filePath + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss") + ".xls";
//创建文件
FileStream file = new FileStream(filePath, FileMode.Create, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
MessageBox.Show(ex.ToString());
}
}
return(savePath);
}
public void TestSetGetProperties()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
HSSFSheet sh = wb1.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFTextbox textbox = patriarch.CreateTextbox(new HSSFClientAnchor()) as HSSFTextbox;
textbox.String = (new HSSFRichTextString("test"));
Assert.AreEqual(textbox.String.String, "test");
textbox.HorizontalAlignment = ((HorizontalTextAlignment)5);
Assert.AreEqual((HorizontalTextAlignment)5, textbox.HorizontalAlignment);
textbox.VerticalAlignment = ((VerticalTextAlignment)6);
Assert.AreEqual((VerticalTextAlignment)6, textbox.VerticalAlignment);
textbox.MarginBottom = (7);
Assert.AreEqual(textbox.MarginBottom, 7);
textbox.MarginLeft = (8);
Assert.AreEqual(textbox.MarginLeft, 8);
textbox.MarginRight = (9);
Assert.AreEqual(textbox.MarginRight, 9);
textbox.MarginTop = (10);
Assert.AreEqual(textbox.MarginTop, 10);
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1);
wb1.Close();
sh = wb2.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
textbox = (HSSFTextbox)patriarch.Children[0];
Assert.AreEqual(textbox.String.String, "test");
Assert.AreEqual(textbox.HorizontalAlignment, (HorizontalTextAlignment)5);
Assert.AreEqual(textbox.VerticalAlignment, (VerticalTextAlignment)6);
Assert.AreEqual(textbox.MarginBottom, 7);
Assert.AreEqual(textbox.MarginLeft, 8);
Assert.AreEqual(textbox.MarginRight, 9);
Assert.AreEqual(textbox.MarginTop, 10);
textbox.String = (new HSSFRichTextString("test1"));
textbox.HorizontalAlignment = HorizontalTextAlignment.Center;
textbox.VerticalAlignment = VerticalTextAlignment.Top;
textbox.MarginBottom = (71);
textbox.MarginLeft = (81);
textbox.MarginRight = (91);
textbox.MarginTop = (101);
Assert.AreEqual(textbox.String.String, "test1");
Assert.AreEqual(textbox.HorizontalAlignment, HorizontalTextAlignment.Center);
Assert.AreEqual(textbox.VerticalAlignment, VerticalTextAlignment.Top);
Assert.AreEqual(textbox.MarginBottom, 71);
Assert.AreEqual(textbox.MarginLeft, 81);
Assert.AreEqual(textbox.MarginRight, 91);
Assert.AreEqual(textbox.MarginTop, 101);
HSSFWorkbook wb3 = HSSFTestDataSamples.WriteOutAndReadBack(wb2);
wb2.Close();
sh = wb3.GetSheetAt(0) as HSSFSheet;
patriarch = sh.DrawingPatriarch as HSSFPatriarch;
textbox = (HSSFTextbox)patriarch.Children[0];
Assert.AreEqual(textbox.String.String, "test1");
Assert.AreEqual(textbox.HorizontalAlignment, HorizontalTextAlignment.Center);
Assert.AreEqual(textbox.VerticalAlignment, VerticalTextAlignment.Top);
Assert.AreEqual(textbox.MarginBottom, 71);
Assert.AreEqual(textbox.MarginLeft, 81);
Assert.AreEqual(textbox.MarginRight, 91);
Assert.AreEqual(textbox.MarginTop, 101);
wb3.Close();
}
/// <summary>
/// 导出EXCEL到流
/// </summary>
/// <param name="stream"></param>
public override void Export(Stream stream)
{
var columns = SheetColumns();
if (columns != null)
{
columns = SortByRange(columns.ToArray());
}
HSSFWorkbook hSSFWorkbook = new HSSFWorkbook();
ISheet sheet = hSSFWorkbook.CreateSheet(string.IsNullOrEmpty(_table.TableName) ? "sheet1" : _table.TableName);
IRow head_row = sheet.GetRow(0) ?? sheet.CreateRow(0);
head_row.HeightInPoints = 20;
var index = 0;
List <int> columnWidthlist = new List <int>();
//表头
if (columns == null)
{
columns = new ExcelSheetContext[_table.Columns.Count];
for (int i = 0; i < _table.Columns.Count; i++)
{
columns[i] = new ExcelSheetContext
{
ColumnName = _table.Columns[i].ColumnName,
Column_Index = i,
};
}
}
foreach (var item in columns)
{
string titlename = string.IsNullOrEmpty(item.Cell_Title) ? item.ColumnName : item.Cell_Title;
if (string.IsNullOrEmpty(titlename))
{
continue;
}
ICell cell = head_row.CreateCell(index);
cell.SetCellValue(titlename);
cell.CellStyle = GetTitleStyle(hSSFWorkbook);
index++;
}
//单元格
for (int j = 0; j < _table.Rows.Count; j++)
{
IRow row2 = sheet.GetRow(j + 1) ?? sheet.CreateRow(j + 1);
row2.HeightInPoints = 20;
for (int k = 0; k < columns.Length; k++)
{
var col = columns[k];
ICell cell2 = row2.CreateCell(k);
cell2.CellStyle = GetCellStyle(hSSFWorkbook);
int num = Encoding.Default.GetByteCount(_table.Rows[j][col.ColumnName].ToString()) > Encoding.Default.GetByteCount(col.ColumnName) ?
Encoding.Default.GetByteCount(_table.Rows[j][col.ColumnName].ToString()) :
Encoding.Default.GetByteCount(col.ColumnName);
sheet.SetColumnWidth(k, (num + 1) * 256);
object value = col.Convert(_table.Rows[j][col.ColumnName]);
if (value == null)
{
SetCellValue(cell2, string.Empty, typeof(string));
}
else
{
SetCellValue(cell2, value, value.GetType());
}
}
}
hSSFWorkbook.Write(stream);
//hSSFWorkbook.Dispose();
hSSFWorkbook.Close();
}
public void TestReallyEmbedSomething()
{
HSSFWorkbook wb1 = new HSSFWorkbook();
ISheet sheet = wb1.CreateSheet();
HSSFPatriarch patriarch = sheet.CreateDrawingPatriarch() as HSSFPatriarch;
byte[] pictureData = HSSFTestDataSamples.GetTestDataFileContent("logoKarmokar4.png");
byte[] picturePPT = POIDataSamples.GetSlideShowInstance().ReadFile("clock.jpg");
int imgIdx = wb1.AddPicture(pictureData, PictureType.PNG);
POIFSFileSystem pptPoifs = GetSamplePPT();
int pptIdx = wb1.AddOlePackage(pptPoifs, "Sample-PPT", "sample.ppt", "sample.ppt");
POIFSFileSystem xlsPoifs = GetSampleXLS();
int imgPPT = wb1.AddPicture(picturePPT, PictureType.JPEG);
int xlsIdx = wb1.AddOlePackage(xlsPoifs, "Sample-XLS", "sample.xls", "sample.xls");
int txtIdx = wb1.AddOlePackage(GetSampleTXT(), "Sample-TXT", "sample.txt", "sample.txt");
int rowoffset = 5;
int coloffset = 5;
ICreationHelper ch = wb1.GetCreationHelper();
HSSFClientAnchor anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
anchor.SetAnchor((short)(2 + coloffset), 1 + rowoffset, 0, 0, (short)(3 + coloffset), 5 + rowoffset, 0, 0);
anchor.AnchorType = (/*setter*/ AnchorType.DontMoveAndResize);
patriarch.CreateObjectData(anchor, pptIdx, imgPPT);
anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
anchor.SetAnchor((short)(5 + coloffset), 1 + rowoffset, 0, 0, (short)(6 + coloffset), 5 + rowoffset, 0, 0);
anchor.AnchorType = (/*setter*/ AnchorType.DontMoveAndResize);
patriarch.CreateObjectData(anchor, xlsIdx, imgIdx);
anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
anchor.SetAnchor((short)(3 + coloffset), 10 + rowoffset, 0, 0, (short)(5 + coloffset), 11 + rowoffset, 0, 0);
anchor.AnchorType = (/*setter*/ AnchorType.DontMoveAndResize);
patriarch.CreateObjectData(anchor, txtIdx, imgIdx);
anchor = (HSSFClientAnchor)ch.CreateClientAnchor();
anchor.SetAnchor((short)(1 + coloffset), -2 + rowoffset, 0, 0, (short)(7 + coloffset), 14 + rowoffset, 0, 0);
anchor.AnchorType = (/*setter*/ AnchorType.DontMoveAndResize);
HSSFSimpleShape circle = patriarch.CreateSimpleShape(anchor);
circle.ShapeType = (/*setter*/ HSSFSimpleShape.OBJECT_TYPE_OVAL);
circle.IsNoFill = (/*setter*/ true);
//if (false)
//{
// FileStream fos = new FileStream("embed.xls", FileMode.Create);
// wb.Write(fos);
// fos.Close();
//}
HSSFWorkbook wb2 = HSSFTestDataSamples.WriteOutAndReadBack(wb1 as HSSFWorkbook);
wb1.Close();
MemoryStream bos = new MemoryStream();
HSSFObjectData od = wb2.GetAllEmbeddedObjects()[0];
Ole10Native ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory());
bos = new MemoryStream();
pptPoifs.WriteFileSystem(bos);
Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray()));
od = wb2.GetAllEmbeddedObjects()[1];
ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory());
bos = new MemoryStream();
xlsPoifs.WriteFileSystem(bos);
Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, bos.ToArray()));
od = wb2.GetAllEmbeddedObjects()[2];
ole10 = Ole10Native.CreateFromEmbeddedOleObject((DirectoryNode)od.GetDirectory());
Assert.IsTrue(Arrays.Equals(ole10.DataBuffer, GetSampleTXT()));
xlsPoifs.Close();
pptPoifs.Close();
wb2.Close();
}
public void TestResultEqualsToAbstractShape()
{
HSSFWorkbook wb = new HSSFWorkbook();
HSSFSheet sh = wb.CreateSheet() as HSSFSheet;
HSSFPatriarch patriarch = sh.CreateDrawingPatriarch() as HSSFPatriarch;
HSSFComment comment = patriarch.CreateCellComment(new HSSFClientAnchor()) as HSSFComment;
HSSFRow row = sh.CreateRow(0) as HSSFRow;
HSSFCell cell = row.CreateCell(0) as HSSFCell;
cell.CellComment = (comment);
CommentShape commentShape = HSSFTestModelHelper.CreateCommentShape(1025, comment);
Assert.AreEqual(comment.GetEscherContainer().ChildRecords.Count, 5);
Assert.AreEqual(commentShape.SpContainer.ChildRecords.Count, 5);
//sp record
byte[] expected = commentShape.SpContainer.GetChild(0).Serialize();
byte[] actual = comment.GetEscherContainer().GetChild(0).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = commentShape.SpContainer.GetChild(2).Serialize();
actual = comment.GetEscherContainer().GetChild(2).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = commentShape.SpContainer.GetChild(3).Serialize();
actual = comment.GetEscherContainer().GetChild(3).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
expected = commentShape.SpContainer.GetChild(4).Serialize();
actual = comment.GetEscherContainer().GetChild(4).Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
ObjRecord obj = comment.GetObjRecord();
ObjRecord objShape = commentShape.ObjRecord;
expected = obj.Serialize();
actual = objShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
//assertArrayEquals(expected, actual);
TextObjectRecord tor = comment.GetTextObjectRecord();
TextObjectRecord torShape = commentShape.TextObjectRecord;
expected = tor.Serialize();
actual = torShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
NoteRecord note = comment.NoteRecord;
NoteRecord noteShape = commentShape.NoteRecord;
expected = note.Serialize();
actual = noteShape.Serialize();
Assert.AreEqual(expected.Length, actual.Length);
Assert.IsTrue(Arrays.Equals(expected, actual));
wb.Close();
}
public void ExportExcelDouble(DataTable dt)
{
try
{
//创建一个工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建一个 sheet 表
ISheet sheet = workbook.CreateSheet(dt.TableName);
//创建第一行
IRow rowFirst = sheet.CreateRow(0);
//创建第二行
IRow rowSecond = sheet.CreateRow(1);
//创建一个单元格
ICell cell = null;
//创建单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.VerticalAlignment = VerticalAlignment.Justify; //垂直对齐(默认应该为center，如果center无效则用justify)
cellStyle.Alignment = HorizontalAlignment.Center; //水平对齐
//创建格式
IDataFormat dataFormat = workbook.CreateDataFormat();
//设置为文本格式，也可以为 text，即 dataFormat.GetFormat("text");
cellStyle.DataFormat = dataFormat.GetFormat("@");
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 2));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 3, 5));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 6, 8));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 10, 12));
rowFirst.CreateCell(0).SetCellValue("考试内容");
rowFirst.Cells[0].CellStyle = cellStyle;
rowFirst.CreateCell(3).SetCellValue("考试要求");
rowFirst.Cells[1].CellStyle = cellStyle;
rowFirst.CreateCell(6).SetCellValue("难度值");
rowFirst.Cells[2].CellStyle = cellStyle;
rowFirst.CreateCell(9).SetCellValue("题型");
rowFirst.Cells[3].CellStyle = cellStyle;
rowFirst.CreateCell(10).SetCellValue("题目来源");
rowFirst.Cells[4].CellStyle = cellStyle;
//设置列名
foreach (DataColumn col in dt.Columns)
{
//创建单元格并设置单元格内容
rowSecond.CreateCell(col.Ordinal).SetCellValue(col.Caption);
//设置单元格格式
rowSecond.Cells[col.Ordinal].CellStyle = cellStyle;
}
//写入数据
for (int i = 0; i < dt.Rows.Count; i++)
{
//跳过第一行，第一行为列名
IRow row = sheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
cell = row.CreateCell(j);
cell.SetCellValue(dt.Rows[i][j].ToString());
cell.CellStyle = cellStyle;
}
}
//设置导出文件路径
string path = HttpContext.Current.Server.MapPath("/ImportExcel/");
//设置新建文件路径及名称
string savePath = path + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";
//创建文件
FileStream file = new FileStream(savePath, FileMode.CreateNew, FileAccess.Write);
//创建一个 IO 流
MemoryStream ms = new MemoryStream();
//写入到流
workbook.Write(ms);
//转换为字节数组
byte[] bytes = ms.ToArray();
file.Write(bytes, 0, bytes.Length);
file.Flush();
//还可以调用下面的方法，把流输出到浏览器下载
OutputClient(bytes);
//释放资源
bytes = null;
ms.Close();
ms.Dispose();
file.Close();
file.Dispose();
workbook.Close();
sheet = null;
workbook = null;
}
catch (Exception ex)
{
}
}
static void Main(string[] args)
{
var tmpPath = @"D:\C_Spaces\ToolsLibrary\GenerateArea\Excel2003.xls";
if (File.Exists(tmpPath))
{
File.Delete(tmpPath);
}
// if (!File.Exists(tmpPath))
// {
// var workbook2003 = new HSSFWorkbook();
// workbook2003.CreateSheet("Sheet1");
// workbook2003.CreateSheet("Sheet2");
// workbook2003.CreateSheet("Sheet3");
// var file2003 = new FileStream(tmpPath , FileMode.Create);
// workbook2003.Write(file2003);
// file2003.Close();
// workbook2003.Close();
// }
// tmpPath = @"D:\C_Spaces\ToolsLibrary\GenerateArea\Excel2007.xls";
//
// var workbook2007 = new XSSFWorkbook();
// workbook2007.CreateSheet("Sheet1");
// workbook2007.CreateSheet("Sheet2");
// workbook2007.CreateSheet("Sheet3");
// var fs2007 = new FileStream(tmpPath , FileMode.Create);
// workbook2007.Write(fs2007);
// fs2007.Close();
// workbook2007.Close();
var workbook2003 = new HSSFWorkbook();
workbook2003.CreateSheet("Sheet1");
var sheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1");
for (int i = 0; i < 10; i++)
{
sheetOne.CreateRow(i);
}
var sheetRow = (HSSFRow)sheetOne.GetRow(0);
var sheetCell = new HSSFCell[10];
for (int i = 0; i < 10; i++)
{
sheetCell[i] = (HSSFCell)sheetRow.CreateCell(i);
}
sheetCell[0].SetCellValue(true);
sheetCell[1].SetCellValue(0.111111);
sheetCell[2].SetCellValue("Excel2003");
sheetCell[3].SetCellValue("123456789132456798");
for (int i = 4; i < 10; i++)
{
sheetCell[i].SetCellValue(i);
}
var fs = new FileStream(tmpPath, FileMode.Create);
workbook2003.Write(fs);
fs.Close();
workbook2003.Close();
}
public int line_num = 0; //接收到多少行
private void serialPort1_DataReceived(object sender, SerialDataReceivedEventArgs e) //对于串口要接收大量数据不能用定时获取的方式
{
Thread.Sleep(serial_read_dealy); //等待串口数据全部到达，如果出现乱码就加长些
if (serialPort1.IsOpen) //serialPort1.IsOpen&&serial_temp!=""
{
line_num++;
serial = serialPort1.ReadExisting();
serialPort1.DiscardInBuffer();//记得清空串口，不然容易留到下次接收导致乱码
LM_print(serial);
if (draw_open)
{
DATA_SPLIT A = new DATA_SPLIT();
if (form2.serial_read_way_0 == "自由捕获")
{
A = GetData_Free(serial);
}
else
{
A = GetData_Keyword(serial);
}
line_var = A.DATA + 1;
for (int i = 0; i < line_var; i++)
{
if (line_var_first)
{
coor[i, coor_var - 1, 0] = 0; //ms
time_temp = Environment.TickCount; //系统启功后到当前的时间
}
else
{
coor[i, coor_var - 1, 0] = Environment.TickCount - time_temp;//开始画图后到现在的时间
}
coor[i, coor_var - 1, 1] = Convert.ToDouble(A.data_split[i]);
for (int i_0 = 0; i_0 < coor_var - 1; i_0++)
{
coor[i, i_0, 0] = coor[i, i_0 + 1, 0];
coor[i, i_0, 1] = coor[i, i_0 + 1, 1];
if (auto_limit)
{
if (coor[i, i_0 + 1, 1] < var_min && coor[i, i_0 + 1, 1] != 0)
{
var_min = coor[i, i_0 + 1, 1];
}
if (coor[i, i_0 + 1, 1] > var_max)
{
var_max = coor[i, i_0 + 1, 1] + 1;
var_min = var_max - 1;//防止等于0
}
}
}
line_var_first = false;
}
/*************************散点图**********************************/
DATA_SPLIT B = GetData_Keyword_SD(serial);
sd_var = B.DATA + 1;
for (int i = 0; i < sd_var; i++)
{
coor_sd[i, coor_var_sd - 1, 0] = Convert.ToDouble(B.data_split[i]);
coor_sd[i, coor_var_sd - 1, 1] = Convert.ToDouble(B.data_split_Y[i]);
for (int i_0 = 0; i_0 < coor_var_sd - 1; i_0++)
{
coor_sd[i, i_0, 0] = coor_sd[i, i_0 + 1, 0];
coor_sd[i, i_0, 1] = coor_sd[i, i_0 + 1, 1];
if (coor_sd[i, i_0 + 1, 1] < var_min_sdy && coor_sd[i, i_0 + 1, 1] != 0)
{
var_min_sdy = coor_sd[i, i_0 + 1, 1];
}
if (coor_sd[i, i_0 + 1, 1] > var_max_sdy)
{
var_max_sdy = coor_sd[i, i_0 + 1, 1] + 1;
var_min_sdy = var_max_sdy - 1;//防止等于0
}
if (coor_sd[i, i_0 + 1, 0] < var_min_sdx && coor_sd[i, i_0 + 1, 0] != 0)
{
var_min_sdx = coor_sd[i, i_0 + 1, 0];
}
if (coor_sd[i, i_0 + 1, 0] > var_max_sdx)
{
var_max_sdx = coor_sd[i, i_0 + 1, 0] + 1;
var_min_sdx = var_max_sdx - 1;//防止等于0
}
}
}
}
//***********************************数据记录与保存****************************************************/
if (excel_record)
{
label_excel_record_val.Text = "已记录\n折线" + row_var + "条\n点阵" + row_var_sd + "条";
HSSFSheet Sheet1 = (HSSFSheet)workbook2003.GetSheet("散点图"); //获取名称为Sheet1的工作表
HSSFSheet Sheet2 = (HSSFSheet)workbook2003.GetSheet("数据可视化"); //获取名称为Sheet1的工作表
row_var_sd++;
if (excel_first_sd)
{
for (int i = 0; i < sd_var; i++)//散点图
{
if (i > 0)
{
Sheet1.GetRow(0).CreateCell(0 + i * 3).SetCellValue("点" + i + " X坐标");
Sheet1.GetRow(0).CreateCell(1 + i * 3).SetCellValue("点" + i + "Y坐标");
}
else
{
Sheet1.CreateRow(0).CreateCell(0 + i * 3).SetCellValue("点" + i + " X坐标");
Sheet1.GetRow(0).CreateCell(1 + i * 3).SetCellValue("点" + i + "Y坐标");
}
}
excel_first_sd = !excel_first_sd;
}
for (int i = 0; i < sd_var; i++)
{
if (i > 0)
{
Sheet1.GetRow(row_var_sd).CreateCell(0 + i * 3).SetCellValue(coor_sd[i, coor_var_sd - 1, 0]);
Sheet1.GetRow(row_var_sd).CreateCell(1 + i * 3).SetCellValue(coor_sd[i, coor_var_sd - 1, 1]);
}
else
{
Sheet1.CreateRow(row_var_sd).CreateCell(0).SetCellValue(coor_sd[0, coor_var_sd - 1, 0]);
Sheet1.GetRow(row_var_sd).CreateCell(1).SetCellValue(coor_sd[0, coor_var_sd - 1, 1]);//已经创造过的不能再用create,会被覆盖
}
}
if (line_var > 0)
{
row_var++;
Sheet2.CreateRow(0).CreateCell(0).SetCellValue("时间（秒）");
for (int i = 0; i < line_var; i++)//数据可视化
{
Sheet2.GetRow(0).CreateCell(i + 1).SetCellValue("数据 " + i);
}
Sheet2.CreateRow(row_var).CreateCell(0).SetCellValue((coor[0, coor_var - 1, 0] * 0.001));
for (int i = 0; i < line_var; i++)
{
Sheet2.GetRow(row_var).CreateCell(i + 1).SetCellValue(coor[i, coor_var - 1, 1]);
}
}
}
if (ecxel_save)//导出文件
{
FileStream file2003 = new FileStream(@file_name, FileMode.Create);
workbook2003.Write(file2003);
file2003.Close();
workbook2003.Close();
ecxel_save = false;
}
}
}
/// <summary>
/// 从Excel文件读取并转换到数据表
/// </summary>
/// <param name="fileName">文件名</param>
/// <param name="sheetName">工作表名</param>
/// <param name="isFirstRowColumn">是否第1行列名</param>
/// <returns>数据表</returns>
public static DataTable ToDataTableFromExcelFile(this string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
if (string.IsNullOrWhiteSpace(fileName))
{
return(null);
}
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = 0;
FileStream fs = null;
IWorkbook workbook = null;
try
{
fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
string expandName = fileName.FileExpandName();
switch (expandName)
{
case ".xlsx": // 2007版本
workbook = new XSSFWorkbook(fs);
break;
case ".xls": // 2003版本
workbook = new HSSFWorkbook(fs);
break;
default:
throw new NotSupportedException($"不支持的{expandName}");
}
if (sheetName == null)
{
sheet = workbook.GetSheetAt(0);
}
else
{
sheet = workbook.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw new Exception(ex.Message, ex);
}
finally
{
if (workbook != null)
{
workbook.Close();
workbook = null;
}
if (fs != null)
{
fs.Close();
fs.Dispose();
fs = null;
}
}
}
/// <summary>
/// Excel导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <T> ImportFromExcel(string filePath)
{
string absoluteFilePath = GlobalContext.HostingEnvironment.ContentRootPath + filePath.Replace(Path.AltDirectorySeparatorChar, Path.DirectorySeparatorChar);
List <T> list = new List <T>();
HSSFWorkbook hssfWorkbook = null;
XSSFWorkbook xssWorkbook = null;
ISheet sheet = null;
using (FileStream file = new FileStream(absoluteFilePath, FileMode.Open, FileAccess.Read))
{
switch (Path.GetExtension(filePath))
{
case ".xls":
hssfWorkbook = new HSSFWorkbook(file);
sheet = hssfWorkbook.GetSheetAt(0);
break;
case ".xlsx":
xssWorkbook = new XSSFWorkbook(file);
sheet = xssWorkbook.GetSheetAt(0);
break;
default:
throw new Exception("不支持的文件格式");
}
}
IRow columnRow = sheet.GetRow(1); // 第二行为字段名
Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>();
for (int j = 0; j < columnRow.LastCellNum; j++)
{
ICell cell = columnRow.GetCell(j);
PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
if (propertyInfo != null)
{
mapPropertyInfoDict.Add(j, propertyInfo);
}
}
for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
T entity = new T();
for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++)
{
if (mapPropertyInfoDict.ContainsKey(j))
{
if (row.GetCell(j) != null)
{
PropertyInfo propertyInfo = mapPropertyInfoDict[j];
switch (propertyInfo.PropertyType.ToString())
{
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int16":
case "System.Nullable`1[System.Int16]":
mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int32":
case "System.Nullable`1[System.Int32]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
break;
default:
case "System.String":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
break;
}
}
}
}
list.Add(entity);
}
hssfWorkbook?.Close();
xssWorkbook?.Close();
return(list);
}
public MemoryStream GetMemoryStream()
{
MemoryStream stream = null;
try
{
IWorkbook workBook = new HSSFWorkbook();
for (int g = 0; g < _ds.Tables.Count; g++)
{
DataTable dt = _ds.Tables[g];
ISheet sheet = workBook.CreateSheet(dt.TableName);
//处理表格标题
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue(dt.TableName);
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dt.Columns.Count - 1));
row.Height = 500;
ICellStyle cellStyle = workBook.CreateCellStyle();
IFont font = workBook.CreateFont();
font.FontName = "微软雅黑";
font.FontHeightInPoints = 17;
cellStyle.SetFont(font);
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.Alignment = HorizontalAlignment.Center;
row.Cells[0].CellStyle = cellStyle;
//处理表格列头
row = sheet.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
row.Height = 350;
sheet.AutoSizeColumn(i);
}
//处理数据内容
for (int i = 0; i < dt.Rows.Count; i++)
{
row = sheet.CreateRow(2 + i);
row.Height = 250;
for (int j = 0; j < dt.Columns.Count; j++)
{
row.CreateCell(j).SetCellValue(dt.Rows[i][j].ToString());
sheet.SetColumnWidth(j, 256 * 15);
}
}
}
stream = new MemoryStream();
workBook.Write(stream);
workBook.Close();
}
catch (Exception ex)
{
throw ex;
}
return(stream);
}