/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(List <T> lists, ExcelConfig excelConfig)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = type.GetProperties();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "刘晓雷"; //填加xls文件作者信息
si.ApplicationName = "力软信息"; //填加xls文件创建程序信息
si.LastAuthor = "刘晓雷"; //填加xls文件最后保存者信息
si.Comments = "刘晓雷"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[properties.Length];
string[] arrColName = new string[properties.Length]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[properties.Length]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
int i = 0;
foreach (PropertyInfo column in properties)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(column.Name).Length;
arrColName[i] = column.Name;
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == column.Name);
if (columnentity != null)
{
arrColName[i] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[i] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
}
}
arryColumStyle[i] = columnStyle;
i++;
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (T item in lists)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, lists.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
int headIndex = 0;
foreach (PropertyInfo column in properties)
{
headerRow.CreateCell(headIndex).SetCellValue(arrColName[headIndex]);
headerRow.GetCell(headIndex).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(headIndex, (arrColWidth[headIndex] + 1) * 256);
headIndex++;
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int ordinal = 0;
foreach (PropertyInfo column in properties)
{
ICell newCell = dataRow.CreateCell(ordinal);
newCell.CellStyle = arryColumStyle[ordinal];
string drValue = column.GetValue(item, null) == null ? "" : column.GetValue(item, null).ToString();
SetCell(newCell, dateStyle, column.PropertyType, drValue);
ordinal++;
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel文件（无表头）另外的是有表头的
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="strFileName">保存位置</param>
/// <param name="strSheetName">工作表名称</param>
/// <Author>CallmeYhz 2015-11-26 10:13:09</Author>
public static void MyExport(DataTable dtSource, string strHeaderText, string strFileName, string strSheetName, string[] oldColumnNames, string[] newColumnNames)
{
if (strSheetName == "")
{
strSheetName = "Sheet";
}
MemoryStream getms = new MemoryStream();
#region 为getms赋值
if (oldColumnNames.Length != newColumnNames.Length)
{
getms = new MemoryStream();
}
HSSFWorkbook workbook = new HSSFWorkbook();
//HSSFSheet sheet = workbook.CreateSheet();// workbook.CreateSheet();
ISheet sheet = workbook.CreateSheet(strSheetName);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "http://....../";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
if (HttpContext.Current.Session["realname"] != null)
{
si.Author = HttpContext.Current.Session["realname"].ToString();
}
else
{
if (HttpContext.Current.Session["username"] != null)
{
si.Author = HttpContext.Current.Session["username"].ToString();
}
} //填加xls文件作者信息
si.ApplicationName = "NPOI"; //填加xls文件创建程序信息
si.LastAuthor = "OA系统"; //填加xls文件最后保存者信息
si.Comments = "OA系统自动创建文件"; //填加xls文件作者信息
si.Title = strHeaderText; //填加xls文件标题信息
si.Subject = strHeaderText; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
#region 取得列宽
int[] arrColWidth = new int[oldColumnNames.Length];
for (int i = 0; i < oldColumnNames.Length; i++)
{
arrColWidth[i] = Encoding.GetEncoding(936).GetBytes(newColumnNames[i]).Length;
}
/*
* 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 < oldColumnNames.Length; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][oldColumnNames[j]].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
/*
* 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;
* }
* }
* */
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet(strSheetName + ((int)rowIndex / 65535).ToString());
}
#region 列头及样式
{
//HSSFRow headerRow = sheet.CreateRow(1);
IRow headerRow = sheet.CreateRow(0);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int i = 0; i < oldColumnNames.Length; i++)
{
headerRow.CreateCell(i).SetCellValue(newColumnNames[i]);
headerRow.GetCell(i).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(i, (arrColWidth[i] + 1) * 256);
}
/*
* foreach (DataColumn column in dtSource.Columns)
* {
* headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
* headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
*
* //设置列宽
* sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
* }
* */
}
#endregion
rowIndex = 1;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
//foreach (DataColumn column in dtSource.Columns)
for (int i = 0; i < oldColumnNames.Length; i++)
{
ICell newCell = dataRow.CreateCell(i);
string drValue = row[oldColumnNames[i]].ToString();
switch (dtSource.Columns[oldColumnNames[i]].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 (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
sheet = null;
workbook = null;
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
getms = ms;
}
#endregion
using (MemoryStream ms = getms)
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
byte[] data = ms.ToArray();
fs.Write(data, 0, data.Length);
fs.Flush();
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static MemoryStream ExportMemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
for (int i = 0; i < dtSource.Columns.Count;)
{
bool IsExists = false;
DataColumn column = dtSource.Columns[i];
for (int j = 0; j < excelConfig.ColumnEntity.Count; j++)
{
if (excelConfig.ColumnEntity[j].Column == column.ColumnName)
{
IsExists = true;
break;
}
}
if (!IsExists)
{
dtSource.Columns.Remove(column);
}
else
{
i++;
}
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "江苏华康"; //填加xls文件作者信息
si.ApplicationName = "江苏华康"; //填加xls文件创建程序信息
si.LastAuthor = "江苏华康"; //填加xls文件最后保存者信息
si.Comments = "江苏华康"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
#region 设置标题样式
ICellStyle headStyle = workbook.CreateCellStyle();
int[] arrColWidth = new int[dtSource.Columns.Count];
string[] arrColName = new string[dtSource.Columns.Count]; //列名
ICellStyle[] arryColumStyle = new ICellStyle[dtSource.Columns.Count]; //样式表
headStyle.Alignment = HorizontalAlignment.Center; // ------------------
if (excelConfig.Background != new Color())
{
if (excelConfig.Background != new Color())
{
headStyle.FillPattern = FillPattern.SolidForeground;
headStyle.FillForegroundColor = GetXLColour(workbook, excelConfig.Background);
}
}
IFont font = workbook.CreateFont();
font.FontHeightInPoints = excelConfig.TitlePoint;
if (excelConfig.ForeColor != new Color())
{
font.Color = GetXLColour(workbook, excelConfig.ForeColor);
}
font.Boldweight = 700;
headStyle.SetFont(font);
#endregion
#region 列头及样式
ICellStyle cHeadStyle = workbook.CreateCellStyle();
cHeadStyle.Alignment = HorizontalAlignment.Center; // ------------------
IFont cfont = workbook.CreateFont();
cfont.FontHeightInPoints = excelConfig.HeadPoint;
cHeadStyle.SetFont(cfont);
#endregion
#region 设置内容单元格样式
foreach (DataColumn item in dtSource.Columns)
{
ICellStyle columnStyle = workbook.CreateCellStyle();
columnStyle.Alignment = HorizontalAlignment.Center;
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
arrColName[item.Ordinal] = item.ColumnName.ToString();
if (excelConfig.ColumnEntity != null)
{
ColumnEntity columnentity = excelConfig.ColumnEntity.Find(t => t.Column == item.ColumnName);
if (columnentity != null)
{
arrColName[item.Ordinal] = columnentity.ExcelColumn;
if (columnentity.Width != 0)
{
arrColWidth[item.Ordinal] = columnentity.Width;
}
if (columnentity.Background != new Color())
{
if (columnentity.Background != new Color())
{
columnStyle.FillPattern = FillPattern.SolidForeground;
columnStyle.FillForegroundColor = GetXLColour(workbook, columnentity.Background);
}
}
if (columnentity.Font != null || columnentity.Point != 0 || columnentity.ForeColor != new Color())
{
IFont columnFont = workbook.CreateFont();
columnFont.FontHeightInPoints = 10;
if (columnentity.Font != null)
{
columnFont.FontName = columnentity.Font;
}
if (columnentity.Point != 0)
{
columnFont.FontHeightInPoints = columnentity.Point;
}
if (columnentity.ForeColor != new Color())
{
columnFont.Color = GetXLColour(workbook, columnentity.ForeColor);
}
columnStyle.SetFont(font);
}
columnStyle.Alignment = getAlignment(columnentity.Alignment);
}
}
arryColumStyle[item.Ordinal] = columnStyle;
}
if (excelConfig.IsAllSizeColumn)
{
#region 根据列中最长列的长度取得列宽
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
if (arrColWidth[j] != 0)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
}
#endregion
}
#endregion
#region 填充数据
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
if (excelConfig.Title != null)
{
IRow headerRow = sheet.CreateRow(0);
if (excelConfig.TitleHeight != 0)
{
headerRow.Height = (short)(excelConfig.TitleHeight * 20);
}
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(excelConfig.Title);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); // ------------------
}
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
#region 如果设置了列标题就按列标题定义列头，没定义直接按字段名输出
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(arrColName[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = cHeadStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
#endregion
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = arryColumStyle[column.Ordinal];
string drValue = row[column].ToString();
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
/// <param name="columnNames">列名</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string[] columnNames)
{
if (columnNames != null && columnNames.Length != dtSource.Columns.Count)
{
throw new ArgumentException("参数不正确:columnNames,数组元素的个数需要和数据源列的数量相同!");
}
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "上海驰亚防伪科技有限公司";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "技术部";
si.Title = strHeaderText;
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] 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 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
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, dtSource.Columns.Count - 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);
if (columnNames == null)
{
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000)
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 40000);
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
else
{
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(columnNames[column.Ordinal]);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
if (((arrColWidth[column.Ordinal] + 1) * 256) >= 40000)
{
sheet.SetColumnWidth(column.Ordinal, 40000);
}
else
{
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string 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 (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public void ExportToExcel <T>(IEnumerable <T> data, string fullPath, ExportOption exportOption = null)
{
data.ThrowIfNull(nameof(data));
fullPath.ThrowIfNull(nameof(fullPath));
exportOption = exportOption ?? new ExportOption();
List <T> list = data.ToList();
#region create workbook
IWorkbook workbook;
if (fullPath.EndsWith("xlsx", StringComparison.OrdinalIgnoreCase))
{
if (File.Exists(fullPath))
{
workbook = new XSSFWorkbook(new FileStream(fullPath, FileMode.OpenOrCreate));
}
else
{
workbook = new XSSFWorkbook();
}
}
else
{
if (File.Exists(fullPath))
{
workbook = new HSSFWorkbook(new FileStream(fullPath, FileMode.OpenOrCreate));
}
else
{
workbook = new HSSFWorkbook();
}
}
#endregion
#region cell style
Dictionary <Type, ICellStyle> cellStyles = new Dictionary <Type, ICellStyle>();
IDataFormat dataFormat = workbook.CreateDataFormat();
ICellStyle dateTimeCellStyle = workbook.CreateCellStyle();
dateTimeCellStyle.DataFormat = dataFormat.GetFormat(exportOption.DateFormat ?? "yyyy-MM-dd HH:mm:ss");
cellStyles[typeof(DateTime)] = dateTimeCellStyle;
if (!exportOption.NumberFormat.IsEmpty())
{
ICellStyle doubleCellStyle = workbook.CreateCellStyle();
doubleCellStyle.DataFormat = dataFormat.GetFormat(exportOption.NumberFormat);
cellStyles[typeof(double)] = doubleCellStyle;
}
#endregion
#region create sheet
ISheet exportSheet;
string sheetName = exportOption.SheetName.IsEmpty() ? "sheet1" : exportOption.SheetName;
for (int i = 0; i < workbook.NumberOfSheets; i++)
{
ISheet sheet = workbook.GetSheetAt(i);
if (sheet.SheetName == sheetName)
{
workbook.RemoveSheetAt(i);
break;
}
}
exportSheet = workbook.CreateSheet(sheetName);
#endregion
#region create header
IRow row = exportSheet.CreateRow(0);
PropertyInfo[] propertyInfos = typeof(T).GetProperties(BindingFlags.Public | BindingFlags.Instance);
for (int i = 0; i < propertyInfos.Length; i++)
{
ICell cell = row.CreateCell(i);
var prop = propertyInfos[i];
var headerAttr = prop.GetCustomAttribute <ExcelHeaderAttribute>();
cell.SetCellValue(headerAttr?.Name ?? prop.Name);
}
#endregion
#region export data
for (int i = 0; i < list.Count; i++)
{
T t = list[i];
IRow sheetRow = exportSheet.CreateRow(i + 1);
for (int j = 0; j < propertyInfos.Length; j++)
{
object cellValue = propertyInfos[j].GetValue(t);
ICell cell = sheetRow.CreateCell(j);
if (cellValue == null)
{
cell.SetCellValue("");
}
else
{
SetCellValue(cell, cellValue, cellStyles);
}
}
}
#endregion
for (int i = 0; i < propertyInfos.Length; i++)
{
exportSheet.AutoSizeColumn(i);
}
using (FileStream fileStream = new FileStream(fullPath, FileMode.OpenOrCreate))
{
workbook.Write(fileStream);
}
workbook?.Close();
}
/// <summary>
/// 导出文件
/// </summary>
/// <param name="list">数据集合List</param>
/// <param name="head">列集合</param>
/// <param name="title">导出文件名称</param>
/// <param name="rootpath">导出路径</param>
/// <returns></returns>
public static string ExportListToExcelNew(IQueryable list, List <ExportObj> head, string title, string rootpath, string filename)
{
try
{
string FileName = filename.ToString() + "_" + title + ".xlsx";
string strPath = Path.Combine(rootpath, FileName);
if (Directory.Exists(rootpath) == false)
{
Directory.CreateDirectory(rootpath);
}
//文件流对象
using (FileStream filestream = new FileStream(strPath, FileMode.Create, FileAccess.Write))
{
Type type = list.ElementType;
PropertyInfo[] properties = type.GetProperties();
Int32 i = 0;
Int32 j = 0;
//打开Excel对象
XSSFWorkbook workbook = new XSSFWorkbook();
IDataFormat format = workbook.CreateDataFormat();
//set int format
ICellStyle cellStyleInt = workbook.CreateCellStyle();
cellStyleInt.DataFormat = format.GetFormat("#,##0");
cellStyleInt.WrapText = true;
//set decimal format
ICellStyle cellStyleDecimal = workbook.CreateCellStyle();
cellStyleDecimal.DataFormat = format.GetFormat("#,##0.0000");
cellStyleDecimal.WrapText = true;
//set float format
ICellStyle cellStylefloat = workbook.CreateCellStyle();
cellStylefloat.DataFormat = HSSFDataFormat.GetBuiltinFormat("0%");
cellStylefloat.WrapText = true;
//set double format
ICellStyle cellStyledouble = workbook.CreateCellStyle();
cellStyledouble.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyledouble.WrapText = true;
ICellStyle cellStyleWrapText = workbook.CreateCellStyle();
cellStyleWrapText.WrapText = true;
//创建一个字体样式对象
IFont font = workbook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle cellStyleWrapTextTitle = workbook.CreateCellStyle();
cellStyleWrapTextTitle.WrapText = true;
cellStyleWrapTextTitle.VerticalAlignment = VerticalAlignment.Center;
cellStyleWrapTextTitle.SetFont(font);
//Excel的Sheet对象
ISheet sheet = workbook.CreateSheet(title);
sheet.SetColumnWidth(0, 30 * 256);
sheet.SetColumnWidth(1, 15 * 256);
sheet.SetColumnWidth(2, 15 * 256);
sheet.SetColumnWidth(3, 22 * 256);
//生成sheet第一行列名
IRow headerRow = sheet.CreateRow(0);
foreach (var item in head)
{
if (type.GetProperty(item.key) != null)
{
ICell cell = headerRow.CreateCell(j);
cell.CellStyle = cellStyleWrapTextTitle;
cell.SetCellValue(item.Name);
j++;
}
}
//生成sheet数据部分
j = 1;
foreach (var obj in list)
{
//Writelog(string.Format("【创建行开始】"));
IRow dataRow = sheet.CreateRow(j);
//Writelog(string.Format("【创建行结束】"));
i = 0;
foreach (var item in head)
{
//Writelog(string.Format("【列开始】行数{0},列名{1}", j, item.key));
PropertyInfo column = type.GetProperty(item.key);
if (column != null)
{
ICell cell = dataRow.CreateCell(i);
Type cellType = item.DataType;
if (column.GetValue(obj, null) != null)
{
//整数123,456
if (cellType == typeof(int))
{
cell.SetCellValue((int)column.GetValue(obj, null));
cell.CellStyle = cellStyleInt;
}
//金额123,456
else if (cellType == typeof(decimal))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStyleDecimal;
}
else if (cellType == typeof(float))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStylefloat;
}
else if (cellType == typeof(double))
{
cell.SetCellValue(Convert.ToDouble(column.GetValue(obj, null)));
cell.CellStyle = cellStyledouble;
}
else
{
cell.SetCellValue(column.GetValue(obj, null).ToString());
cell.CellStyle.WrapText = true;
}
}
i++;
}
}
j++;
}
//保存excel文档
sheet.ForceFormulaRecalculation = true;
workbook.Write(filestream);
workbook.Clear();
}
return(rootpath + FileName);
}
catch (Exception ex)
{
return("");
}
}
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)
{
}
}
public static ICellStyle Getcellstyle(IWorkbook wb, stylexls str, NPOI.SS.Util.CellRangeAddress region, HSSFSheet sh)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 12;
font12.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
font12.FontName = "微软雅黑";
IFont font = wb.CreateFont();
font.FontName = "微软雅黑";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Black.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "微软雅黑";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //BorderLeft
if (region != null)
{
for (int i = region.FirstRow; i <= region.LastRow; i++)
{
IRow row = HSSFCellUtil.GetRow(i, sh);
for (int j = region.FirstColumn; j <= region.LastColumn; j++)
{
ICell singleCell = HSSFCellUtil.GetCell(row, (short)j);
singleCell.CellStyle = cellStyle;
}
}
}
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//背景图形，我没有用到过。感觉很丑
//cellStyle.FillBackgroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillForegroundColor = HSSFColor.OliveGreen.Black.Index;
//cellStyle.FillPattern = FillPatternType.NO_FILL;
//cellStyle.FillForegroundColor = HSSFColor.White.Index;
//cellStyle.FillBackgroundColor = HSSFColor.Black.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
//cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palette = ((HSSFWorkbook)wb).GetCustomPalette();
//HSSFColor newColor = palette.AddColor((byte)153, (byte)204, (byte)255);
palette.SetColorAtIndex((short)10, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy-mm-dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
//IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("¥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.None;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
case stylexls.居中:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
cellStyle.SetFont(font);
break;
case stylexls.商品导入头:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette pale = ((HSSFWorkbook)wb).GetCustomPalette();
pale.SetColorAtIndex((short)30, (byte)11, (byte)87, (byte)235);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.CornflowerBlue.Index;
cellStyle.SetFont(font12);
break;
case stylexls.导入说明:
HSSFPalette pale1 = ((HSSFWorkbook)wb).GetCustomPalette();
pale1.SetColorAtIndex((short)61, (byte)227, (byte)232, (byte)227);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightCornflowerBlue.Index;
IFont font2 = wb.CreateFont();
font2.FontName = "微软雅黑";
font2.FontHeightInPoints = 9;
cellStyle.SetFont(font2);
break;
case stylexls.导入红字:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgss = ((HSSFWorkbook)wb).GetCustomPalette();
palebgss.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
IFont fontRed = wb.CreateFont();
fontRed.FontName = "微软雅黑";
fontRed.Color = HSSFColor.OliveGreen.Red.Index;
cellStyle.SetFont(fontRed);
break;
case stylexls.导入错误提示:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette paleError = ((HSSFWorkbook)wb).GetCustomPalette();
paleError.SetColorAtIndex((short)10, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
cellStyle.SetFont(font);
break;
case stylexls.导入背景色:
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HSSFPalette palebgs = ((HSSFWorkbook)wb).GetCustomPalette();
palebgs.SetColorAtIndex((short)62, (byte)255, (byte)0, (byte)0);
cellStyle.FillPattern = FillPattern.SolidForeground; // NoFill;
cellStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
/// <summary>
/// 根据属性类型创建对应单元格
/// </summary>
/// <param name="cell">单元格</param>
/// <returns>字符串值</returns>
public static void CreateCell(this IRow row, int columnIndex, PropertyInfo property, object instance)
{
var val = property.GetValue(instance);
var cell = row.CreateCell(columnIndex);
if (property.PropertyType.Equals(typeof(string)))
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
else if (property.PropertyType.Equals(typeof(int)) ||
property.PropertyType.Equals(typeof(double)) ||
property.PropertyType.Equals(typeof(decimal)) ||
property.PropertyType.Equals(typeof(float)))
{
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(val));
var attr = property.GetCustomAttributes(typeof(DisplayFormatAttribute), true).FirstOrDefault();
if (attr != null)
{
var format = ((DisplayFormatAttribute)attr).DataFormatString ?? "";
if (!format.IsNullOrEmpty())
{
IDataFormat dataFormat = cell.Sheet.Workbook.CreateDataFormat();
ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.DataFormat = dataFormat.GetFormat(format);
cell.CellStyle = style;
}
}
}
else if (property.PropertyType.Equals(typeof(DateTime)) || property.PropertyType.Equals(typeof(DateTime?)))
{
cell.SetCellType(CellType.Numeric);
var format = "yyyy-MM-dd hh:mm:ss ";
var attr = property.GetCustomAttributes(typeof(DisplayFormatAttribute), true).FirstOrDefault();
if (attr != null)
{
format = ((DisplayFormatAttribute)attr).DataFormatString ?? format;
}
IDataFormat dataFormat = cell.Sheet.Workbook.CreateDataFormat();
ICellStyle style = cell.Sheet.Workbook.CreateCellStyle();
style.DataFormat = dataFormat.GetFormat(format);
cell.SetCellValue(val.TryDateTime());
cell.CellStyle = style;
}
else if (property.PropertyType.Equals(typeof(bool)))
{
cell.SetCellType(CellType.Boolean);
}
else if (property.PropertyType.Equals(typeof(string)) && instance != null)
{
var value = property.GetValue(instance);
if (value != null &&
value.ToString().ToLower().StartsWith("http") ||
value.ToString().ToLower().StartsWith("https"))
{
cell.SetCellType(CellType.Blank);
IHyperlink link;
if (cell.Sheet.Workbook.GetType() == typeof(XSSFWorkbook))
{
link = new XSSFHyperlink(HyperlinkType.Url);
link.Address = val.ToString();
}
else
{
link = new HSSFHyperlink(HyperlinkType.Url);
link.Address = val.ToString();
}
cell.SetCellValue(val.ToString());
cell.Hyperlink = link;
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
}
else
{
cell.SetCellType(CellType.String);
cell.SetCellValue(val == null ? "" : val.ToString());
}
}
/// <summary>
/// Esporta i dati di una tabella in un nuovo file di excel
/// </summary>
/// <param name="param">i dati della tabella</param>
public static void ExportDataInXlsx(object param)
{
SaveFileDialog saveFileDialog = new SaveFileDialog();
saveFileDialog.Filter = "Excel files (*.xlsx)|*.xlsx";
saveFileDialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.MyDocuments);
IWorkbook workbook = new XSSFWorkbook();
if (saveFileDialog.ShowDialog() == true)
{
int riga = 1; // la riga in excel parte da uno perchè in 0 c'è l'intestazione
int colonna = 0; // la colonna in excel
int ultimaColonna = 0; // l'ultima colonna dei dati
string valoreCampo; // il valore del campo da scrivere in excel
ISheet sheet; // il foglio di excel
IRow rigaXLS; // la riga di excel
ICell cellaXLS; // la cella di excel
#region style
ICellStyle rowStyle = workbook.CreateCellStyle();
ICellStyle R0C0 = CellsTableBorderStyle.TopSx(workbook.CreateCellStyle());
ICellStyle R0CX = CellsTableBorderStyle.TopCenter(workbook.CreateCellStyle());
ICellStyle R0CF = CellsTableBorderStyle.TopDx(workbook.CreateCellStyle());
ICellStyle RXC0 = CellsTableBorderStyle.LeftTable(workbook.CreateCellStyle());
ICellStyle RXCX = CellsTableBorderStyle.CenterTable(workbook.CreateCellStyle());
ICellStyle RXCF = CellsTableBorderStyle.RightTable(workbook.CreateCellStyle());
ICellStyle RFC0 = CellsTableBorderStyle.BottomSx(workbook.CreateCellStyle());
ICellStyle RFCX = CellsTableBorderStyle.BottomCenter(workbook.CreateCellStyle());
ICellStyle RFCF = CellsTableBorderStyle.BottomDx(workbook.CreateCellStyle());
ICellStyle RXCData = CellsTableBorderStyle.CenterTable(workbook.CreateCellStyle());
ICellStyle RXCPerc = CellsTableBorderStyle.CenterTable(workbook.CreateCellStyle());
ICellStyle RXCValuta = CellsTableBorderStyle.CenterTable(workbook.CreateCellStyle());
ICellStyle RFCData = CellsTableBorderStyle.BottomCenter(workbook.CreateCellStyle());
ICellStyle RFCPerc = CellsTableBorderStyle.BottomCenter(workbook.CreateCellStyle());
ICellStyle RFCValuta = CellsTableBorderStyle.BottomCenter(workbook.CreateCellStyle());
ICellStyle RXCFPerc = CellsTableBorderStyle.RightTable(workbook.CreateCellStyle());
ICellStyle RFCFPerc = CellsTableBorderStyle.BottomDx(workbook.CreateCellStyle());
IFont myBoldFont = workbook.CreateFont();
myBoldFont.Boldweight = (short)FontBoldWeight.Bold;
myBoldFont.FontHeightInPoints = 14;
IFont myPlainFont = workbook.CreateFont();
myPlainFont.Boldweight = (short)FontBoldWeight.Normal;
myPlainFont.FontHeightInPoints = 12;
#endregion
if (param is ReportProfitLossList report1)
{
sheet = workbook.CreateSheet("ProfitLoss");
ultimaColonna = SearchEndColumn(report1[0]);
RXCF.DataFormat = 8;
RFCF.DataFormat = 8;
RXCValuta.DataFormat = 8;
RFCValuta.DataFormat = 8;
foreach (ReportProfitLoss RPL in report1)
{
colonna = 0;
bool total = false;
foreach (var prop in RPL.GetType().GetProperties())
{
valoreCampo = prop.GetValue(RPL) == null ? "" : prop.GetValue(RPL).ToString();
bool isDbl = double.TryParse(valoreCampo, out double dbl);
if (riga - 1 == 0) // la riga di intestazione in excel
{
rigaXLS = colonna == 0 ? sheet.CreateRow(0) : sheet.GetRow(0);
rowStyle.SetFont(myBoldFont);
rigaXLS.RowStyle = rowStyle;
cellaXLS = rigaXLS.CreateCell(colonna);
cellaXLS.SetCellValue(prop.Name);
if (colonna == 0)
{
cellaXLS.CellStyle = R0C0;
}
else if (colonna > 0 && colonna < ultimaColonna)
{
cellaXLS.CellStyle = R0CX;
}
else if (colonna == ultimaColonna)
{
cellaXLS.CellStyle = R0CF;
}
} // fine intestazione
rigaXLS = colonna == 0 ? sheet.CreateRow(riga) : sheet.GetRow(riga);
rowStyle.SetFont(myPlainFont);
rigaXLS.RowStyle = rowStyle;
cellaXLS = rigaXLS.CreateCell(colonna);
if (riga < report1.Count) // il corpo centrale del report
{
if (colonna == 0)
{
cellaXLS.CellStyle = RXC0;
}
else if (colonna < 4)
{
cellaXLS.CellStyle = RXCX;
}
else if (colonna >= 4 && colonna < ultimaColonna)
{
cellaXLS.CellStyle = RXCValuta;
}
else if (colonna == ultimaColonna)
{
cellaXLS.CellStyle = RXCF;
}
}
if (riga == report1.Count) // la riga di chiusura
{
if (colonna == 0)
{
cellaXLS.CellStyle = RFC0;
}
else if (colonna < 4)
{
cellaXLS.CellStyle = RFCX;
}
else if (colonna >= 4 && colonna < ultimaColonna)
{
cellaXLS.CellStyle = RFCValuta;
}
else if (colonna == ultimaColonna)
{
cellaXLS.CellStyle = RFCF;
}
}
if (isDbl)
{
cellaXLS.SetCellValue(dbl);
}
else if (!isDbl)
{
cellaXLS.SetCellValue(valoreCampo);
if (valoreCampo.Contains("TOTALE"))
{
total = true;
}
}
colonna++;
}
if (total)
{
rigaXLS = sheet.GetRow(riga);
rowStyle.SetFont(myBoldFont);
rigaXLS.RowStyle = rowStyle;
}
//else
//{
// rigaXLS = sheet.GetRow(riga);
// foreach (ICell cell in rigaXLS)
// cell.CellStyle.SetFont(myPlainFont);
//}
riga++;
}
}
else if (param is ReportMovementDetailedList report2)
{
sheet = workbook.CreateSheet("DettaglioTitolo");
IDataFormat format = workbook.CreateDataFormat();
int EndCol = SearchEndColumn(report2[0]);
MakeTopTableRow(report2[0], workbook, sheet, EndCol, report2.Count);
int ExcelRow = 1;
foreach (ReportMovementDetailed RMD in report2)
{
IRow row;
int iCol = 0;
row = sheet.CreateRow(ExcelRow);
// dati
foreach (var prop in RMD.GetType().GetProperties())
{
string fieldValue = prop.GetValue(RMD) == null ? "" : prop.GetValue(RMD).ToString();
bool isDbl = double.TryParse(fieldValue, out double dbl);
bool isDate = DateTime.TryParse(fieldValue, out DateTime dt);
ICell cell = row.CreateCell(iCol);
SetStyle(workbook, cell, iCol, ExcelRow, EndCol, report2.Count);
if (isDbl)
{
cell.SetCellValue(dbl);
cell.CellStyle.DataFormat = 8;
}
else if (isDate)
{
cell.SetCellValue(dt);
cell.CellStyle.DataFormat = 14;
}
else
{
cell.SetCellValue(fieldValue);
}
iCol++;
}
ExcelRow++;
}
}
else if (param is ReportTitoliAttiviList report3)
{
sheet = workbook.CreateSheet("TitoliAttivi");
IDataFormat format = workbook.CreateDataFormat();
int EndCol = SearchEndColumn(report3[0]);
MakeTopTableRow(report3[0], workbook, sheet, EndCol, report3.Count);
int ExcelRow = 1;
foreach (ReportTitoliAttivi RTA in report3)
{
IRow row;
int iCol = 0;
row = sheet.CreateRow(ExcelRow);
// dati
foreach (var prop in RTA.GetType().GetProperties())
{
string fieldValue = prop.GetValue(RTA) == null ? "" : prop.GetValue(RTA).ToString();
bool isDbl = double.TryParse(fieldValue, out double dbl);
ICell cell = row.CreateCell(iCol);
SetStyle(workbook, cell, iCol, ExcelRow, EndCol, report3.Count);
if (isDbl && prop.Name != "N_Titoli")
{
cell.SetCellValue(dbl);
cell.CellStyle.DataFormat = 8;
}
else if (isDbl && prop.Name == "N_Titoli")
{
cell.SetCellValue(dbl);
cell.CellStyle.DataFormat = 4;
}
else
{
cell.SetCellValue(fieldValue);
}
iCol++;
}
ExcelRow++;
}
}
else if (param is ObservableCollection <AnalisiPortafoglio> report4)
{
sheet = workbook.CreateSheet("AnalisiPortafoglio");
IDataFormat format = workbook.CreateDataFormat();
int TotalRow = SearchEndColumn(report4[0]) - 6;
int iColText = 0;
int iCol = 1;
IRow row;
ICell cell;
foreach (AnalisiPortafoglio analisiPortafoglio in report4)
{
int iRow = 0;
foreach (var prop in analisiPortafoglio.GetType().GetProperties())
{
if (prop.Name != "id_titolo" && prop.Name != "desc_titolo" && prop.Name != "Isin" &&
prop.Name != "id_tipo_titolo" && prop.Name != "id_azienda" && prop.Name != "data_modifica")
{
string fieldValue = prop.GetValue(analisiPortafoglio) == null ? "" : prop.GetValue(analisiPortafoglio).ToString();
bool isDbl = double.TryParse(fieldValue, out double dbl);
if (iCol == 1)
{
row = sheet.CreateRow(iRow);
cell = row.CreateCell(iColText);
cell.SetCellValue(prop.Name);
SetStyle(workbook, cell, iColText, iRow, iCol, TotalRow);
if (iRow < 2)
{
MakeCellBold(workbook, cell);
}
}
else
{
row = sheet.GetRow(iRow);
}
cell = row.CreateCell(iCol);
SetStyle(workbook, cell, iCol, iRow, iCol, TotalRow);
if (isDbl)
{
cell.SetCellValue(dbl);
if (iRow < 2)
{
cell.CellStyle.DataFormat = 8;
}
else
{
cell.CellStyle.DataFormat = format.GetFormat("0.00%");
}
}
else
{
cell.SetCellValue(fieldValue);
}
if (iRow < 2)
{
MakeCellBold(workbook, cell);
}
iRow++;
}
}
iCol++;
}
}
else if (param is GuadagnoPerQuoteList report5)
{
sheet = workbook.CreateSheet("GuadagnoPerQuote");
#region syling
R0C0.SetFont(myBoldFont);
R0CF.SetFont(myBoldFont);
R0CX.SetFont(myBoldFont);
RXC0.SetFont(myPlainFont);
RXCX.SetFont(myPlainFont);
RXCF.SetFont(myPlainFont);
RFC0.SetFont(myPlainFont);
RFCX.SetFont(myPlainFont);
RFCF.SetFont(myPlainFont);
RXCData.SetFont(myPlainFont);
RXCPerc.SetFont(myPlainFont);
RXCValuta.SetFont(myPlainFont);
RFCData.SetFont(myPlainFont);
RFCPerc.SetFont(myPlainFont);
RFCValuta.SetFont(myPlainFont);
RXCData.DataFormat = 14;
RXCPerc.DataFormat = 10;
RXCValuta.DataFormat = 8;
RFCData.DataFormat = 14;
RFCPerc.DataFormat = 10;
RFCValuta.DataFormat = 8;
#endregion
int EndCol = SearchEndColumn(report5[0]);
int Riga = 1;
int Colonna;
IRow row;
foreach (GuadagnoPerQuote GPQ in report5) // tutti i record
{
Colonna = 0;
foreach (var prop in GPQ.GetType().GetProperties())
{
if (prop.Name != "IdTipoMovimento")
{
ICell cell;
string fieldValue = prop.GetValue(GPQ) == null ? "" : prop.GetValue(GPQ).ToString();
bool isDbl = double.TryParse(fieldValue, out double dbl);
bool isDate = DateTime.TryParse(fieldValue, out DateTime dt);
if (Riga - 1 == 0)
{
row = Colonna == 0 ? sheet.CreateRow(0) : sheet.GetRow(0);
cell = row.CreateCell(Colonna);
cell.SetCellValue(prop.Name);
if (Colonna == 0)
{
cell.CellStyle = R0C0;
}
else if (Colonna > 0 && Colonna < EndCol)
{
cell.CellStyle = R0CX;
}
else if (Colonna == EndCol)
{
cell.CellStyle = R0CF;
}
}
row = Colonna == 0 ? sheet.CreateRow(Riga) : sheet.GetRow(Riga);
cell = row.CreateCell(Colonna);
if (Riga < report5.Count)
{
if (Colonna == 0)
{
cell.CellStyle = RXC0;
}
else if (Colonna < 7)
{
cell.CellStyle = RXCX;
}
else if (Colonna == 7)
{
cell.CellStyle = RXCData;
}
else if (Colonna == 8)
{
cell.CellStyle = RXCPerc;
}
else if (Colonna > 8 && Colonna < 13)
{
cell.CellStyle = RXCValuta;
}
else if (Colonna == 13)
{
cell.CellStyle = RXCF;
}
}
else if (Riga == report5.Count)
{
if (Colonna == 0)
{
cell.CellStyle = RFC0;
}
else if (Colonna < 7)
{
cell.CellStyle = RFCX;
}
else if (Colonna == 7)
{
cell.CellStyle = RFCData;
}
else if (Colonna == 8)
{
cell.CellStyle = RFCPerc;
}
else if (Colonna > 8 && Colonna < 13)
{
cell.CellStyle = RFCValuta;
}
else if (Colonna == 13)
{
cell.CellStyle = RFCF;
}
}
if (isDbl)
{
cell.SetCellValue(dbl);
}
else if (isDate)
{
cell.SetCellValue(dt);
}
else if (!isDbl && !isDate)
{
cell.SetCellValue(fieldValue);
}
Colonna++;
}
}
Riga++;
}
}
else if (param is GuadagnoPerPeriodoList report6)
{
sheet = workbook.CreateSheet("DeltaPerPeriodo");
int EndCol = SearchEndColumn(report6[0]);
int Riga = 1;
int Colonna;
IRow row;
#region syling
R0C0.SetFont(myBoldFont);
R0CF.SetFont(myBoldFont);
R0CX.SetFont(myBoldFont);
RXC0.SetFont(myPlainFont);
RXCX.SetFont(myPlainFont);
RXCValuta.SetFont(myPlainFont);
RFCValuta.SetFont(myPlainFont);
RXCFPerc.SetFont(myPlainFont);
RFCFPerc.SetFont(myPlainFont);
RFCFPerc.DataFormat = 10;
RXCFPerc.DataFormat = 10;
RXCValuta.DataFormat = 8;
RFCValuta.DataFormat = 8;
#endregion
foreach (GuadagnoPerPeriodo GPP in report6)
{
Colonna = 0;
foreach (var prop in GPP.GetType().GetProperties())
{
if (prop.Name != "IdGestione")
{
ICell cell;
string fieldValue = prop.GetValue(GPP) == null ? "" : prop.GetValue(GPP).ToString();
bool isDbl = double.TryParse(fieldValue, out double dbl);
bool isDate = DateTime.TryParse(fieldValue, out DateTime dt);
if (Riga - 1 == 0)
{
row = Colonna == 0 ? sheet.CreateRow(0) : sheet.GetRow(0);
cell = row.CreateCell(Colonna);
cell.SetCellValue(prop.Name);
if (Colonna == 0)
{
cell.CellStyle = R0C0;
}
else if (Colonna > 0 && Colonna < EndCol)
{
cell.CellStyle = R0CX;
}
else if (Colonna == EndCol)
{
cell.CellStyle = R0CF;
}
}
row = Colonna == 0 ? sheet.CreateRow(Riga) : sheet.GetRow(Riga);
cell = row.CreateCell(Colonna);
if (Riga < report6.Count)
{
if (Colonna == 0)
{
cell.CellStyle = RXC0;
}
else if (Colonna == 1)
{
cell.CellStyle = RXCX;
}
else if (Colonna < 5)
{
cell.CellStyle = RXCValuta;
}
else if (Colonna == 5)
{
cell.CellStyle = RXCFPerc;
}
}
else if (Riga == report6.Count)
{
if (Colonna == 0)
{
cell.CellStyle = RFC0;
}
else if (Colonna == 1)
{
cell.CellStyle = RFCX;
}
else if (Colonna < 5)
{
cell.CellStyle = RFCValuta;
}
else if (Colonna == 5)
{
cell.CellStyle = RFCFPerc;
}
}
if (isDbl)
{
cell.SetCellValue(dbl);
}
else if (!isDbl && !isDate)
{
cell.SetCellValue(fieldValue);
}
Colonna++;
}
}
Riga++;
}
}
FileStream file = new FileStream(saveFileDialog.FileName, FileMode.Create);
workbook.Write(file);
file.Close();
}
}
private static ICellStyle Getcellstyle(IWorkbook wb, stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
IFont font12 = wb.CreateFont();
font12.FontHeightInPoints = 14;
font12.FontName = "宋体";
IFont font = wb.CreateFont();
font.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true; //下划线
fontcolorblue.FontName = "宋体";
//边框
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Dotted;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Hair;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Hair;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Dotted;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Blue.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Blue.Index;
//背景图形，我没有用到过。感觉很丑
// cellStyle.FillBackgroundColor = HSSFColor.OLIVE_GREEN.GREEN.index;
//cellStyle.FillForegroundColor = HSSFColor.OLIVE_GREEN.BLUE.index;
// cellStyle.FillForegroundColor = HSSFColor.WHITE.index;
// cellStyle.FillPattern = FillPatternType.NO_FILL;
cellStyle.FillBackgroundColor = HSSFColor.Maroon.Index;
//水平对齐
cellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
// cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case stylexls.头:
// cellStyle.FillPattern = FillPatternType.LEAST_DOTS;
cellStyle.SetFont(font12);
break;
case stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy/mm/dd");
cellStyle.SetFont(font);
break;
case stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.SetFont(font);
break;
case stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0");
cellStyle.SetFont(font);
break;
case stylexls.url:
fontcolorblue.Underline = FontUnderlineType.Single;
cellStyle.SetFont(fontcolorblue);
break;
case stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(font);
break;
case stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(font);
break;
case stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(font);
break;
case stylexls.默认:
cellStyle.SetFont(font);
break;
}
return(cellStyle);
}
static async void Getstockdataasync()
{
XmlDocument xml = new XmlDocument();
String exepath = AppDomain.CurrentDomain.BaseDirectory;
xml.Load(exepath + @"config.xml");
XmlNode tokenst = xml.SelectSingleNode("/configuration/token");
string token = tokenst.InnerText;
StreamReader stocklist = new StreamReader("stocklist.txt", Encoding.Default);
StreamWriter connect5 = new StreamWriter(@"stockdata_daily.txt", true, Encoding.Default);
StreamWriter connect6 = new StreamWriter(@"summary.txt", true, Encoding.Default);
string stockl = null;
while ((stockl = stocklist.ReadLine()) != null)
{
var symbol = stockl;
StreamWriter connect5s = new StreamWriter(@symbol + "_daily.txt", true, Encoding.Default);
var dailyprices = new List <AlphaVantageData>();
await Task.Run(() =>
{
dailyprices = $"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol={symbol}&outputsize=full&apikey={token}&datatype=csv"
.GetStringFromUrl().FromCsv <List <AlphaVantageData> >();
});
List <string> bm = new List <string>();
HSSFWorkbook wb;
HSSFSheet sh;
if (!File.Exists(@symbol + "_daily.txt" + "test.xls"))
{
wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());
// create sheet
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
IDataFormat dataFormatCustom = wb.CreateDataFormat();
ICellStyle style1 = wb.CreateCellStyle();
style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM");
for (int x = 0; x < dailyprices.Count; x++)
{
var r = sh.CreateRow(x);
for (int j = 0; j < 7; j++)
{
IRow row = sh.GetRow(x);
if (j == 0)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(symbol);
}
else if (j == 1)
{
r.CreateCell(j).CellStyle = style1;
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(dailyprices[x].Timestamp);
}
else if (j == 2)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[x].Volume);
}
else if (j == 3)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[x].Open);
}
else if (j == 4)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[x].High);
}
else if (j == 5)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[x].Low);
}
else if (j == 6)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[x].Close);
}
}
}
using (var fs = new FileStream(@symbol + "_daily.txt" + "test.xls", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
for (int x = 0; x < dailyprices.Count; x++)
{
connect5.WriteLine(symbol + "\t" + dailyprices[x].Timestamp + "\t" + dailyprices[x].Volume + "\t" + dailyprices[x].Open + "\t" + dailyprices[x].High + "\t" + dailyprices[x].Low + "\t" + dailyprices[x].Close);
connect5s.WriteLine(symbol + "\t" + dailyprices[x].Timestamp + "\t" + dailyprices[x].Volume + "\t" + dailyprices[x].Open + "\t" + dailyprices[x].High + "\t" + dailyprices[x].Low + "\t" + dailyprices[x].Close);
}
decimal ten = ((dailyprices[0].Close - dailyprices[9].Close) / (dailyprices[9].Close)) * 100;
decimal thirty = ((dailyprices[0].Close - dailyprices[29].Close) / (dailyprices[29].Close)) * 100;
decimal sixty = ((dailyprices[0].Close - dailyprices[59].Close) / (dailyprices[59].Close)) * 100;
decimal ninety = ((dailyprices[0].Close - dailyprices[89].Close) / (dailyprices[89].Close)) * 100;
decimal N120 = ((dailyprices[0].Close - dailyprices[119].Close) / (dailyprices[119].Close)) * 100;
decimal N240 = ((dailyprices[0].Close - dailyprices[239].Close) / (dailyprices[239].Close)) * 100;
decimal N360 = ((dailyprices[0].Close - dailyprices[359].Close) / (dailyprices[359].Close)) * 100;
connect6.WriteLine(symbol + " 10 day " + ten);
connect6.WriteLine(symbol + " 30 day " + thirty);
connect6.WriteLine(symbol + " 60 day " + sixty);
connect6.WriteLine(symbol + " 90 day " + ninety);
connect6.WriteLine(symbol + " 120 day " + N120);
connect6.WriteLine(symbol + " 240 day " + N240);
connect6.WriteLine(symbol + " 360 day " + N360);
connect5s.Close();
}
connect5.Close();
connect6.Close();
stocklist.Close();
}
static void Getstockdata()
{
//main1
//main2
//main3
try
{
XmlDocument xml = new XmlDocument();
String exepath = AppDomain.CurrentDomain.BaseDirectory;
xml.Load(exepath + @"config.xml");
XmlNode tokenst = xml.SelectSingleNode("/configuration/token");
string token = tokenst.InnerText;
XmlNode functionst = xml.SelectSingleNode("/configuration/function");
string function = functionst.InnerText;
XmlNode outputsizest = xml.SelectSingleNode("/configuration/outputsize");
string outputsize = outputsizest.InnerText;
XmlNode intervalst = xml.SelectSingleNode("/configuration/interval");
string interval = intervalst.InnerText;
StreamReader stocklist = new StreamReader(@AppDomain.CurrentDomain.BaseDirectory + "stocklist.txt", Encoding.Default);
string stockl = null;
while ((stockl = stocklist.ReadLine()) != null)
{
var symbol = stockl;
//StreamWriter connect5s = new StreamWriter(@symbol + "_daily.txt", true, Encoding.Default);
StreamWriter connect5s = new StreamWriter(@symbol + "_" + function + "_" + interval + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".txt", false, Encoding.Default);
//var dailyprices = $"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol={symbol}&outputsize=full&apikey={token}&datatype=csv"
// .GetStringFromUrl().FromCsv<List<AlphaVantageData>>();
var prices = $"https://www.alphavantage.co/query?function={function}&symbol={symbol}&outputsize={outputsize}&interval={interval}&apikey={token}&datatype=csv"
.GetStringFromUrl().FromCsv <List <Program.AlphaVantageData> >();
HSSFWorkbook wb;
HSSFSheet sh;
wb = HSSFWorkbook.Create(InternalWorkbook.CreateWorkbook());
// create sheet
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
IDataFormat dataFormatCustom = wb.CreateDataFormat();
ICellStyle style1 = wb.CreateCellStyle();
style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM");
XSSFWorkbook wbx;
XSSFSheet shx;
for (int x = 0; x < prices.Count; x++)
{
var r = sh.CreateRow(x);
for (int j = 0; j < 7; j++)
{
IRow row = sh.GetRow(x);
if (j == 0)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(symbol);
}
else if (j == 1)
{
r.CreateCell(j).CellStyle = style1;
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(prices[x].Timestamp);
}
else if (j == 2)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)prices[x].Volume);
}
else if (j == 3)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)prices[x].Open);
}
else if (j == 4)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)prices[x].High);
}
else if (j == 5)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)prices[x].Low);
}
else if (j == 6)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)prices[x].Close);
}
}
}
using (var fs = new FileStream(@symbol + "_" + function + "_" + interval + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xls", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
//}
for (int x = 0; x < prices.Count; x++)
{
//connect5.WriteLine(symbol + "\t" + prices[x].Timestamp + "\t" + prices[x].Volume + "\t" + prices[x].Open + "\t" + prices[x].High + "\t" + prices[x].Low + "\t" + prices[x].Close);
connect5s.WriteLine(symbol + "\t" + prices[x].Timestamp + "\t" + prices[x].Volume + "\t" + prices[x].Open + "\t" + prices[x].High + "\t" + prices[x].Low + "\t" + prices[x].Close);
}
decimal ten = ((prices[0].Close - prices[9].Close) / (prices[9].Close)) * 100;
decimal thirty = ((prices[0].Close - prices[29].Close) / (prices[29].Close)) * 100;
decimal sixty = ((prices[0].Close - prices[59].Close) / (prices[59].Close)) * 100;
decimal ninety = ((prices[0].Close - prices[89].Close) / (prices[89].Close)) * 100;
decimal N120 = ((prices[0].Close - prices[119].Close) / (prices[119].Close)) * 100;
decimal N240 = ((prices[0].Close - prices[239].Close) / (prices[239].Close)) * 100;
decimal N360 = ((prices[0].Close - prices[359].Close) / (prices[359].Close)) * 100;
//connect6.WriteLine(symbol + " 10 day " + ten);
//connect6.WriteLine(symbol + " 30 day " + thirty);
//connect6.WriteLine(symbol + " 60 day " + sixty);
//connect6.WriteLine(symbol + " 90 day " + ninety);
//connect6.WriteLine(symbol + " 120 day " + N120);
//connect6.WriteLine(symbol + " 240 day " + N240);
//connect6.WriteLine(symbol + " 360 day " + N360);
connect5s.Close();
}
//connect5.Close();
//connect6.Close();
stocklist.Close();
}
catch (Exception e)
{
Console.WriteLine($"Generic Exception Handler: {e}");
}
finally
{
}
}
private ExportRunEntity ProcessCellStyle(ExportRunEntity helpEntity)
{
IEnumerable <ColorEntity> listColors = ProcessColor(helpEntity);
List <ICellStyle> listStyle = new List <ICellStyle>();
ICellStyle cellStyle = null;
List <CellStyleEntity> listCellStyle = new List <CellStyleEntity>();
CellStyleEntity styleEntity = null;
foreach (var item in helpEntity.ExportStyles)
{
cellStyle = workBook.CreateCellStyle();
cellStyle.Alignment = item.Alignment;
cellStyle.BorderBottom = item.BorderBottom;
cellStyle.BorderDiagonal = item.BorderDiagonal;
var temp = listColors.Where(t => t.RGB == item.BorderDiagonalColor);
if (temp != null && temp.Any())
{
cellStyle.BorderDiagonalColor = temp.FirstOrDefault().Index;
}
cellStyle.BorderDiagonalLineStyle = item.BorderDiagonalLineStyle;
cellStyle.BorderLeft = item.BorderLeft;
cellStyle.BorderRight = item.BorderRight;
cellStyle.BorderTop = item.BorderTop;
temp = listColors.Where(t => t.RGB == item.BottomBorderColor);
if (temp != null && temp.Any())
{
cellStyle.BottomBorderColor = temp.FirstOrDefault().Index;
}
if (!string.IsNullOrEmpty(item.DataFormat))
{
IDataFormat df = workBook.CreateDataFormat();
cellStyle.DataFormat = df.GetFormat(item.DataFormat);
}
temp = listColors.Where(t => t.RGB == item.FillBackgroundColor);
if (temp != null && temp.Any())
{
cellStyle.FillBackgroundColor = temp.FirstOrDefault().Index;
}
temp = listColors.Where(t => t.RGB == item.FillForegroundColor);
if (temp != null && temp.Any())
{
cellStyle.FillForegroundColor = temp.FirstOrDefault().Index;
}
cellStyle.FillPattern = item.FillPattern;
cellStyle.Indention = item.Indention;
cellStyle.IsHidden = item.IsHidden;
cellStyle.IsLocked = item.IsLocked;
temp = listColors.Where(t => t.RGB == item.LeftBorderColor);
if (temp != null && temp.Any())
{
cellStyle.LeftBorderColor = temp.FirstOrDefault().Index;
}
temp = listColors.Where(t => t.RGB == item.RightBorderColor);
if (temp != null && temp.Any())
{
cellStyle.RightBorderColor = temp.FirstOrDefault().Index;
}
cellStyle.Rotation = item.Rotation;
cellStyle.ShrinkToFit = item.ShrinkToFit;
cellStyle.VerticalAlignment = item.VerticalAlignment;
cellStyle.WrapText = item.WrapText;
if (item.Font != null)
{
IFont font = workBook.CreateFont();
font.Boldweight = item.Font.Boldweight;
font.Charset = item.Font.Charset;
temp = listColors.Where(t => t.RGB == item.Font.Color);
if (temp != null && temp.Any())
{
font.Color = temp.FirstOrDefault().Index;
}
font.FontHeight = item.Font.FontHeight;
font.FontHeightInPoints = item.Font.FontHeightInPoints;
font.FontName = item.Font.FontName;
font.IsItalic = item.Font.IsItalic;
font.IsStrikeout = item.Font.IsStrikeout;
font.Underline = item.Font.Underline;
cellStyle.SetFont(font);
}
styleEntity = new CellStyleEntity()
{
CellStyleIndex = item.CellStyleIndex,
CellStyle = cellStyle
};
listCellStyle.Add(styleEntity);
}
foreach (var item in helpEntity.ExportColumns)
{
var temp = listCellStyle.Where(t => t.CellStyleIndex == item.CellStyleIndex);
if (temp != null && temp.Any())
{
item.CellStyle = temp.FirstOrDefault().CellStyle;
}
}
return(helpEntity);
}
/// <summary>
/// 导出数据到数据流
/// </summary>
/// <param name="dataSet">要导出的数据</param>
/// <param name="stream">要导出到的数据流</param>
/// <exception cref="ArgumentException">参数异常</exception>
public static void ExportToStream(IEnumerable<IEnumerable<object>> dataSet, Stream stream)
{
if (dataSet == null) throw new ArgumentException("无效的导入数据", "dataSet");
if (stream == null) throw new ArgumentException("目标数据流无效", "stream");
//创建工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建日期的显示样式
_dateStyle = workbook.CreateCellStyle();
_dataFormat = workbook.CreateDataFormat();
_dateStyle.DataFormat = _dataFormat.GetFormat(_dataType);
//遍历每张表
foreach (IEnumerable<object> dataTable in dataSet) //每张表
{
if (dataTable == null || !dataTable.Any()) continue;
//获取表中第一个不为空的元素
var obj = dataTable.FirstOrDefault(o => o != null);
//如果没有不为空的元素,跳过这张表
if (obj == null) continue;
//获取该表中的对象的属性
var columns = obj.GetType().GetProperties();
//创建表
ISheet sheet = workbook.CreateSheet();
SetTable(sheet, dataTable);
}
workbook.Write(stream);
}
private void OutputLocaleDataFormats(DateTime date, bool dates, bool times, int style, String styleName)
{
IWorkbook workbook = new HSSFWorkbook();
String sheetName;
if (dates)
{
if (times)
{
sheetName = "DateTimes";
}
else
{
sheetName = "Dates";
}
}
else
{
sheetName = "Times";
}
ISheet sheet = workbook.CreateSheet(sheetName);
IRow header = sheet.CreateRow(0);
header.CreateCell(0).SetCellValue("locale");
header.CreateCell(1).SetCellValue("DisplayName");
header.CreateCell(2).SetCellValue("Excel " + styleName);
header.CreateCell(3).SetCellValue("java.text.DateFormat");
header.CreateCell(4).SetCellValue("Equals");
header.CreateCell(5).SetCellValue("Java pattern");
header.CreateCell(6).SetCellValue("Excel pattern");
int rowNum = 1;
foreach (CultureInfo locale in CultureInfo.GetCultures(CultureTypes.AllCultures))
{
if (string.IsNullOrEmpty(locale.ToString()))
{
continue;
}
IRow row = sheet.CreateRow(rowNum++);
row.CreateCell(0).SetCellValue(locale.ToString());
row.CreateCell(1).SetCellValue(locale.DisplayName);
string csharpDateFormatPattern;
if (dates)
{
if (times)
{
csharpDateFormatPattern = DateFormat.GetDateTimePattern(style, style, locale);
}
else
{
csharpDateFormatPattern = DateFormat.GetDatePattern(style, locale);
}
}
else
{
csharpDateFormatPattern = DateFormat.GetTimePattern(style, locale);
}
//Excel Date Value
ICell cell = row.CreateCell(2);
cell.SetCellValue(date);
ICellStyle cellStyle = row.Sheet.Workbook.CreateCellStyle();
//String csharpDateFormatPattern = locale.DateTimeFormat.LongDatePattern;
String excelFormatPattern = DateFormatConverter.Convert(locale, csharpDateFormatPattern);
IDataFormat poiFormat = row.Sheet.Workbook.CreateDataFormat();
cellStyle.DataFormat = (poiFormat.GetFormat(excelFormatPattern));
cell.CellStyle = (cellStyle);
//C# Date value
row.CreateCell(3).SetCellValue(date.ToString(csharpDateFormatPattern, locale.DateTimeFormat));
// the formula returns TRUE is the formatted date in column C equals to the string in column D
row.CreateCell(4).SetCellFormula("TEXT(C" + rowNum + ",G" + rowNum + ")=D" + rowNum);
//C# pattern
row.CreateCell(5).SetCellValue(csharpDateFormatPattern);
//excel pattern
row.CreateCell(6).SetCellValue(excelFormatPattern);
}
//FileInfo outputFile = TempFile.CreateTempFile("Locale" + sheetName + styleName, ".xlsx");
string filename = "Locale" + sheetName + styleName + ".xls";
FileStream outputStream = new FileStream(filename, FileMode.Create);
try
{
workbook.Write(outputStream);
}
finally
{
outputStream.Close();
}
System.Console.WriteLine("Open " + filename + " in Excel");
}
private short ParseDataFormat(string formatStr) {
IDataFormat dataFormat = workbook.CreateDataFormat();
return dataFormat.GetFormat(formatStr);
}
public void Stackoverflow23114397()
{
IWorkbook wb = _testDataProvider.CreateWorkbook();
IDataFormat format = wb.GetCreationHelper().CreateDataFormat();
// How close the sizing should be, given that not all
// systems will have quite the same fonts on them
float fontAccuracy = 0.22f;
// x%
ICellStyle iPercent = wb.CreateCellStyle();
iPercent.DataFormat = (/*setter*/ format.GetFormat("0%"));
// x.x%
ICellStyle d1Percent = wb.CreateCellStyle();
d1Percent.DataFormat = (/*setter*/ format.GetFormat("0.0%"));
// x.xx%
ICellStyle d2Percent = wb.CreateCellStyle();
d2Percent.DataFormat = (/*setter*/ format.GetFormat("0.00%"));
ISheet s = wb.CreateSheet();
IRow r1 = s.CreateRow(0);
for (int i = 0; i < 3; i++)
{
r1.CreateCell(i, CellType.Numeric).SetCellValue(0);
}
for (int i = 3; i < 6; i++)
{
r1.CreateCell(i, CellType.Numeric).SetCellValue(1);
}
for (int i = 6; i < 9; i++)
{
r1.CreateCell(i, CellType.Numeric).SetCellValue(0.12345);
}
for (int i = 9; i < 12; i++)
{
r1.CreateCell(i, CellType.Numeric).SetCellValue(1.2345);
}
for (int i = 0; i < 12; i += 3)
{
r1.GetCell(i + 0).CellStyle = (/*setter*/ iPercent);
r1.GetCell(i + 1).CellStyle = (/*setter*/ d1Percent);
r1.GetCell(i + 2).CellStyle = (/*setter*/ d2Percent);
}
for (int i = 0; i < 12; i++)
{
s.AutoSizeColumn(i);
}
// Check the 0(.00)% ones
assertAlmostEquals(980, s.GetColumnWidth(0), fontAccuracy);
assertAlmostEquals(1400, s.GetColumnWidth(1), fontAccuracy);
assertAlmostEquals(1700, s.GetColumnWidth(2), fontAccuracy);
// Check the 100(.00)% ones
assertAlmostEquals(1500, s.GetColumnWidth(3), fontAccuracy);
assertAlmostEquals(1950, s.GetColumnWidth(4), fontAccuracy);
assertAlmostEquals(2225, s.GetColumnWidth(5), fontAccuracy);
// Check the 12(.34)% ones
assertAlmostEquals(1225, s.GetColumnWidth(6), fontAccuracy);
assertAlmostEquals(1650, s.GetColumnWidth(7), fontAccuracy);
assertAlmostEquals(1950, s.GetColumnWidth(8), fontAccuracy);
// Check the 123(.45)% ones
assertAlmostEquals(1500, s.GetColumnWidth(9), fontAccuracy);
assertAlmostEquals(1950, s.GetColumnWidth(10), fontAccuracy);
assertAlmostEquals(2225, s.GetColumnWidth(11), fontAccuracy);
}
public static ICellStyle Getcellstyle(IWorkbook wb, Stylexls str)
{
ICellStyle cellStyle = wb.CreateCellStyle();
//定义几种字体
//也可以一种字体，写一些公共属性，然后在下面需要时加特殊的
//标题
IFont fontTitle = wb.CreateFont();
fontTitle.FontHeightInPoints = 16;
fontTitle.FontName = "宋体";
fontTitle.IsBold = true;
//列头
IFont fontColumnHeader = wb.CreateFont();
fontColumnHeader.FontHeightInPoints = 12;
fontColumnHeader.FontName = "宋体";
fontColumnHeader.IsBold = true;
//内容
IFont fontContent = wb.CreateFont();
fontContent.FontHeightInPoints = 9;
fontContent.FontName = "宋体";
//font.Underline = 1;下划线
IFont fontcolorblue = wb.CreateFont();
fontcolorblue.Color = HSSFColor.OliveGreen.Blue.Index;
fontcolorblue.IsItalic = true;//下划线
fontcolorblue.FontName = "宋体";
////边框
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
//边框颜色
cellStyle.BottomBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.TopBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.LeftBorderColor = HSSFColor.OliveGreen.Black.Index;
cellStyle.RightBorderColor = HSSFColor.OliveGreen.Black.Index;
//水平对齐
cellStyle.Alignment = HorizontalAlignment.Left;
//垂直对齐
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//自动换行
cellStyle.WrapText = true;
//缩进;当设置为1时，前面留的空白太大了。希旺官网改进。或者是我设置的不对
cellStyle.Indention = 0;
//上面基本都是设共公的设置
//下面列出了常用的字段类型
switch (str)
{
case Stylexls.标题:
cellStyle.Alignment = HorizontalAlignment.Center;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
cellStyle.SetFont(fontTitle);
break;
case Stylexls.头:
cellStyle.SetFont(fontColumnHeader);
break;
case Stylexls.时间:
IDataFormat datastyle = wb.CreateDataFormat();
cellStyle.DataFormat = datastyle.GetFormat("yyyy-MM-dd");
cellStyle.SetFont(fontContent);
break;
case Stylexls.数字:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00");
cellStyle.Alignment = HorizontalAlignment.Right;
cellStyle.SetFont(fontContent);
break;
case Stylexls.钱:
IDataFormat format = wb.CreateDataFormat();
cellStyle.DataFormat = format.GetFormat("￥#,##0.00");
cellStyle.SetFont(fontContent);
break;
case Stylexls.百分比:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00%");
cellStyle.SetFont(fontContent);
break;
case Stylexls.中文大写:
IDataFormat format1 = wb.CreateDataFormat();
cellStyle.DataFormat = format1.GetFormat("[DbNum2][$-804]0");
cellStyle.SetFont(fontContent);
break;
case Stylexls.科学计数法:
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0.00E+00");
cellStyle.SetFont(fontContent);
break;
case Stylexls.默认:
cellStyle.SetFont(fontContent);
break;
}
return(cellStyle);
}
public void SetUp()
{
// One or more test methods depends on the american culture.
System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo.CreateSpecificCulture("en-US");
// create the formatter to Test
formatter = new HSSFDataFormatter();
// create a workbook to Test with
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet();
IDataFormat format = wb.CreateDataFormat();
// create a row and Put some cells in it
IRow row = sheet.CreateRow(0);
// date value for July 8 1901 1:19 PM
double dateNum = 555.555;
// date value for July 8 1901 11:23 AM
double timeNum = 555.47431;
//valid date formats -- all should have "Jul" in output
String[] goodDatePatterns =
{
"[$-F800]dddd\\,\\ mmmm\\ dd\\,\\ yyyy",
"mmm/d/yy\\ h:mm PM;@",
"mmmm/d/yy\\ h:mm;@",
"mmmm/d;@",
"mmmm/d/yy;@",
"mmm/dd/yy;@",
"[$-409]d\\-mmm;@",
"[$-409]d\\-mmm\\-yy;@",
"[$-409]dd\\-mmm\\-yy;@",
"[$-409]mmm\\-yy;@",
"[$-409]mmmm\\-yy;@",
"[$-409]mmmm\\ d\\,\\ yyyy;@",
"[$-409]mmm/d/yy\\ h:mm:ss;@",
"[$-409]mmmm/d/yy\\ h:mm:ss am;@",
"[$-409]mmmmm;@",
"[$-409]mmmmm\\-yy;@",
"mmmm/d/yyyy;@",
"[$-409]d\\-mmm\\-yyyy;@"
};
//valid time formats - all should have 11:23 in output
String[] goodTimePatterns =
{
"HH:MM",
"HH:MM:SS",
"HH:MM;HH:MM;HH:MM",
// This is fun - blue if positive time,
// red if negative time or green for zero!
"[BLUE]HH:MM;[RED]HH:MM;[GREEN]HH:MM",
"yyyy-mm-dd hh:mm",
"yyyy-mm-dd hh:mm:ss",
};
// valid number formats
String[] goodNumPatterns =
{
"#,##0.0000",
"#,##0;[Red]#,##0",
"(#,##0.00_);(#,##0.00)",
"($#,##0.00_);[Red]($#,##0.00)",
"$#,##0.00",
"[$-809]#,##0.00", // international format
"[$-2]#,##0.00", // international format
"0000.00000%",
"0.000E+00",
"0.00E+00",
"[BLACK]0.00;[COLOR 5]##.##",
};
// invalid date formats -- will throw exception in DecimalFormat ctor
String[] badNumPatterns =
{
"#,#$'#0.0000",
"'#','#ABC#0;##,##0",
"000 '123 4'5'6 000",
"#''0#0'1#10L16EE"
};
// create cells with good date patterns
for (int i = 0; i < goodDatePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(dateNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodDatePatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(1);
// create cells with time patterns
for (int i = 0; i < goodTimePatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(timeNum);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodTimePatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(2);
// create cells with num patterns
for (int i = 0; i < goodNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(-1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(goodNumPatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
row = sheet.CreateRow(3);
// create cells with bad num patterns
for (int i = 0; i < badNumPatterns.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(1234567890.12345);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat(badNumPatterns[i]));
cell.CellStyle = (/*setter*/ cellStyle);
}
// Built in formats
{ // Zip + 4 format
row = sheet.CreateRow(4);
ICell cell = row.CreateCell(0);
cell.SetCellValue(123456789);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("00000-0000"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // Phone number format
row = sheet.CreateRow(5);
ICell cell = row.CreateCell(0);
cell.SetCellValue(5551234567D);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("[<=9999999]###-####;(###) ###-####"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // SSN format
row = sheet.CreateRow(6);
ICell cell = row.CreateCell(0);
cell.SetCellValue(444551234);
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("000-00-0000"));
cell.CellStyle = (/*setter*/ cellStyle);
}
{ // formula cell
row = sheet.CreateRow(7);
ICell cell = row.CreateCell(0);
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(12.25,12.25)/100");
ICellStyle cellStyle = wb.CreateCellStyle();
cellStyle.DataFormat = (/*setter*/ format.GetFormat("##.00%;"));
cell.CellStyle = (/*setter*/ cellStyle);
}
}
public void ExportExcel(DataTable dt)
{
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 = 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)
{
}
}
public static void WriteToExcel(string filePath, string[] ContentArr, string[] ADContent = null, string[] DAContent = null)
{
if (ContentArr == null || ContentArr.Count() == 0)
{
return;
}
if (ADContent != null && ADContent.Count() == 0)
{
return;
}
if (DAContent != null && DAContent.Count() == 0)
{
return;
}
//创建工作薄
IWorkbook wb;
string extension = System.IO.Path.GetExtension(filePath);
//根据指定的文件格式创建对应的类
if (extension.Equals(".xls"))
{
wb = new HSSFWorkbook();
}
else
{
wb = new XSSFWorkbook();
}
ICellStyle style1 = wb.CreateCellStyle(); //样式
style1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style1.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置边框
style1.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
style1.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
style1.WrapText = true; //自动换行
ICellStyle style2 = wb.CreateCellStyle(); //样式
IFont font1 = wb.CreateFont(); //字体
font1.FontName = "楷体";
font1.Color = HSSFColor.Red.Index; //字体颜色
font1.Boldweight = (short)FontBoldWeight.Normal; //字体加粗样式
style2.SetFont(font1); //样式里的字体设置具体的字体样式
//设置背景色
style2.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.FillPattern = FillPattern.SolidForeground;
style2.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Yellow.Index;
style2.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
style2.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
ICellStyle dateStyle = wb.CreateCellStyle(); //样式
dateStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left; //文字水平对齐方式
dateStyle.VerticalAlignment = NPOI.SS.UserModel.VerticalAlignment.Center; //文字垂直对齐方式
//设置数据显示格式
IDataFormat dataFormatCustom = wb.CreateDataFormat();
dateStyle.DataFormat = dataFormatCustom.GetFormat("yyyy-MM-dd HH:mm:ss");
//创建一个表单
ISheet sheet = wb.CreateSheet("总表");
//设置列宽
int[] columnWidth = { 20, 20, 20, 20, 20, 20, 20 };
for (int i = 0; i < columnWidth.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet.SetColumnWidth(i, 256 * columnWidth[i]);
}
//测试数据
int rowCount = ContentArr.Count(), columnCount = (ContentArr[0].Split(',')).Count();
IRow row;
ICell cell;
for (int i = 0; i < rowCount; i++)
{
row = sheet.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
//cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
string obj = ContentArr[i].Split(',')[j];
cell.SetCellValue(obj);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
//合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
ISheet sheet2 = wb.CreateSheet("DA表");
int[] columnWidth1 = { 20, 20, 20, 20 };
for (int i = 0; i < columnWidth1.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet2.SetColumnWidth(i, 256 * columnWidth1[i]);
}
//测试数据
rowCount = DAContent.Count(); columnCount = (DAContent[0].Split(',')).Count();
for (int i = 0; i < rowCount; i++)
{
row = sheet2.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
//cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
string obj = DAContent[i].Split(',')[j];
cell.SetCellValue(obj);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
ISheet sheet3 = wb.CreateSheet("AD表");
int[] columnWidth2 = { 20, 20, 20, 20 };
for (int i = 0; i < columnWidth2.Length; i++)
{
//设置列宽度，256*字符数，因为单位是1/256个字符
sheet3.SetColumnWidth(i, 256 * columnWidth2[i]);
}
//测试数据
rowCount = ADContent.Count(); columnCount = (ADContent[0].Split(',')).Count();
for (int i = 0; i < rowCount; i++)
{
row = sheet3.CreateRow(i);//创建第i行
for (int j = 0; j < columnCount; j++)
{
cell = row.CreateCell(j);//创建第j列
//cell.CellStyle = j % 2 == 0 ? style1 : style2;
//根据数据类型设置不同类型的cell
string obj = ADContent[i].Split(',')[j];
cell.SetCellValue(obj);
//如果是日期，则设置日期显示的格式
if (obj.GetType() == typeof(DateTime))
{
cell.CellStyle = dateStyle;
}
//如果要根据内容自动调整列宽，需要先setCellValue再调用
//sheet.AutoSizeColumn(j);
}
}
//合并单元格，如果要合并的单元格中都有数据，只会保留左上角的
//CellRangeAddress(0, 2, 0, 0)，合并0-2行，0-0列的单元格
try
{
FileStream fs = File.OpenWrite(filePath);
wb.Write(fs);//向打开的这个Excel文件中写入表单并保存。
fs.Close();
}
catch (Exception e)
{
return;
}
}
public void TestUnicodeInAll()
{
TestCases.CultureShim.SetCurrentCulture("en-US");
IWorkbook wb = _testDataProvider.CreateWorkbook();
ICreationHelper factory = wb.GetCreationHelper(/*getter*/);
//Create a unicode dataformat (Contains euro symbol)
IDataFormat df = wb.CreateDataFormat();
String formatStr = "_([$\u20ac-2]\\\\\\ * #,##0.00_);_([$\u20ac-2]\\\\\\ * \\\\\\(#,##0.00\\\\\\);_([$\u20ac-2]\\\\\\ *\\\"\\-\\\\\"??_);_(@_)";
short fmt = df.GetFormat(formatStr);
//Create a unicode sheet name (euro symbol)
ISheet s = wb.CreateSheet("\u20ac");
//Set a unicode header (you guessed it the euro symbol)
IHeader h = s.Header;
h.Center = (/*setter*/ "\u20ac");
h.Left = (/*setter*/ "\u20ac");
h.Right = (/*setter*/ "\u20ac");
//Set a unicode footer
IFooter f = s.Footer;
f.Center = (/*setter*/ "\u20ac");
f.Left = (/*setter*/ "\u20ac");
f.Right = (/*setter*/ "\u20ac");
IRow r = s.CreateRow(0);
ICell c = r.CreateCell(1);
c.SetCellValue(12.34);
c.CellStyle.DataFormat = (/*setter*/ fmt);
ICell c2 = r.CreateCell(2); // TODO - c2 unused but changing next line ('c'->'c2') causes Test to fail
c.SetCellValue(factory.CreateRichTextString("\u20ac"));
ICell c3 = r.CreateCell(3);
String formulaString = "TEXT(12.34,\"\u20ac###,##\")";
c3.CellFormula = (/*setter*/ formulaString);
wb = _testDataProvider.WriteOutAndReadBack(wb);
//Test the sheetname
s = wb.GetSheet("\u20ac");
Assert.IsNotNull(s);
//Test the header
h = s.Header;
Assert.AreEqual(h.Center, "\u20ac");
Assert.AreEqual(h.Left, "\u20ac");
Assert.AreEqual(h.Right, "\u20ac");
//Test the footer
f = s.Footer;
Assert.AreEqual(f.Center, "\u20ac");
Assert.AreEqual(f.Left, "\u20ac");
Assert.AreEqual(f.Right, "\u20ac");
//Test the dataformat
r = s.GetRow(0);
c = r.GetCell(1);
df = wb.CreateDataFormat();
Assert.AreEqual(formatStr, df.GetFormat(c.CellStyle.DataFormat));
//Test the cell string value
c2 = r.GetCell(2);
Assert.AreEqual(c.RichStringCellValue.String, "\u20ac");
//Test the cell formula
c3 = r.GetCell(3);
Assert.AreEqual(c3.CellFormula, formulaString);
}
/// <summary>
/// 导出多个工作簿
/// </summary>
/// <param name="dicSheet">工作簿名：数据表</param>
/// <param name="fullPathName">物理路径 + 文件名称 + 格式</param>
/// <returns></returns>
public static bool DataTableToExcel(Dictionary <string, DataTable> dicSheet, string fullPathName)
{
try
{
IWorkbook workbook = new HSSFWorkbook();
if (fullPathName.ToLower().Contains(".xlsx"))
{
workbook = new XSSFWorkbook();
}
foreach (var sheetitem in dicSheet.Keys)
{
var dt = dicSheet[sheetitem];
ISheet sheet = workbook.CreateSheet(sheetitem);
//标题样式
ICellStyle hStyle = workbook.CreateCellStyle();
hStyle.BorderBottom = BorderStyle.Thin;
hStyle.BorderLeft = BorderStyle.Thin;
hStyle.BorderRight = BorderStyle.Thin;
hStyle.BorderTop = BorderStyle.Thin;
//水平垂直居中
hStyle.Alignment = HorizontalAlignment.Center;
hStyle.VerticalAlignment = VerticalAlignment.Center;
//背景颜色
hStyle.FillForegroundColor = 9;
hStyle.FillPattern = FillPattern.SolidForeground;
////用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
headerRow.Height = 20 * 20;
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
//单元格字体
IFont font = workbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 10;
font.Color = 8;
font.Boldweight = (short)FontBoldWeight.Bold;
hStyle.SetFont(font);
cell.CellStyle = hStyle;
icolIndex++;
}
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//创建CellStyle与DataFormat并加载格式样式
IDataFormat dataformat = workbook.CreateDataFormat();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = dataformat.GetFormat("@");
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
DataRow.Height = 20 * 16;
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
}
//写Excel
using (FileStream file = new FileStream(fullPathName, FileMode.OpenOrCreate))
{
workbook.Write(file);
}
return(true);
}
catch (Exception)
{
return(false);
}
}
/**
* create a library of cell styles
*/
private static Dictionary <String, ICellStyle> createStyles(IWorkbook wb)
{
Dictionary <String, ICellStyle> styles = new Dictionary <String, ICellStyle>();
IDataFormat df = wb.CreateDataFormat();
ICellStyle style;
IFont headerFont = wb.CreateFont();
headerFont.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
styles.Add("header", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.FillForegroundColor = (IndexedColors.LightCornflowerBlue.Index);
style.FillPattern = FillPattern.SolidForeground;
style.SetFont(headerFont);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("header_date", style);
IFont font1 = wb.CreateFont();
font1.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
styles.Add("cell_b_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_b_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_g", style);
IFont font2 = wb.CreateFont();
font2.Color = (IndexedColors.Blue.Index);
font2.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font2);
styles.Add("cell_bb", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font1);
style.FillForegroundColor = (IndexedColors.Grey25Percent.Index);
style.FillPattern = FillPattern.SolidForeground;
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_bg", style);
IFont font3 = wb.CreateFont();
font3.FontHeightInPoints = ((short)14);
font3.Color = (IndexedColors.DarkBlue.Index);
font3.IsBold = true;
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.SetFont(font3);
style.WrapText = (true);
styles.Add("cell_h", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
styles.Add("cell_normal_centered", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.WrapText = (true);
style.DataFormat = (df.GetFormat("d-mmm"));
styles.Add("cell_normal_date", style);
style = CreateBorderedStyle(wb);
style.Alignment = HorizontalAlignment.Center;
style.Indention = ((short)1);
style.WrapText = (true);
styles.Add("cell_indented", style);
style = CreateBorderedStyle(wb);
style.FillForegroundColor = (IndexedColors.Blue.Index);
style.FillPattern = FillPattern.SolidForeground;
styles.Add("cell_blue", style);
return(styles);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：信息工程学院2014－2015学年第一学期教学检查听课安排）</param>
private MemoryStream Export(List <ExportExcelModel> dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "破晓技术团队";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "管理员名册"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (ExportExcelModel row in dtSource)
{
// 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
// 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
//
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
rowIndex = 2;
}
//填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
//！！！！！！！！！！！！！！！！！！！！！！！如果没有职称可能会异常
DistinctSupervisor(dtSource[j].supervisors, ListSupervisor);//去督导员的职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
//序号
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
//课程
newCell.SetCellValue(dtSource[j].classname.ToString() + "\t");
break;
case 2:
//授课内容
newCell.SetCellValue(dtSource[j].classcontent.ToString() + "\t");
break;
case 3:
//授课方式
newCell.SetCellValue(dtSource[j].classtype.ToString() + "\t");
break;
case 4:
//专业
newCell.SetCellValue(dtSource[j].major.ToString() + "\t");
break;
case 5:
//教室
newCell.SetCellValue(dtSource[j].classroom.ToString() + "\t");
break;
case 6:
//教师
newCell.SetCellValue(dtSource[j].teachername.ToString() + "\t");
break;
case 7:
//周次
newCell.SetCellValue(dtSource[j].week.ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(dtSource[j].time + "\t");
break;
case 9:
//听课人员安排
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
//分数
newCell.SetCellValue(" " + "\t");
break;
}
}
else
{
//申报
newCell.SetCellValue(" ");
}
}
j++;
rowIndex++;
}
adjustcolum(sheet); //调整列宽
AddBorder(sheet, workbook); //加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <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 = GetProperties(type, columns);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#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 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
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();
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.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);
}
}
public async Task <IActionResult> GenerateBlankTemplateAsync()
{
var TheStream = new NPOIMemoryStream {
AllowClose = false
};
var IntegerFields = new List <string> {
"*** Surveyor Number", "DeedVolume", "DeedPage", "AutomatedFileNumber"
};
var FileName = $"PLSO_Upload_Template {DateTime.Now.ToString("yy-MMdd")}-{DateTime.Now.Ticks}.xlsx";
try {
var Columns = await excelTemplateRepo.GetTemplateColumnsAsync();
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("PLSO Record Import");
IsBold = CreateBoldStyle(workbook);
IsBlue = CreateBlueStyle(workbook);
IsValidation = CreateValidationStyle(workbook);
IsInteger = CreateIntegerStyle(workbook);
int RowIndex = 0;
IRow row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
sheet1.SetColumnWidth(col.ColumnIndex - 1, (col.ColumnWidth * 256));
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.DisplayName);
} // foreach of the columns on the Display Name row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue(col.ExampleData);
Cell.CellStyle = IsBlue;
} // foreach of the columns on the Example Data row
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
Cell.SetCellType(CellType.String);
Cell.SetCellValue((col.IsRequired ? "REQUIRED: " : "") + col.Validation);
Cell.CellStyle = IsValidation;
Cell.CellStyle.WrapText = true;
} // foreach of the columns on the Validation row
for (var index = 1; index < 6; index++)
{
row = sheet1.CreateRow(RowIndex++);
foreach (var col in Columns.Result)
{
var Cell = row.CreateCell(col.ColumnIndex - 1);
if (col.IsRequired)
{
Cell.CellStyle = IsBold;
}
if (col.FieldName == "SurveyDate")
{
IDataFormat dataFormat = workbook.CreateDataFormat();
Cell.CellStyle.DataFormat = dataFormat.GetFormat("M/d/yyyy");
}
else if (IntegerFields.Contains(col.FieldName))
{
Cell.SetCellType(CellType.Numeric);
Cell.CellStyle = IsInteger;
}
else
{
Cell.SetCellType(CellType.String);
}
} // foreach of the columns on the Display Name row
} // for 5 rows add formatting and make the cell bold if required
XSSFFormulaEvaluator.EvaluateAllFormulaCells(workbook);
workbook.Write(TheStream);
} catch (Exception e) {
TheStream.Dispose();
logger.LogError(1, e, "Unable to export Blank Template to Excel");
}
TheStream.Seek(0, SeekOrigin.Begin);
TheStream.AllowClose = true;
return(File(TheStream, "application/vnd.ms-excel", FileName));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] 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 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
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 NPOI.SS.Util.CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 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);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.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 (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream NpoiExport(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
//GBK对应的code page是CP936
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;
}
}
}
#endregion
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#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(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 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);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = contentStyle;
string 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 (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param Name="dtSource">DataTable数据源</param>
/// <param Name="strHeaderText">Excel表头文本（例如：车辆列表）</param>
public MemoryStream Export(DataTable dtSource, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
int j = 0;
foreach (DataRow row in dtSource.Rows)
{
#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(strHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dcs.Count - 1)); // ------------------
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 800;
font.FontName = "宋体";
headStyle.SetFont(font);
int index = 0;
foreach (string column in dcs)
{
headerRow.CreateCell(index).SetCellValue(column);
headerRow.GetCell(index).CellStyle = headStyle;
sheet.AutoSizeColumn(index);
//设置列宽
//sheet.SetColumnWidth(index, (arrColWidth[index] + 1) * 256);
//sheet.SetColumnWi
index++;
}
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
DistinctSupervisor(dtSource.Rows[j][6].ToString(), ListSupervisor); //去职称
for (int i = 0; i < dcs.Count; i++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
columnIndex++;
if (i != dcs.Count - 1)
{
//注意这个在导出的时候加了“\t” 的目的就是避免导出的数据显示为科学计数法。可以放在每行的首尾。
switch (i)
{
case 0:
newCell.SetCellValue((j + 1).ToString() + "\t");
break;
case 1:
newCell.SetCellValue(dtSource.Rows[j][8].ToString() + "\t");
break;
case 2:
newCell.SetCellValue(dtSource.Rows[j][9].ToString() + "\t");
break;
case 3:
newCell.SetCellValue(dtSource.Rows[j][10].ToString() + "\t");
break;
case 4:
newCell.SetCellValue(dtSource.Rows[j][11].ToString() + "\t");
break;
case 5:
newCell.SetCellValue(dtSource.Rows[j][7].ToString() + "\t");
break;
case 6:
newCell.SetCellValue(dtSource.Rows[j][2].ToString() + "\t");
break;
case 7:
newCell.SetCellValue(dtSource.Rows[j][3].ToString() + "\t");
break;
case 8:
//听课时间
newCell.SetCellValue(
CalendarTools.getdata(Common.Year, Convert.ToInt32(dtSource.Rows[j][3]), Convert.ToInt32(dtSource.Rows[j][4]) - CalendarTools.weekdays(CalendarTools.CaculateWeekDay(Common.Year, Common.Month, Common.Day)), Common.Month, Common.Day).ToLongDateString()
+ " " + addseparator(Convert.ToInt32(dtSource.Rows[j][5])) + "节" + "\t");
break;
case 9:
newCell.SetCellValue(FormatSupervisor(ListSupervisor) + "\t");
break;
case 10:
newCell.SetCellValue(dtSource.Rows[j][12].ToString() + "\t");
break;
}
}
else
{
newCell.SetCellValue(" ");
}
}
#endregion
j++;
rowIndex++;
}
adjustcolum(sheet); //调整列宽
AddBorder(sheet, workbook); //加边框
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
/// <summary>
/// 导出数据到数据流
/// </summary>
/// <param name="dataSet">要导出的数据</param>
/// <param name="stream">要导出到的数据流</param>
/// <exception cref="ArgumentException">参数异常</exception>
public static void ExportToStream(DataSet dataSet, Stream stream)
{
if (dataSet == null) throw new ArgumentException("无效的导入数据", "dataSet");
if (stream == null) throw new ArgumentException("目标数据流无效", "stream");
//创建工作簿
IWorkbook workbook = new HSSFWorkbook();
//创建日期的显示样式
_dateStyle = workbook.CreateCellStyle();
_dataFormat = workbook.CreateDataFormat();
_dateStyle.DataFormat = _dataFormat.GetFormat(_dataType);
DataTableCollection tables = dataSet.Tables;
for (int i = 0; i < tables.Count; i++) //每张表
{
//当前表的总行数
DataRowCollection rows = tables[i].Rows;
//如果表里面没有数据,忽略这个表
if (rows.Count < 1) continue;
//当前表的列集合
var columns = tables[i].Columns;
//创建表
string tableName = string.IsNullOrEmpty(tables[i].TableName) ? "Sheet" + i : tables[i].TableName;
var sheet = workbook.CreateSheet(tableName);
//创建表头
var titleRow = sheet.CreateRow(0);
for (int j = 0; j < columns.Count; j++)
{
string columnName = string.IsNullOrEmpty(columns[j].ColumnName) ? "Cell" + j : columns[j].ColumnName;
var cell = titleRow.CreateCell(j);
cell.SetCellValue(columnName);
}
//定义行开始索引
int rowIndex = 1;
foreach (DataRow dataRow in tables[i].Rows) //每一行
{
var row = sheet.CreateRow(rowIndex);
rowIndex++;
for (int j = 0; j < columns.Count; j++) //每一个格子
{
//var cellValue = dataRow[j] == DBNull.Value ? string.Empty : dataRow[j].ToString();
var cell = row.CreateCell(j);
//如果该属性为null,设置单元格为空格
if (dataRow[j] == null)
{
cell.SetCellType(CellType.Blank);
return;
}
//判断单元格类型
switch (columns[j].DataType.Name.ToLower())
{
case "char":
case "string":
cell.SetCellValue(Convert.ToString(dataRow[j]));
break;
case "double":
case "single":
case "int32":
cell.SetCellValue(Convert.ToDouble(dataRow[j]));
break;
case "boolean":
cell.SetCellValue(Convert.ToBoolean(dataRow[j]));
break;
case "datetime":
cell.SetCellValue(Convert.ToDateTime(dataRow[j]));
cell.CellStyle = _dateStyle;
break;
default:
cell.SetCellValue(dataRow[j].ToString());
break;
}
}
}
}
workbook.Write(stream);
}
