public void exportVariety(string varietyName, int agreement, DateTime startingDate, DateTime endingDate)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1");
setColumnWidth(sheet);
initStyle(workbook);
//第一行，品种、起止时间
XSSFRow row0 = (XSSFRow)sheet.CreateRow(0);
setRowHeight(row0);
XSSFCell cell = (XSSFCell)row0.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(varietyName + Variety.getAgreementName(agreement));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
cell = (XSSFCell)row0.CreateCell(2);
cell.CellStyle = headerStyle;
cell = (XSSFCell)row0.CreateCell(1);
cell.CellStyle = headerStyle;
cell.SetCellValue(Utils.formatDate(startingDate) + "-" + Utils.formatDate(endingDate));
excelFilePath = Utils.getExportDir() + varietyName + "_" + Variety.getAgreementName(agreement) + "_" + Utils.getDate() + "_回测" + "_" + Utils.getTimeMillisecond() + ".xlsx";
FileStream file = new FileStream(excelFilePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
private static void CopyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List <CellRangeAddress> mergedRegions)
{
destRow.Height = srcRow.Height;
for (int j = srcRow.FirstCellNum; srcRow.LastCellNum >= 0 && j <= srcRow.LastCellNum; j++)
{
var oldCell = (HSSFCell)srcRow.GetCell(j);
var newCell = (XSSFCell)destRow.GetCell(j);
if (oldCell != null)
{
if (newCell == null)
{
newCell = (XSSFCell)destRow.CreateCell(j);
}
CopyCell(oldCell, newCell);
var mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
if (mergedRegion != null)
{
var newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
destSheet.AddMergedRegion(newMergedRegion);
}
}
}
}
}
/**
* Shift merged regions
*
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @return an array of affected cell regions
*/
public List <CellRangeAddress> ShiftMerged(int startRow, int endRow, int n)
{
List <CellRangeAddress> ShiftedRegions = new List <CellRangeAddress>();
NPOI.Util.Collections.HashSet <int> removedIndices = new NPOI.Util.Collections.HashSet <int>();
//move merged regions completely if they fall within the new region boundaries when they are Shifted
int size = sheet.NumMergedRegions;
for (int i = 0; i < size; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
if (merged == null)
{
continue;
}
bool inStart = (merged.FirstRow >= startRow || merged.LastRow >= startRow);
bool inEnd = (merged.FirstRow <= endRow || merged.LastRow <= endRow);
//don't check if it's not within the Shifted area
if (!inStart || !inEnd)
{
continue;
}
//only shift if the region outside the Shifted rows is not merged too
if (!ContainsCell(merged, startRow - 1, 0) && !ContainsCell(merged, endRow + 1, 0))
{
merged.FirstRow = (merged.FirstRow + n);
merged.LastRow = (merged.LastRow + n);
//have to Remove/add it back
ShiftedRegions.Add(merged);
removedIndices.Add(i);
}
}
if (removedIndices.Count > 0)
{
sheet.RemoveMergedRegions(removedIndices);
}
//read so it doesn't get Shifted again
foreach (CellRangeAddress region in ShiftedRegions)
{
sheet.AddMergedRegion(region);
}
return(ShiftedRegions);
}
/// <summary>
/// Копирование содежимого ячеек
/// </summary>
/// <param name="srcSheet"></param>
/// <param name="destSheet"></param>
/// <param name="srcRow"></param>
/// <param name="destRow"></param>
/// <param name="mergedRegions"></param>
private void CopyRow(HSSFSheet srcSheet, XSSFSheet destSheet, HSSFRow srcRow, XSSFRow destRow, List <CellRangeAddress> mergedRegions)
{
// Копирование высоты строки
destRow.Height = srcRow.Height;
for (int j = srcRow.FirstCellNum; srcRow.LastCellNum >= 0 && j <= srcRow.LastCellNum; j++)
{
var oldCell = (HSSFCell)srcRow.GetCell(j);
var newCell = (XSSFCell)destRow.GetCell(j);
if (oldCell != null)
{
// создание новой ячейки в новой таблице
if (newCell == null)
{
newCell = (XSSFCell)destRow.CreateCell(j);
}
CopyCell(oldCell, newCell);
// Ниже идет обработка объединенных ячеек
// Проверка на вхождение текущей ячейки в число объединенных
var mergedRegion = GetMergedRegion(srcSheet, srcRow.RowNum,
(short)oldCell.ColumnIndex);
// Если ячейка является объединенное
if (mergedRegion != null)
{
// Проверяем обработывали ли мы уже группу объединенных ячеек или нет
var newMergedRegion = new CellRangeAddress(mergedRegion.FirstRow,
mergedRegion.LastRow, mergedRegion.FirstColumn, mergedRegion.LastColumn);
// Если не обрабатывали, то добавляем в текущий диапазон оъединенных ячеек текущую ячейку
if (IsNewMergedRegion(newMergedRegion, mergedRegions))
{
mergedRegions.Add(newMergedRegion);
destSheet.AddMergedRegion(newMergedRegion);
}
}
}
}
}
/**
* Shift merged regions
*
* @param startRow the row to start Shifting
* @param endRow the row to end Shifting
* @param n the number of rows to shift
* @return an array of affected cell regions
*/
public List <CellRangeAddress> ShiftMerged(int startRow, int endRow, int n)
{
List <CellRangeAddress> ShiftedRegions = new List <CellRangeAddress>();
//move merged regions completely if they fall within the new region boundaries when they are Shifted
for (int i = 0; i < sheet.NumMergedRegions; i++)
{
CellRangeAddress merged = sheet.GetMergedRegion(i);
bool inStart = (merged.FirstRow >= startRow || merged.LastRow >= startRow);
bool inEnd = (merged.FirstRow <= endRow || merged.LastRow <= endRow);
//don't check if it's not within the Shifted area
if (!inStart || !inEnd)
{
continue;
}
//only shift if the region outside the Shifted rows is not merged too
if (!ContainsCell(merged, startRow - 1, 0) && !ContainsCell(merged, endRow + 1, 0))
{
merged.FirstRow = (merged.FirstRow + n);
merged.LastRow = (merged.LastRow + n);
//have to Remove/add it back
ShiftedRegions.Add(merged);
sheet.RemoveMergedRegion(i);
i = i - 1; // we have to back up now since we Removed one
}
}
//read so it doesn't get Shifted again
foreach (CellRangeAddress region in ShiftedRegions)
{
sheet.AddMergedRegion(region);
}
return(ShiftedRegions);
}
public string ExciseRpt(List <ExciseFreeApply> data)
{
string templateFilePath = Path.Combine(HostingEnvironment.MapPath("~/ExcelTemplate"), "ExciseFreeApplyTemplate.xlsx");
// Open Template
FileStream fs = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);
// Load the template into a NPOI workbook
XSSFWorkbook templateWorkbook = new XSSFWorkbook(fs);
// Load the sheet you are going to use as a template into NPOI
XSSFSheet sheet = (XSSFSheet)templateWorkbook.GetSheetAt(0);
// 建立新頁籤並命名Rpt1
templateWorkbook.CreateSheet("Rpt1");
XSSFSheet sheet2 = (XSSFSheet)templateWorkbook.GetSheetAt(1);
//寬度格式設定
int[] ColWidthSetArr = new int[] { 1, 13, 32, 4, 7,
9, 10, 1, 11, 1 };
SetSheetColumnWidth(ref sheet2, ColWidthSetArr);
//設定將所有欄放入單一頁面中
sheet2.FitToPage = true;
sheet2.PrintSetup.FitWidth = 1;
sheet2.PrintSetup.FitHeight = 0;
//設定列印格式為A4(A3=8,A4=9,Letter=1)
sheet2.PrintSetup.PaperSize = 9;
//基本參數
int originalSheetLastRow = sheet.LastRowNum;
int headerRowLen = 7;
int templateBodyRow = 7;
int footerRowStart = 14;
int footerRowLen = originalSheetLastRow - footerRowStart;
//欄位資訊
int newSheetRowLen = headerRowLen + data.Count + (sheet.LastRowNum - footerRowStart);
int newSheetPageNum = Convert.ToInt16(Math.Ceiling(newSheetRowLen / 44.0));
var tempdata = new ExciseFreeApply();
tempdata.ID = data.Count == 0 ? "" : data[0].ID;
tempdata.Name = data.Count == 0 ? "" : data[0].Name;
tempdata.Address = data.Count == 0 ? "" : data[0].Address;
//表頭
ReplaceRowCellValue(ref sheet, 5, 1, "#0", tempdata.ID);
//表尾
ReplaceRowCellValue(ref sheet, 17, 4, "#1", tempdata.Name);
ReplaceRowCellValue(ref sheet, 18, 4, "#2", tempdata.Address);
for (var i = 0; i < headerRowLen; i++)
{
CopySheetRow(ref sheet, ref sheet2, i, i, false, false, true, false);
}
int sheet2Last = sheet2.LastRowNum + 1;
for (var i = 0; i < data.Count; i++)
{
if (i == 0)
{
SetRowCellValue(ref sheet, templateBodyRow, 1, data[i].ProdTaxNumber);
SetRowCellValue(ref sheet, templateBodyRow, 2, data[i].ProdEngName + data[i].ProdChName);
SetRowCellValue(ref sheet, templateBodyRow, 3, data[i].TaxUnits);
SetRowCellValue(ref sheet, templateBodyRow, 4, (double)data[i].Qty);
SetRowCellValue(ref sheet, templateBodyRow, 5, ToSimpleTaiwanDate(data[i].ProcessDate));
SetRowCellValue(ref sheet, templateBodyRow, 6, data[i].SheetNumber);
SetRowCellValue(ref sheet, templateBodyRow, 7, data[i].Mode);
CopySheetRow(ref sheet, ref sheet2, templateBodyRow, sheet2Last + i, false, false, true, true);
}
else
{
SetRowCellValue(ref sheet, templateBodyRow + 1, 1, data[i].ProdTaxNumber);
SetRowCellValue(ref sheet, templateBodyRow + 1, 2, data[i].ProdEngName + data[i].ProdChName);
SetRowCellValue(ref sheet, templateBodyRow + 1, 3, data[i].TaxUnits);
SetRowCellValue(ref sheet, templateBodyRow + 1, 4, (double)data[i].Qty);
SetRowCellValue(ref sheet, templateBodyRow + 1, 5, ToSimpleTaiwanDate(data[i].ProcessDate));
SetRowCellValue(ref sheet, templateBodyRow + 1, 6, data[i].SheetNumber);
SetRowCellValue(ref sheet, templateBodyRow + 1, 7, data[i].Mode);
CopySheetRow(ref sheet, ref sheet2, templateBodyRow + 1, sheet2Last + i, false, false, false, false);
}
}
sheet2.CreateRow(sheet2.LastRowNum + 1);
sheet2.CopyRow(sheet2.LastRowNum - 1, sheet2.LastRowNum + 1);
sheet2Last = sheet2.LastRowNum + 1;
for (var i = 0; i < footerRowLen; i++)
{
CopySheetRow(ref sheet, ref sheet2, footerRowStart + i, sheet2Last + i, false, false, true, false);
}
//自動調整欄位寬度，此欄位為數量
sheet2.AutoSizeColumn(4);
//合併儲存格
if (data.Count > 0)
{
RemoveMergeCells(ref sheet2, headerRowLen);
sheet2.AddMergedRegion(new CellRangeAddress(headerRowLen, sheet2.LastRowNum - footerRowLen, 8, 8));
}
string filePath = @"C:\temp\excel";
//建folder
if (!Directory.Exists(filePath))
{
Directory.CreateDirectory(filePath);
}
//新完整檔名
string newFileName = "export.xlsx";
//檔案路徑 + 新完整檔名
string fullFilePath = Path.Combine(filePath, newFileName);
FileStream file = new FileStream(fullFilePath, FileMode.Create);//產生檔案
templateWorkbook.Write(file);
file.Close();
GC.Collect();
return(fullFilePath);
}
public void MergeCell(int x1, int y1, int x2, int y2)
{
CellRangeAddress range = new CellRangeAddress(y1, y2, x1, x2);
sheet.AddMergedRegion(range);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream Export()
/// </summary>
/// <param name="dtSource">DataTable数据源</param>
/// <param name="excelConfig">导出设置包含文件名、标题、列设置</param>
public static void MemoryStream(DataTable dtSource, ExcelConfig excelConfig)
{
try {
int colint = 0;
bool cellValue = false;
for (int i = 0; i < dtSource.Columns.Count;)
{
DataColumn column = dtSource.Columns[i];
if (excelConfig.ColumnEntity[colint].Column != column.ColumnName)
{
dtSource.Columns.Remove(column.ColumnName);
}
else
{
i++;
colint++;
}
}
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
int rowIndex = 0;
//判断是否已存在文件
if (File.Exists(excelConfig.FileName))
{
FileStream fs = new FileStream(@excelConfig.FileName, FileMode.Open, FileAccess.ReadWrite);
workbook = new XSSFWorkbook(fs);
fs.Close();
sheet = workbook.GetSheetAt(0) as XSSFSheet;
cellValue = true;
rowIndex = sheet.LastRowNum + 1;
}
#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");
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (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);
if (arrColWidth[column.Ordinal] > 255)
{
arrColWidth[column.Ordinal] = 254;
}
else
{
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();
//排列序号
if (column.ColumnName == "Id")
{
drValue = (rowIndex - 1).ToString();
}
if (drValue.Length >= 255)
{
drValue = drValue.Remove(255, drValue.Length - 255);
}
SetCell(newCell, dateStyle, column.DataType, drValue);
}
#endregion
rowIndex++;
}
sheet.ForceFormulaRecalculation = true;
//创建或打开文件填充数据
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
byte[] data = ms.ToArray();
using (FileStream fs1 = File.OpenWrite(@excelConfig.FileName))
{
fs1.Write(data, 0, data.Length);
fs1.Flush();
fs1.Close();
Console.WriteLine("下载完成！");
}
ms.Flush();
}
}
catch (Exception e) {
Console.WriteLine("下载失败,休眠2S！========================>" + e.ToString());
Thread.Sleep(2000);
MemoryStream(dtSource, excelConfig);
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "http://www.yongfa365.com/";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "柳永法"; //填加xls文件作者信息
// si.ApplicationName = "NPOI测试程序"; //填加xls文件创建程序信息
// si.LastAuthor = "柳永法2"; //填加xls文件最后保存者信息
// si.Comments = "说明信息"; //填加xls文件作者信息
// si.Title = "NPOI测试"; //填加xls文件标题信息
// si.Subject = "NPOI测试Demo"; //填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
#endregion
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
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 == 0)
{
#region 表头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
CellRangeAddress region = new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1); //改的
sheet.AddMergedRegion(region);
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow; //第一列，换1则第一行空余
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
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);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 2; //开始填充数据的列
}
#endregion
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
double result;
if (isNumeric(drValue, out result))
{
double.TryParse(drValue, out result);
newCell.SetCellValue(result);
break;
}
else
{
newCell.SetCellValue(drValue);
break;
}
case "System.float": //空
case "System.DateTime": //日期类型
DateTime dateV;
if (drValue == null || drValue == "" || drValue == "-1")
{
newCell.SetCellValue(" ");
}
else
{
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;
if (drValue == null || drValue == "")
{
newCell.SetCellValue(" ");
}
else
{
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
}
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
/// <summary>
/// 通过模板生成EXCEL
/// </summary>
/// <param name="workbook"></param>
/// <param name="dataList">sheet集合</param>
/// <param name="temName">模板名称</param>
/// <returns></returns>
public static byte[] writeExcelToFile(XSSFWorkbook workbook, List <ExcelSheetModel> dataList, String temName)
{
// 列宽一个像素的固定值
//decimal pixCellConst = 31.94888178913738m;
// 行宽一个像素的固定值
//decimal pixRowConst = 15.15151515151515m;
String location = null;
XSSFRow row = null;
foreach (ExcelSheetModel dataSheet in dataList)
{
if (!string.IsNullOrEmpty(dataSheet.sheetType))
{
}
else
{
#region 自定义模式
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(dataSheet.sheetName);
// 自动更新公式
sheet.ForceFormulaRecalculation = true;
foreach (ExcelCellModel dataCell in dataSheet.dataList)
{
if (!string.IsNullOrEmpty(dataCell.location))
{
location = dataCell.location.ToUpper();
int dataNum = 0;
int dataRow = 0;
string rowStr = "";
string columnStr = "";
for (int i = 0; i < location.Length; i++)
{
var locationArr = location.ToCharArray();
if (locationArr[i] >= 48 && locationArr[i] <= 57)
{
columnStr += locationArr[i];
}
if (locationArr[i] >= 65 && locationArr[i] <= 90)
{
rowStr += locationArr[i];
}
}
for (int i = 0; i < rowStr.Length; i++)
{
var rowStrArr = rowStr.ToCharArray();
char ch = rowStrArr[rowStr.Length - i - 1];
dataNum = (int)(ch - 'A' + 1);
dataNum *= int.Parse(Math.Pow(26, i).ToString());
dataRow += dataNum;
}
dataCell.rowNum = int.Parse(columnStr) - 1;
dataCell.cellNum = dataRow - 1;
}
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->Strat");
XSSFCellStyle setBorder = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFFont font = (XSSFFont)workbook.CreateFont();
XSSFColor fontColor = null;
XSSFColor backColor = null;
if (row == null || row.RowNum != dataCell.rowNum)
{
row = (XSSFRow)sheet.GetRow(dataCell.rowNum);
}
XSSFCell cell = (XSSFCell)row.CreateCell(dataCell.cellNum);
// check合并单元格
if (dataCell.regionCell != null)
{
if (dataCell.regionCell.Length == 2)
{
if (dataCell.rowNum > dataCell.regionCell[0])
{
//logger.Debug("RegionCella结束行小于当前行");
}
else if (dataCell.cellNum > dataCell.regionCell[1])
{
//logger.Debug("RegionCella结束列小于当前列");
}
else
{
sheet.AddMergedRegion(
new CellRangeAddress(dataCell.rowNum, dataCell.regionCell[0],
dataCell.cellNum, dataCell.regionCell[1]));
}
}
else
{
//logger.Debug("RegionCell参数异常");
}
}
// check列宽
if (dataCell.cellWidth != 0)
{
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* int.Parse(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString())).ToString()));
* //*/
/*
* sheet.SetColumnWidth(dataCell.cellNum,
* Convert.ToInt32(decimal.Multiply(pixCellConst, decimal.Parse(dataCell.cellWidth.ToString()))));
* //*/
sheet.SetColumnWidth(dataCell.cellNum, dataCell.cellWidth);
}
// check行高
if (dataCell.cellHeight != 0)
{
row.Height = dataCell.cellHeight;
}
// check字体颜色
if (dataCell.cellColor != null)
{
if (dataCell.cellColor.Length == 3)
{
if (((dataCell.cellColor[0] >= 0) || (dataCell.cellColor[0] <= 255)) &&
((dataCell.cellColor[1] >= 0) || (dataCell.cellColor[1] <= 255)) &&
((dataCell.cellColor[2] >= 0) || (dataCell.cellColor[2] <= 255)))
{
fontColor = new XSSFColor(Color.FromArgb(dataCell.cellColor[0],
dataCell.cellColor[1], dataCell.cellColor[2]));
font.SetColor(fontColor);
}
}
}
// check背景色
if (dataCell.backColor != null)
{
if (dataCell.backColor.Length == 3)
{
if (((dataCell.backColor[0] >= 0) || (dataCell.backColor[0] <= 255)) &&
((dataCell.backColor[1] >= 0) || (dataCell.backColor[1] <= 255)) &&
((dataCell.backColor[2] >= 0) || (dataCell.backColor[2] <= 255)))
{
backColor = new XSSFColor(Color.FromArgb(dataCell.backColor[0],
dataCell.backColor[1], dataCell.backColor[2]));
setBorder.FillPattern = FillPattern.SolidForeground;
setBorder.SetFillForegroundColor(backColor);
}
}
}
// check边框
if (dataCell.borderLine != null)
{
if (dataCell.borderLine.Length == 4)
{
setBorder.BorderTop = dataCell.borderLine[0];
setBorder.BorderBottom = dataCell.borderLine[1];
setBorder.BorderLeft = dataCell.borderLine[2];
setBorder.BorderRight = dataCell.borderLine[3];
}
}
// check字体
if (!string.IsNullOrEmpty(dataCell.fontName))
{
font.FontName = dataCell.fontName;
}
else
{
font.FontName = "宋体";
}
// check字体大小
if (dataCell.fontSize != 0)
{
font.FontHeightInPoints = dataCell.fontSize;
}
else
{
font.FontHeightInPoints = (short)10;
}
// 字体是否加粗
if (dataCell.Boldweight)
{
font.IsBold = true;
}
// 是否自动换行
if (dataCell.wrapText)
{
setBorder.WrapText = true;
}
// 单元格的值
if (!string.IsNullOrEmpty(dataCell.cellValue))
{
cell.SetCellValue(dataCell.cellValue);
}
else
{
cell.SetCellValue("");
}
// 水平类型
setBorder.Alignment = dataCell.horizontalAlignment;
// 垂直类型
setBorder.VerticalAlignment = dataCell.verticalAlignment;
// 图片
if (dataCell.excelPictureModel != null)
{
int col2 = 0;
int row2 = 0;
byte[] arr = null;
if (dataCell.excelPictureModel.CodeType == PrintStructFlag.QRCODE)
{//二维码
MemoryStream pictureIS = QRCodeUtil.GetQRCode(dataCell.excelPictureModel.qrCode);
arr = new byte[pictureIS.Length];
pictureIS.Position = 0;
pictureIS.Read(arr, 0, (int)pictureIS.Length);
pictureIS.Close();
col2 = dataCell.excelPictureModel.endColNum + 1;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
else if (dataCell.excelPictureModel.CodeType == PrintStructFlag.BARCODE)
{ //一维码
BarCodeClass bc = new BarCodeClass();
Image image = bc.ZXCreateBarCode(dataCell.excelPictureModel.qrCode);
ImageConverter imgconv = new ImageConverter();
arr = (byte[])imgconv.ConvertTo(image, typeof(byte[]));
col2 = dataCell.excelPictureModel.endColNum + 4;
row2 = dataCell.excelPictureModel.endRowNum + 1;
}
int pIndex = workbook.AddPicture(arr, NPOI.SS.UserModel.PictureType.JPEG);
XSSFDrawing patriarch = (XSSFDrawing)sheet.CreateDrawingPatriarch();
XSSFClientAnchor anchor = new XSSFClientAnchor(0, 0, 0, 0, dataCell.cellNum,
dataCell.rowNum,
col2,
row2);
patriarch.CreatePicture(anchor, pIndex);
}
setBorder.SetFont(font);
cell.CellStyle = setBorder;
//logger.Debug(dataSheet.sheetName + "->Row:" + dataCell.rowNum + "->Cell:"
//+ dataCell.cellNum + "->End");
}
#endregion
}
}
string filePath = System.Threading.Thread.GetDomain().BaseDirectory + "\\";
var new_tempFileName = "to_" + temName + ".xlsx";
FileStream files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.Create);
workbook.Write(files);
files.Close();
files = new FileStream(filePath + @"PrintTemplete\" + new_tempFileName, FileMode.OpenOrCreate);
byte[] bytes = new byte[files.Length];
files.Read(bytes, 0, bytes.Length);
files.Close();
workbook.Close();
return(bytes);
}
private void BtnExport_Click(object sender, EventArgs e)
{
if (lvClients.Items.Count < 1)
{
MessageBox.Show("请先根据条件查询，再进行导出操作", "提示");
return;
}
DialogResult dialogResult = saveFileDialog.ShowDialog();
if (dialogResult == DialogResult.OK)
{
Cursor = Cursors.WaitCursor;
int indexSequence = 0, indexCustomerName = 1, indexMobile = 2, indexLeaveWordsTime = 3,
indexFirstOwnerName = 4, indexTimes = 5, indexFollowerName = 6,
indexFollowTime = 7, indexRemark = 8;
XSSFWorkbook workbook = new XSSFWorkbook();
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
headerStyle.VerticalAlignment = VerticalAlignment.Center;
IFont headerFont = workbook.CreateFont();
//headerFont.IsBold = true;
headerFont.Boldweight = 20;
headerFont.FontHeightInPoints = 12;
headerFont.Color = HSSFColor.Black.Index;
//headerStyle.FillBackgroundColor = HSSFColor.LightYellow.Index;
headerStyle.SetFont(headerFont);
headerStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
headerStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
#region 设置表头
for (int rowNum = 0; rowNum < 2; rowNum++)
{
IRow rowTitle = sheet.CreateRow(rowNum);
ICell cellSequence = rowTitle.CreateCell(indexSequence);
cellSequence.SetCellValue("序号");
cellSequence.CellStyle = headerStyle;
sheet.SetColumnWidth(indexSequence, 5 * 256);
ICell cellCustomerName = rowTitle.CreateCell(indexCustomerName);
cellCustomerName.SetCellValue("客户姓名");
sheet.SetColumnWidth(indexCustomerName, 10 * 256);
cellCustomerName.CellStyle = headerStyle;
ICell cellMobile = rowTitle.CreateCell(indexMobile);
cellMobile.SetCellValue("手机号码");
sheet.SetColumnWidth(indexMobile, 12 * 256);
cellMobile.CellStyle = headerStyle;
ICell cellLeaveWordsTime = rowTitle.CreateCell(indexLeaveWordsTime);
sheet.SetColumnWidth(cellLeaveWordsTime.ColumnIndex, 18 * 256);//列宽
cellLeaveWordsTime.SetCellValue("留言时间");
cellLeaveWordsTime.CellStyle = headerStyle;
ICell cellFirstOwnerName = rowTitle.CreateCell(indexFirstOwnerName);
cellFirstOwnerName.SetCellValue("招商经理");
sheet.SetColumnWidth(indexFirstOwnerName, 10 * 256);
cellFirstOwnerName.CellStyle = headerStyle;
ICell cellTimes = rowTitle.CreateCell(indexTimes);
sheet.SetColumnWidth(indexTimes, 5 * 256);
cellTimes.SetCellValue("次数");
cellTimes.CellStyle = headerStyle;
//sheet.SetColumnWidth(cellRemark.ColumnIndex, 100 * 256);//指定列宽
ICell cellFollowerName = rowTitle.CreateCell(indexFollowerName);
cellFollowerName.SetCellValue("跟进人");
cellFollowerName.CellStyle = headerStyle;
ICell cellFollowTime = rowTitle.CreateCell(indexFollowTime);
cellFollowTime.SetCellValue("跟进时间");
sheet.SetColumnWidth(indexFollowTime, 11 * 256);//列宽
cellFollowTime.CellStyle = headerStyle;
ICell cellRemark = rowTitle.CreateCell(indexRemark);
cellRemark.SetCellValue("沟通记录");
cellRemark.CellStyle = headerStyle;
sheet.SetColumnWidth(cellRemark.ColumnIndex, 100 * 256);
}
sheet.AddMergedRegion(new CellRangeAddress(0, 1, indexSequence, indexSequence));
sheet.AddMergedRegion(new CellRangeAddress(0, 1, indexCustomerName, indexCustomerName));
sheet.AddMergedRegion(new CellRangeAddress(0, 1, indexMobile, indexMobile));
sheet.AddMergedRegion(new CellRangeAddress(0, 1, indexLeaveWordsTime, indexLeaveWordsTime));
sheet.AddMergedRegion(new CellRangeAddress(0, 1, indexFirstOwnerName, indexFirstOwnerName));
sheet.AddMergedRegion(new CellRangeAddress(0, 0, indexTimes, indexRemark));//合并沟通信息
ICell cellFollowInformation = sheet.GetRow(0).GetCell(4);
cellFollowInformation.SetCellValue("沟通信息");
ICellStyle followInfoSytle = workbook.CreateCellStyle();
followInfoSytle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
followInfoSytle.VerticalAlignment = VerticalAlignment.Center;
followInfoSytle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
followInfoSytle.SetFont(headerFont);
//cellFollowInformation.CellStyle = followInfoSytle;
//followInfoSytle.Indention = 4;
ICell cellRemark2 = sheet.GetRow(1).GetCell(indexRemark);
cellRemark2.CellStyle = followInfoSytle;
#endregion
int startRowIndex = 2;
ICellStyle styleCenter = workbook.CreateCellStyle();
styleCenter.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
styleCenter.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleCenter.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleCenter.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleCenter.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleCenter.VerticalAlignment = VerticalAlignment.Center;
ICellStyle styleDefault = workbook.CreateCellStyle();
styleDefault.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
styleDefault.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleDefault.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleDefault.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleDefault.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleDefault.VerticalAlignment = VerticalAlignment.Center;
ICellStyle styleTopLeft = workbook.CreateCellStyle();
styleTopLeft.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
styleTopLeft.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
styleTopLeft.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
styleTopLeft.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
styleTopLeft.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
styleTopLeft.VerticalAlignment = VerticalAlignment.Top;
styleTopLeft.WrapText = true;
for (int i = 0; i < customers.getResults().Count; i++)
{
CustomerDto customer = customers.getResults()[i];
IList <CustomerFollowRecord> records = CustomerService.GetFollowerRecordsByCusotmerId(customer.id, Global.USER_TOKEN);
if (records == null || records.Count < 1)
{
IRow row = sheet.CreateRow(startRowIndex);
ICell cellSeq = row.CreateCell(indexSequence);
cellSeq.SetCellValue(i + 1);
cellSeq.CellStyle = styleCenter;
ICell cellCustomer = row.CreateCell(indexCustomerName);
cellCustomer.SetCellValue(customer.name);
cellCustomer.CellStyle = styleDefault;
ICell cellMobile = row.CreateCell(indexMobile);
cellMobile.SetCellValue(customer.mobile);
cellMobile.CellStyle = styleDefault;
ICell cellLeaveWordsTime = row.CreateCell(indexLeaveWordsTime);
cellLeaveWordsTime.SetCellValue(customer.leaveWordsTime.HasValue ? customer.leaveWordsTime.Value.ToString(GlobalConfig.DateTimeFormat) : "");
cellLeaveWordsTime.CellStyle = styleCenter;
ICell cellFirstOwnerName = row.CreateCell(indexFirstOwnerName);
cellFirstOwnerName.CellStyle = styleDefault;
cellFirstOwnerName.SetCellValue(customer.firstOwnerName);
ICell cellTimes = row.CreateCell(indexTimes);
cellTimes.CellStyle = styleCenter;
cellTimes.SetCellValue("");
ICell cellFollowerName = row.CreateCell(indexFollowerName);
cellFollowerName.SetCellValue("");
cellFollowerName.CellStyle = styleDefault;
ICell cellFollowTime = row.CreateCell(indexFollowTime);
cellFollowTime.SetCellValue("");
cellFollowTime.CellStyle = styleCenter;
ICell cellRemark = row.CreateCell(indexRemark);
cellRemark.SetCellValue("");
cellRemark.CellStyle = styleTopLeft;
startRowIndex++;
}
else
{
//int mergeStart = startRowIndex;
for (int j = records.Count; j >= 1; j--)
{
IRow row = sheet.CreateRow(startRowIndex);
ICell cellSeq = row.CreateCell(indexSequence);
cellSeq.SetCellValue(i + 1);
cellSeq.CellStyle = styleCenter;
ICell cellCustomer = row.CreateCell(indexCustomerName);
cellCustomer.SetCellValue(customer.name);
cellCustomer.CellStyle = styleDefault;
ICell cellMobile = row.CreateCell(indexMobile);
cellMobile.SetCellValue(customer.mobile);
cellMobile.CellStyle = styleDefault;
ICell cellLeaveWordsTime = row.CreateCell(indexLeaveWordsTime);
cellLeaveWordsTime.SetCellValue(customer.leaveWordsTime.HasValue ? customer.leaveWordsTime.Value.ToString(GlobalConfig.DateTimeFormat) : "");
cellLeaveWordsTime.CellStyle = styleCenter;
ICell cellFirstOwnerName = row.CreateCell(indexFirstOwnerName);
cellFirstOwnerName.CellStyle = styleDefault;
cellFirstOwnerName.SetCellValue(customer.firstOwnerName);
ICell cellTimes = row.CreateCell(indexTimes);
cellTimes.SetCellValue(records.Count - j + 1);
cellTimes.CellStyle = styleCenter;
ICell cellFollowName = row.CreateCell(indexFollowerName);
cellFollowName.SetCellValue(records[j - 1].followUserName);
cellFollowName.CellStyle = styleDefault;
ICell cellFollowTime = row.CreateCell(indexFollowTime);
cellFollowTime.SetCellValue(records[j - 1].communicationTime.ToString("MM-dd HH:mm"));
cellFollowTime.CellStyle = styleCenter;
ICell remarkCell = row.CreateCell(indexRemark);
remarkCell.CellStyle = styleTopLeft;
remarkCell.SetCellValue(records[j - 1].remark);
startRowIndex++;
}
if (records.Count > 1)
{
//合并
int mergeStartRowIndex = startRowIndex - records.Count;
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRowIndex, startRowIndex - 1, 0, 0));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRowIndex, startRowIndex - 1, indexCustomerName, indexCustomerName));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRowIndex, startRowIndex - 1, indexMobile, indexMobile));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRowIndex, startRowIndex - 1, indexLeaveWordsTime, indexLeaveWordsTime));
sheet.AddMergedRegion(new CellRangeAddress(mergeStartRowIndex, startRowIndex - 1, indexFirstOwnerName, indexFirstOwnerName));
}
}
}
try
{
using (FileStream fs = new FileStream(saveFileDialog.FileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
workbook.Close();
Cursor = Cursors.Default;
if (MessageBox.Show("导出成功！是否现在打开文件？", "提示", MessageBoxButtons.YesNo, MessageBoxIcon.Question, MessageBoxDefaultButton.Button1)
== DialogResult.Yes)
{
Process.Start(saveFileDialog.FileName);
}
}
}
catch (Exception ex)
{
Cursor = Cursors.Default;
MessageBox.Show("导出失败:" + ex.Message, "提示");
}
finally
{
Cursor = Cursors.Default;
workbook.Close();
}
}
}
/// <summary>
/// export the data
/// </summary>
/// <param name="lst"></param>
/// <param name="fileName"></param>
/// <param name="lFolder"></param>
/// <param name="rFolder"></param>
/// <returns></returns>
public static bool Export(List <Level> lst, string fileName, string lFolder, string rFolder)
{
bool isSuccess = false;
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workBook.CreateSheet();
try
{
//add header style
ICellStyle cellStyle = workBook.CreateCellStyle();
cellStyle.Alignment = HorizontalAlignment.Left;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
IFont font = workBook.CreateFont();
font.Boldweight = (short)FontBoldWeight.Bold;
font.FontHeightInPoints = 12;
font.FontName = "Arial Unicode MS";
cellStyle.SetFont(font);
#region header settings
IRow first = sheet.CreateRow(0);
ICell fst_cell = first.CreateCell(0);
fst_cell.SetCellValue("Quarterly Source Code Comparison and Retrofit ");
fst_cell.CellStyle = cellStyle;
IRow second = sheet.CreateRow(1);
IRow third = sheet.CreateRow(2);
third.CreateCell(0).SetCellValue("Date:");
string[,] arr = new string[2, 4] {
{ "GIT Version:", "", "Source Path(L):", lFolder }, { "Production Version:", "", "Source Path(R):", rFolder }
};
for (int i = 0; i < 2; i++)
{
IRow forth_fifth = sheet.CreateRow(i + 3);
for (int j = 0; j < 4; j++)
{
forth_fifth.CreateCell(j).SetCellValue(arr[i, j]);
}
}
IRow sixth = sheet.CreateRow(5);
IRow header = sheet.CreateRow(6);
header.HeightInPoints = 12;
List <string> headers = new List <string> {
"Production Version", "GIT Version", ""
};
ICellStyle cs = workBook.CreateCellStyle();
cs.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
cs.FillPattern = FillPattern.SolidForeground;
cs.Alignment = HorizontalAlignment.Center;
cs.VerticalAlignment = VerticalAlignment.Center;
cs.SetFont(font);
cs.BorderTop = BorderStyle.Thin;
cs.BorderRight = BorderStyle.Thin;
cs.BorderBottom = BorderStyle.Thin;
cs.BorderLeft = BorderStyle.Thin;
for (int m = 0; m < headers.Count; m++)
{
CellRangeAddress region = new CellRangeAddress(6, 6, m * 3, m * 3 + 2);
sheet.AddMergedRegion(region);
for (int n = 0; n < 3; n++)
{
ICell cell = header.CreateCell(3 * m + n);
cell.SetCellValue(headers[m]);
cell.CellStyle = cs;
}
}
IRow title = sheet.CreateRow(7);
title.HeightInPoints = 12;
ICellStyle titleStyle = workBook.CreateCellStyle();
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
titleStyle.Alignment = HorizontalAlignment.Left;
titleStyle.VerticalAlignment = VerticalAlignment.Center;
titleStyle.SetFont(font);
titleStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
titleStyle.FillPattern = FillPattern.SolidForeground;
List <string> titles = new List <string> {
"Path", "Filename", "Size(Byte)", "Path", "FileName", "Size(Byte)", "Comparision Result", "Check-in GIT", "Need Check", "Remark"
};
for (int j = 0; j < titles.Count; j++)
{
ICell cell = title.CreateCell(j);
cell.SetCellValue(titles[j]);
cell.CellStyle = titleStyle;
}
#endregion
sheet.SetAutoFilter(new CellRangeAddress(8, 8, 0, titles.Count - 2)); //line filter
sheet.CreateFreezePane(0, 8); //line freeze
#region body
for (int i = 0; i < lst.Count; i++)
{
sheet.AutoSizeColumn(i);
IRow body = sheet.CreateRow(i + 8);
string lpath = string.Empty;
string lfile = "NA";
string lsize = "NA";
string rpath = string.Empty;
string rfile = "NA";
string rsize = "NA";
string result = "NA";
if (lst[i].LeftNode != null)
{
lpath = lst[i].LeftNode.Path;
lfile = lst[i].LeftNode.FileName;
result = lst[i].LeftNode.Result;
lsize = lst[i].LeftNode.Size;
}
else
{
lpath = lst[i].LtPath;
}
if (lst[i].RightNode != null)
{
rpath = lst[i].RightNode.Path;
rfile = lst[i].RightNode.FileName;
result = lst[i].RightNode.Result;
rsize = lst[i].RightNode.Size;
}
else
{
rpath = lst[i].RtPath;
}
switch (result)
{
case "ltonly":
result = $"Only exists in {lst[i].LeftNode.Path}";
break;
case "rtonly":
result = $"Only exists in {lst[i].RightNode.Path}";
break;
case "same":
if (lsize.Equals(rsize))
{
result = "same";
}
else
{
result = "same text but different size";
}
break;
default:
result = "Text files are different";
break;
}
body.CreateCell(0).SetCellValue(lpath);
body.CreateCell(1).SetCellValue(lfile);
body.CreateCell(2).SetCellValue(lsize);
body.CreateCell(3).SetCellValue(rpath);
body.CreateCell(4).SetCellValue(rfile);
body.CreateCell(5).SetCellValue(rsize);
body.CreateCell(6).SetCellValue(result);
}
SetColumnWidth(sheet, titles.Count);
#endregion
using (FileStream fs = new FileStream(fileName, FileMode.Create, FileAccess.Write))
{
workBook.Write(fs);
workBook.Close();
}
isSuccess = true;
}
catch (Exception ex)
{
ex.Message.Logger();
workBook.Close();
}
return(isSuccess);
}
public int AddMergedRegion(CellRangeAddress region)
{
return(_sh.AddMergedRegion(region));
}
/// <summary>
/// 分頁Row拷貝
/// </summary>
/// <param name="worksheet"></param>
/// <param name="worksheet2"></param>
/// <param name="sourceRowNum"></param>
/// <param name="destinationRowNum"></param>
/// <param name="IsCoverRow"></param>
/// <param name="IsRemoveSrcRow"></param>
/// <param name="copyRowHeight"></param>
/// <param name="resetOriginalRowHeight"></param>
private void CopySheetRow(ref XSSFSheet worksheet, ref XSSFSheet worksheet2, int sourceRowNum, int destinationRowNum, bool IsCoverRow = false, bool IsRemoveSrcRow = false, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
{
XSSFRow newRow = worksheet2.GetRow(destinationRowNum) as XSSFRow;
XSSFRow sourceRow = worksheet.GetRow(sourceRowNum) as XSSFRow;
XSSFCell oldCell, newCell;
int i;
if (newRow == null)
{
newRow = worksheet2.CreateRow(destinationRowNum) as XSSFRow;
}
// Loop through source columns to add to new row
for (i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
oldCell = sourceRow.GetCell(i) as XSSFCell;
newCell = newRow.GetCell(i) as XSSFCell;
if (newCell == null)
{
newCell = newRow.CreateCell(i) as XSSFCell;
}
// If the old cell is null jump to next cell
if (oldCell == null)
{
newCell = null;
continue;
}
// Copy style from old cell and apply to new cell
newCell.CellStyle = oldCell.CellStyle;
// If there is a cell comment, copy
if (newCell.CellComment != null)
{
newCell.CellComment = oldCell.CellComment;
}
// If there is a cell hyperlink, copy
if (oldCell.Hyperlink != null)
{
newCell.Hyperlink = oldCell.Hyperlink;
}
// Set the cell data value
switch (oldCell.CellType)
{
case CellType.Blank:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.CellFormula = oldCell.CellFormula;
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.String:
newCell.SetCellValue(oldCell.RichStringCellValue);
break;
case CellType.Unknown:
newCell.SetCellValue(oldCell.StringCellValue);
break;
}
}
// If there are are any merged regions in the source row, copy to new row
CellRangeAddress cellRangeAddress = null, newCellRangeAddress = null;
for (i = 0; i < worksheet.NumMergedRegions; i++)
{
cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.LastRow -
cellRangeAddress.FirstRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet2.AddMergedRegion(newCellRangeAddress);
}
}
//複製行高到新列
if (copyRowHeight)
{
newRow.Height = sourceRow.Height;
}
//重製原始列行高
if (resetOriginalRowHeight)
{
sourceRow.Height = worksheet.DefaultRowHeight;
}
//清掉原列
if (IsRemoveSrcRow == true)
{
worksheet.RemoveRow(sourceRow);
}
}
IActionResult Export(List <PortfoViewModel> model)
{
var filename = Guid.NewGuid().ToString() + ".xlsx";
using (FileStream stream = new FileStream(hosting.WebRootPath + filename, FileMode.Create, FileAccess.Write))
{
IWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet("Sheet1");
sheet.RightToLeft = true;
IRow header_row = sheet.CreateRow(0);
header_row.CreateCell(0).SetCellValue("ردیف");
header_row.CreateCell(1).SetCellValue("سهامدار");
header_row.CreateCell(2).SetCellValue("نام شرکت");
header_row.CreateCell(3).SetCellValue("تعداد سهام");
header_row.CreateCell(4).SetCellValue("درصد سهام");
header_row.CreateCell(5).SetCellValue("درصد تجمعی گروه");
header_row.CreateCell(6).SetCellValue("ارتباط با هلدینگ");
header_row.CreateCell(7).SetCellValue("ارتباط با هلدینگ");
header_row.CreateCell(8).SetCellValue("نوع سرمایهگذاری");
header_row.CreateCell(9).SetCellValue("سرمایه ثبت شده(میلیون ریال)");
header_row.CreateCell(10).SetCellValue("تعداد نماینده");
header_row.CreateCell(11).SetCellValue("سایر سهامداران اصلی");
header_row.CreateCell(12).SetCellValue("درصد مالکیت");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 6, 7));
header_row = sheet.CreateRow(1);
header_row.CreateCell(0).SetCellValue("ردیف");
header_row.CreateCell(1).SetCellValue("سهامدار");
header_row.CreateCell(2).SetCellValue("نام شرکت");
header_row.CreateCell(3).SetCellValue("تعداد سهام");
header_row.CreateCell(4).SetCellValue("درصد سهام");
header_row.CreateCell(5).SetCellValue("درصد تجمعی گروه");
header_row.CreateCell(6).SetCellValue("سهامدار");
header_row.CreateCell(7).SetCellValue(" تحت مدیریت");
header_row.CreateCell(8).SetCellValue("نوع سرمایهگذاری");
header_row.CreateCell(9).SetCellValue("سرمایه ثبت شده(میلیون ریال)");
header_row.CreateCell(10).SetCellValue("تعداد نماینده");
header_row.CreateCell(11).SetCellValue("سایر سهامداران اصلی");
header_row.CreateCell(12).SetCellValue("درصد مالکیت");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 5, 5));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 8, 8));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 9, 9));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 10, 10));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 11, 11));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 1, 12, 12));
int k = 2;
int currGrp = 0;
int prevGrp = 0;
int keepRow = 0;
for (int i = 0; i < model.Count; i++)
{
var modelRow = model.ElementAt(i);
string companyName = modelRow.CompanyTitle;
var res = modelRow.PortfoShareholderViewModels.Where(woak => !woak.IsDeleted && woak.show).OrderByDescending(woak => woak.IsMain);
currGrp = res.Where(woak => !woak.IsDeleted && !woak.IsMain && woak.show).Count();
if (res == null || res.Count() == 0)
{
continue;
}
//int shareHolderId = res.Where(e => e.IsMain).FirstOrDefault().ShareholderId;
if (i > 0)
{
int res_int = model.ElementAt(i - 1).PortfoShareholderViewModels.Where(woak => !woak.IsDeleted && !woak.IsMain && woak.show).Count();
if (res_int > 0)
{
if (res_int == 0)
{
++res_int;
}
prevGrp += res_int;
}
else
{
prevGrp++;
}
}
string subshareholders = string.Empty;
string values = string.Empty;
int rowNum = 0;
keepRow++;
foreach (var item in res)
{
IRow row = sheet.CreateRow(k);
if (!item.IsMain)
{
k++;
rowNum++;
}
DataFormatter da = new DataFormatter();
ICell ic; ICellStyle st;
string titleMain = res.Where(e => e.IsMain == true).FirstOrDefault() == null ? "-" : res.Where(e => e.IsMain == true).FirstOrDefault().Title;
row.CreateCell(0).SetCellValue(keepRow);
//if (shareholder != null)
row.CreateCell(1).SetCellValue(titleMain);
row.CreateCell(2).SetCellValue(modelRow.CompanyTitle == string.Empty ? "-" : modelRow.CompanyTitle);
var mainObject = res.Where(e => e.IsMain == true).FirstOrDefault();
double mainShare = res.Where(e => e.IsMain == true).FirstOrDefault() == null ? 0 : res.Where(e => e.IsMain == true).FirstOrDefault().Value;
if (mainObject == null)
{
if (!string.IsNullOrEmpty(item.Title))
{
row.CreateCell(11).SetCellValue(item.Title);
}
row.CreateCell(12).SetCellValue(item.Value);
continue;
}
row.CreateCell(3).SetCellValue(String.Format("{0:n0}", modelRow.Shares));
row.CreateCell(4).SetCellValue(mainShare);
//aggregate percentage
row.CreateCell(5).SetCellValue(String.Format("{0:.##}", item.Column * 100));//item.Column
//Communication
row.CreateCell(6).SetCellValue(modelRow.ShareholderHoldingCommunication ? "*" : "");
row.CreateCell(7).SetCellValue(modelRow.UnderManagementHoldingCommunication ? "*" : "");
//Invest type
row.CreateCell(8).SetCellValue(modelRow.CompanyTypeTitle);
//CurrentCapital
row.CreateCell(9).SetCellValue(String.Format("{0:n0}", modelRow.CurrentCapital / 1000000d));
//AgentsNumber
row.CreateCell(10).SetCellValue(modelRow.AgentsNumber);
if (item.IsMain)
{
row.CreateCell(11).SetCellValue("-");
row.CreateCell(12).SetCellValue("-");
}
else
{
row.CreateCell(11).SetCellValue(item.Title);
row.CreateCell(12).SetCellValue(item.Value);
}
}
for (int j = 0; j < 11; j++)
{
int fr = 0;
int lr = 0;
if (i == 0)
{
if (rowNum == 0)
{
rowNum++;
k++;
}
if (currGrp == 0)
{
currGrp += 1;
}
fr = 2;
lr = currGrp + 1;
}
else
{
if (rowNum == 0)
{
rowNum++;
k++;
}
if (currGrp == 0)
{
currGrp++;
}
fr = prevGrp + 2;
lr = fr + currGrp - 1;
}
if (lr < fr)
{
break;
}
var cra = new NPOI.SS.Util.CellRangeAddress(fr, lr, j, j);
sheet.AddMergedRegion(cra);
}
}
wb.Write(stream);
var bytes = System.IO.File.ReadAllBytes(hosting.WebRootPath + filename);
System.IO.File.Delete(hosting.WebRootPath + filename);
var fileResult = File(bytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", $"portfo.xlsx");
return(fileResult);
}
}
public ActionResult exampleData(string newid)
{
string filename = "", clientip = Request.HttpContext.Connection.RemoteIpAddress.ToString().TrimEnd() == "::1" ? "127.0.0.1" : Request.HttpContext.Connection.RemoteIpAddress.ToString().TrimEnd();
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Information");
XSSFRow row = (XSSFRow)sheet.CreateRow(0);
database database = new database();
DataTable mainRows = new DataTable();
List <dbparam> dbparamlist = new List <dbparam>();
dbparamlist.Add(new dbparam("@newid", newid));
mainRows = database.checkSelectSql("mssql", "epaperstring", "exec web.checksitename @newid;", dbparamlist);
switch (mainRows.Rows.Count)
{
case 0:
row.CreateCell(0).SetCellValue("NOT YOUR INFORMATION ABOUT THIS FORM DATABASE");
filename = "沒資料呈現";
break;
default:
datetime datetime = new datetime();
string stdate = datetime.sqldate("mssql", "epaperstring"), sttime = datetime.sqltime("mssql", "epaperstring");
XSSFCellStyle xs = (XSSFCellStyle)workbook.CreateCellStyle();
xs.Alignment = HorizontalAlignment.Center;
row.CreateCell(0).SetCellValue("表單上傳範例");
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 19));
row.GetCell(0).CellStyle = xs;
row = (XSSFRow)sheet.CreateRow(1);
row.CreateCell(0).SetCellValue("表單代碼");
row.CreateCell(1).SetCellValue("57ae8a28-5b48-4e61-97af-e50431a9c65b");
row.CreateCell(2).SetCellValue("表單題目");
row.CreateCell(3).SetCellValue("測試表單");
row.CreateCell(4).SetCellValue("表單備註");
row.CreateCell(5).SetCellValue("測試備註");
row = (XSSFRow)sheet.CreateRow(2);
row.CreateCell(0).SetCellValue("開始日期");
row.CreateCell(1).SetCellValue("2020/09/08");
row.CreateCell(2).SetCellValue("開始時間");
row.CreateCell(3).SetCellValue("11:17");
row.CreateCell(4).SetCellValue("截止日期");
row.CreateCell(5).SetCellValue("2020/09/08");
row.CreateCell(6).SetCellValue("截止時間");
row.CreateCell(7).SetCellValue("12:00");
row.CreateCell(8).SetCellValue("是否考試？（0:否 ,1:是）");
row.CreateCell(9).SetCellValue("1");
row.CreateCell(10).SetCellValue("重新開始？（0:否 ,1:是）");
row.CreateCell(11).SetCellValue("1");
row.CreateCell(12).SetCellValue("限制人員？（0:否 ,1:是）");
row.CreateCell(13).SetCellValue("1");
row.CreateCell(14).SetCellValue("選項隨機？（0:否 ,1:是）");
row.CreateCell(15).SetCellValue("1");
row.CreateCell(16).SetCellValue("題目隨機？（0:否 ,1:是）");
row.CreateCell(17).SetCellValue("1");
row.CreateCell(18).SetCellValue("顯示題數（若要全數，請輸入0或空白）");
row.CreateCell(19).SetCellValue("0");
row = (XSSFRow)sheet.CreateRow(4);
row.CreateCell(0).SetCellValue("題目編號");
row.CreateCell(1).SetCellValue("選項編號");
row.CreateCell(2).SetCellValue("題目 / 選項內容");
row.CreateCell(3).SetCellValue("類型（radio:單選 / checkbox:複選 / text:短句 / textarea:長句 / image:圖片）");
row.CreateCell(4).SetCellValue("是否需要檢查（0:不需要 / 1:需要）");
row.CreateCell(5).SetCellValue("是否為答案（0:否 / 1:是）");
row = (XSSFRow)sheet.CreateRow(5);
row.CreateCell(0).SetCellValue("1");
row.CreateCell(2).SetCellValue("測試第一題");
row.CreateCell(3).SetCellValue("radio");
row.CreateCell(4).SetCellValue("1");
row = (XSSFRow)sheet.CreateRow(6);
row.CreateCell(1).SetCellValue("1");
row.CreateCell(2).SetCellValue("是");
row.CreateCell(5).SetCellValue("1");
dbparamlist.Clear();
string date = datetime.sqldate("mssql", "epaperstring"), time = datetime.sqltime("mssql", "epaperstring"), cuname = Dns.GetHostEntry(clientip).HostName.IndexOf('.') == -1 ? Dns.GetHostEntry(clientip).HostName : Dns.GetHostEntry(clientip).HostName.Split('.')[0];
dbparamlist.Add(new dbparam("@externip", clientip));
dbparamlist.Add(new dbparam("@hostname", cuname));
dbparamlist.Add(new dbparam("@stdate", stdate));
dbparamlist.Add(new dbparam("@sttime", sttime));
dbparamlist.Add(new dbparam("@endate", date));
dbparamlist.Add(new dbparam("@entime", time));
dbparamlist.Add(new dbparam("@indate", date));
dbparamlist.Add(new dbparam("@intime", time));
dbparamlist.Add(new dbparam("@inoper", newid));
database.checkActiveSql("mssql", "flyformstring", "insert into web.examplelog (externip,hostname,stdate,sttime,endate,entime,indate,intime,inoper) values (@externip,@hostname,@stdate,@sttime,@endate,@entime,@indate,@intime,@inoper);", dbparamlist);
filename = $"{mainRows.Rows[0]["username"].ToString().TrimEnd()}匯入表單範本";
break;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
byte[] bytes = ms.ToArray();
return(File(bytes, "application/vnd.ms-excel", $"FLYTECH{filename}.xlsx"));
}
/// <summary>
/// 生成任务的Excel报表
/// </summary>
/// <param name="units"></param>
private void TaskBuildExcel(List <DeviceListOutput> devices)
{
/////文件生成存放路径
string filePath = this._appFolders.ExcelFolder + "/导出报表/设备列表.xlsx";
//内存中创建一个hssfworkbook对象流（创建一个Excel文件）
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
//创建sheet
XSSFSheet Sheet1 = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
#region
//第一行
XSSFRow fcell = (XSSFRow)Sheet1.CreateRow(0);
fcell.CreateCell(0).SetCellValue("设备明细表");
//合并单元格
Sheet1.AddMergedRegion(new CellRangeAddress(0, 0, 0, 23));
#endregion
XSSFCellStyle fCellStyle = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont ffont = (XSSFFont)xssfworkbook.CreateFont();
ffont.FontHeightInPoints = 16;
ffont.FontName = "宋体";
ffont.Boldweight = (short)FontBoldWeight.Bold;//加粗;
fCellStyle.SetFont(ffont);
fCellStyle.Alignment = HorizontalAlignment.Left;
fCellStyle.VerticalAlignment = VerticalAlignment.Center;
fCellStyle.BorderBottom = BorderStyle.Thin;
fCellStyle.BorderLeft = BorderStyle.Thin;
fCellStyle.BorderRight = BorderStyle.Thin;
fCellStyle.BorderTop = BorderStyle.Thin;
Sheet1.GetRow(0).Height = 30 * 20;
#region 标题风格
fcell.GetCell(0).CellStyle = fCellStyle;
fcell.CreateCell(1).CellStyle = fCellStyle;
fcell.CreateCell(2).CellStyle = fCellStyle;
fcell.CreateCell(3).CellStyle = fCellStyle;
fcell.CreateCell(4).CellStyle = fCellStyle;
fcell.CreateCell(5).CellStyle = fCellStyle;
fcell.CreateCell(6).CellStyle = fCellStyle;
fcell.CreateCell(7).CellStyle = fCellStyle;
fcell.CreateCell(8).CellStyle = fCellStyle;
fcell.CreateCell(9).CellStyle = fCellStyle;
fcell.CreateCell(10).CellStyle = fCellStyle;
fcell.CreateCell(11).CellStyle = fCellStyle;
fcell.CreateCell(12).CellStyle = fCellStyle;
fcell.CreateCell(13).CellStyle = fCellStyle;
fcell.CreateCell(14).CellStyle = fCellStyle;
fcell.CreateCell(15).CellStyle = fCellStyle;
fcell.CreateCell(16).CellStyle = fCellStyle;
fcell.CreateCell(17).CellStyle = fCellStyle;
fcell.CreateCell(18).CellStyle = fCellStyle;
fcell.CreateCell(19).CellStyle = fCellStyle;
fcell.CreateCell(20).CellStyle = fCellStyle;
fcell.CreateCell(21).CellStyle = fCellStyle;
fcell.CreateCell(22).CellStyle = fCellStyle;
fcell.CreateCell(23).CellStyle = fCellStyle;
#endregion 标题风格
//设置Sheet1的每一列的宽度
#region 每一列的宽度
Sheet1.SetColumnWidth(0, 7 * 256);
Sheet1.SetColumnWidth(1, 11 * 256);
Sheet1.SetColumnWidth(2, 23 * 256);
Sheet1.SetColumnWidth(3, 11 * 256);
Sheet1.SetColumnWidth(4, 9 * 256);
Sheet1.SetColumnWidth(5, 9 * 256);
Sheet1.SetColumnWidth(6, 9 * 256);
Sheet1.SetColumnWidth(7, 9 * 256);
Sheet1.SetColumnWidth(8, 9 * 256);
Sheet1.SetColumnWidth(9, 9 * 256);
Sheet1.SetColumnWidth(10, 18 * 256);
Sheet1.SetColumnWidth(11, 9 * 256);
Sheet1.SetColumnWidth(12, 9 * 256);
Sheet1.SetColumnWidth(13, 9 * 256);
Sheet1.SetColumnWidth(14, 9 * 256);
Sheet1.SetColumnWidth(15, 9 * 256);
Sheet1.SetColumnWidth(16, 9 * 256);
Sheet1.SetColumnWidth(17, 9 * 256);
Sheet1.SetColumnWidth(18, 9 * 256);
Sheet1.SetColumnWidth(19, 9 * 256);
Sheet1.SetColumnWidth(20, 9 * 256);
Sheet1.SetColumnWidth(21, 9 * 256);
Sheet1.SetColumnWidth(22, 9 * 256);
Sheet1.SetColumnWidth(23, 18 * 256);
#endregion 每一列的宽度
//创建第二行
XSSFRow rowob2 = (XSSFRow)Sheet1.CreateRow(1);
#region 字段名
rowob2.CreateCell(0).SetCellValue("序号");
rowob2.CreateCell(1).SetCellValue("设备编号");
rowob2.CreateCell(2).SetCellValue("设备描述");
rowob2.CreateCell(3).SetCellValue("使用状态");
rowob2.CreateCell(4).SetCellValue("泊位状态(综合)");
rowob2.CreateCell(5).SetCellValue("心跳周期");
rowob2.CreateCell(6).SetCellValue("检查周期");
rowob2.CreateCell(7).SetCellValue("温度值");
rowob2.CreateCell(8).SetCellValue("电压");
rowob2.CreateCell(9).SetCellValue("信号强度");
rowob2.CreateCell(10).SetCellValue("更新时间");
rowob2.CreateCell(11).SetCellValue("地磁检测状态");
rowob2.CreateCell(12).SetCellValue("雷达检测状态");
rowob2.CreateCell(13).SetCellValue("射频故障代码");
rowob2.CreateCell(14).SetCellValue("磁地和雷达故障代码");
rowob2.CreateCell(15).SetCellValue("存储器故障代码");
rowob2.CreateCell(16).SetCellValue("电池故障代码");
rowob2.CreateCell(17).SetCellValue("设备健康状态");
rowob2.CreateCell(18).SetCellValue("磁场三轴采样");
rowob2.CreateCell(19).SetCellValue("雷达三轴采样");
rowob2.CreateCell(20).SetCellValue("雷达检测距离值");
rowob2.CreateCell(21).SetCellValue("设备软件版本");
rowob2.CreateCell(22).SetCellValue("设备硬件版本");
rowob2.CreateCell(23).SetCellValue("添加时间");
#endregion 字段名
//设置第二行的样式
XSSFCellStyle Style = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont ffont1 = (XSSFFont)xssfworkbook.CreateFont();
ffont1.FontHeightInPoints = 10;
ffont1.Boldweight = (short)FontBoldWeight.Bold;//加粗;
ffont1.FontName = "宋体";
Style.Alignment = HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.WrapText = true;
Style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
Style.FillPattern = FillPattern.SolidForeground;
Style.BorderBottom = BorderStyle.Thin;
Style.BorderLeft = BorderStyle.Thin;
Style.BorderRight = BorderStyle.Thin;
Style.BorderTop = BorderStyle.Thin;
Style.SetFont(ffont1);
#region 字段名风格
rowob2.GetCell(0).CellStyle = Style;
rowob2.GetCell(1).CellStyle = Style;
rowob2.GetCell(2).CellStyle = Style;
rowob2.GetCell(3).CellStyle = Style;
rowob2.GetCell(4).CellStyle = Style;
rowob2.GetCell(5).CellStyle = Style;
rowob2.GetCell(6).CellStyle = Style;
rowob2.GetCell(7).CellStyle = Style;
rowob2.GetCell(8).CellStyle = Style;
rowob2.GetCell(9).CellStyle = Style;
rowob2.GetCell(10).CellStyle = Style;
rowob2.GetCell(11).CellStyle = Style;
rowob2.GetCell(12).CellStyle = Style;
rowob2.GetCell(13).CellStyle = Style;
rowob2.GetCell(14).CellStyle = Style;
rowob2.GetCell(15).CellStyle = Style;
rowob2.GetCell(16).CellStyle = Style;
rowob2.GetCell(17).CellStyle = Style;
rowob2.GetCell(18).CellStyle = Style;
rowob2.GetCell(19).CellStyle = Style;
rowob2.GetCell(20).CellStyle = Style;
rowob2.GetCell(21).CellStyle = Style;
rowob2.GetCell(22).CellStyle = Style;
rowob2.GetCell(23).CellStyle = Style;
#endregion 字段名风格
//设置表格内容显示样式1
#region
XSSFCellStyle ContentStyle = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont contentFfont = (XSSFFont)xssfworkbook.CreateFont();
contentFfont.FontHeightInPoints = 9;
contentFfont.FontName = "宋体";
ContentStyle.Alignment = HorizontalAlignment.Left;
ContentStyle.VerticalAlignment = VerticalAlignment.Top;
ContentStyle.WrapText = true;
ContentStyle.BorderBottom = BorderStyle.Thin;
ContentStyle.BorderLeft = BorderStyle.Thin;
ContentStyle.BorderRight = BorderStyle.Thin;
ContentStyle.BorderTop = BorderStyle.Thin;
ContentStyle.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("text");
ContentStyle.SetFont(contentFfont);
#endregion
//设置表格内容显示样式2
#region
XSSFCellStyle ContentStyle2 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();
XSSFFont contentFfont2 = (XSSFFont)xssfworkbook.CreateFont();
contentFfont2.FontHeightInPoints = 9;
contentFfont2.FontName = "宋体";
ContentStyle2.Alignment = HorizontalAlignment.Center;
ContentStyle2.VerticalAlignment = VerticalAlignment.Center;
ContentStyle2.WrapText = true;
ContentStyle2.BorderBottom = BorderStyle.Thin;
ContentStyle2.BorderLeft = BorderStyle.Thin;
ContentStyle2.BorderRight = BorderStyle.Thin;
ContentStyle2.BorderTop = BorderStyle.Thin;
ContentStyle2.DataFormat = NPOI.HSSF.UserModel.HSSFDataFormat.GetBuiltinFormat("text");
ContentStyle2.SetFont(contentFfont2);
#endregion
int index = 1;
foreach (var device in devices)
{
XSSFRow row = (XSSFRow)Sheet1.CreateRow(index + 1);
//序号
row.CreateCell(0).SetCellValue(index);
#region 写值
row.CreateCell(1).SetCellValue(device.DeviceNo); //设备编号
row.CreateCell(2).SetCellValue(device.DeviceDescribe); //设备描述
switch (device.DeviceStatus) //使用状态
{
case 0:
row.CreateCell(3).SetCellValue("新增(未连接)");
break;
case 1:
row.CreateCell(3).SetCellValue("在线");
break;
case 2:
row.CreateCell(3).SetCellValue("离线");
break;
default:
row.CreateCell(3).SetCellValue("");
break;
}
switch (device.SynStatus)
{
case 0:
row.CreateCell(4).SetCellValue("无车");
break;
case 1:
row.CreateCell(4).SetCellValue("有车");
break;
case 2:
row.CreateCell(4).SetCellValue("等待激活");
break;
case 3:
row.CreateCell(4).SetCellValue("初始化中");
break;
default:
row.CreateCell(4).SetCellValue("");
break;
} //综合状态
//心跳周期
if (device.HbeatT != null)
{
row.CreateCell(5).SetCellValue(device.HbeatT + "分钟");
}
else
{
row.CreateCell(5).SetCellValue("");
}
//故障检查周期
if (device.healthT != null)
{
row.CreateCell(6).SetCellValue(device.healthT + "分钟");
}
else
{
row.CreateCell(6).SetCellValue("");
}
//温度值
if (device.Temperature != null)
{
row.CreateCell(7).SetCellValue(device.Temperature + "℃");
}
else
{
row.CreateCell(7).SetCellValue("");
}
//电压
if (device.BatVoltage != null)
{
row.CreateCell(8).SetCellValue(device.BatVoltage + "V");
}
else
{
row.CreateCell(8).SetCellValue("");
}
row.CreateCell(9).SetCellValue(device.NbSignal); //NB 信号强度
if (device.StatusUpdateTime == null) //设备更新时间
{
row.CreateCell(10).SetCellValue("");
}
else
{
row.CreateCell(10).SetCellValue(device.StatusUpdateTime.Value.ToString("yyyy/MM/dd HH:mm:ss"));
}
switch (device.MagStatus) //地磁检测状态
{
case 0:
row.CreateCell(11).SetCellValue("无车");
break;
case 1:
row.CreateCell(11).SetCellValue("有车");
break;
case 2:
row.CreateCell(11).SetCellValue("等待激活");
break;
case 3:
row.CreateCell(11).SetCellValue("强磁");
break;
default:
row.CreateCell(11).SetCellValue("");
break;
}
switch (device.RadaStatus) //雷达检测状态
{
case 0:
row.CreateCell(12).SetCellValue("无车");
break;
case 1:
row.CreateCell(12).SetCellValue("有车");
break;
case 2:
row.CreateCell(12).SetCellValue("遮挡");
break;
default:
row.CreateCell(12).SetCellValue("失效");
break;
}
switch (device.NbSignalCode) //射频故障代码
{
case 0:
row.CreateCell(13).SetCellValue("00(正常)");
break;
case 1:
row.CreateCell(13).SetCellValue("01(信号差)");
break;
default:
row.CreateCell(13).SetCellValue("");
break;
}
switch (device.SensorCode) //磁地和雷达传感器故障代码
{
case 0:
row.CreateCell(14).SetCellValue("00(正常)");
break;
case 1:
row.CreateCell(14).SetCellValue(device.FlashCode + "01(磁传感器故障)");
break;
case 2:
row.CreateCell(14).SetCellValue(device.FlashCode + "02(雷达传感器故障)");
break;
case 3:
row.CreateCell(14).SetCellValue(device.FlashCode + "03(磁和雷达传感器故障)");
break;
default:
row.CreateCell(14).SetCellValue("");
break;
}
switch (device.FlashCode) //存储器故障代码
{
case 0:
row.CreateCell(15).SetCellValue("01(正常)");
break;
case 1:
row.CreateCell(15).SetCellValue("02(无法读写)");
break;
case 2:
row.CreateCell(15).SetCellValue("03(已写满)");
break;
default:
row.CreateCell(15).SetCellValue("");
break;
}
switch (device.BatteryCode) //电池故障代码
{
case 0:
row.CreateCell(16).SetCellValue("00(正常)");
break;
case 1:
row.CreateCell(16).SetCellValue("01(电池电压低)");
break;
default:
row.CreateCell(16).SetCellValue("");
break;
}
switch (device.DevHealth) //设备健康状态
{
case 0:
row.CreateCell(17).SetCellValue("00(设备无故障)");
break;
case 1:
row.CreateCell(17).SetCellValue("01(电池电量低)");
break;
case 2:
row.CreateCell(17).SetCellValue("02(地磁故障)");
break;
default:
row.CreateCell(17).SetCellValue("");
break;
}
row.CreateCell(18).SetCellValue(device.MagneticXYZ); //磁场三轴采样
row.CreateCell(19).SetCellValue(device.RadarXYZ); //雷达三轴采样
//雷达检测到的距离值
if (device.Distance != null)
{
row.CreateCell(20).SetCellValue(device.Distance + "mm");
}
else
{
row.CreateCell(20).SetCellValue("");
}
row.CreateCell(21).SetCellValue(device.FwVer); //设备软件版本
row.CreateCell(22).SetCellValue(device.HwVer); //设备硬件版本
//设备添加时间
if (device.CreationTime == null)
{
row.CreateCell(23).SetCellValue("");
}
else
{
row.CreateCell(23).SetCellValue(device.CreationTime.Value.ToString("yyyy/MM/dd HH:mm:ss"));
}
#endregion 写值
Sheet1.GetRow(index + 1).Height = 30 * 15;
row.GetCell(0).CellStyle = ContentStyle2;
row.GetCell(1).CellStyle = ContentStyle2;
row.GetCell(2).CellStyle = ContentStyle;
row.GetCell(3).CellStyle = ContentStyle2;
row.GetCell(4).CellStyle = ContentStyle2;
row.GetCell(5).CellStyle = ContentStyle2;
row.GetCell(6).CellStyle = ContentStyle2;
row.GetCell(7).CellStyle = ContentStyle2;
row.GetCell(8).CellStyle = ContentStyle2;
row.GetCell(9).CellStyle = ContentStyle2;
row.GetCell(10).CellStyle = ContentStyle;
row.GetCell(11).CellStyle = ContentStyle2;
row.GetCell(12).CellStyle = ContentStyle2;
row.GetCell(13).CellStyle = ContentStyle2;
row.GetCell(14).CellStyle = ContentStyle2;
row.GetCell(15).CellStyle = ContentStyle2;
row.GetCell(16).CellStyle = ContentStyle2;
row.GetCell(17).CellStyle = ContentStyle2;
row.GetCell(18).CellStyle = ContentStyle2;
row.GetCell(19).CellStyle = ContentStyle2;
row.GetCell(20).CellStyle = ContentStyle2;
row.GetCell(21).CellStyle = ContentStyle2;
row.GetCell(22).CellStyle = ContentStyle2;
row.GetCell(23).CellStyle = ContentStyle;
index++;
}
string directory = Path.GetDirectoryName(filePath);
// 如果不存在该目录就创建该目录
if (!Directory.Exists(directory))
{
Directory.CreateDirectory(directory);
}
if (System.IO.File.Exists(filePath))
{
System.IO.File.Delete(filePath);
}
FileStream fs = new FileStream(filePath, FileMode.Create);
xssfworkbook.Write(fs);
fs.Close();
}
/// <summary>
/// 导出到Excel函数
/// </summary>
private bool ExportToExcel(string fileName, string[] columnName, string date)
{
bool bRet = true;
System.Windows.Forms.SaveFileDialog sfd = new System.Windows.Forms.SaveFileDialog();
sfd.Filter = "Excel 文件(*.xlsx;*.xls)|*.xlsx;*.xls";
sfd.FileName = fileName;
if (sfd.ShowDialog() != DialogResult.OK)
{
bRet = false;
return(bRet);
}
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(fileName);
sheet.PrintSetup.PaperSize = 9; // A4打印形式
sheet.PrintSetup.Landscape = false; // 纵向打印
var p = fileName.Split('_');
#region 创建标题名称
XSSFRow title = (XSSFRow)sheet.CreateRow(0);
for (int j = 0; j < columnName.Count(); j++)
{
XSSFCell headCell = (XSSFCell)title.CreateCell(j, CellType.String);
}
// 合并单元格
CellRangeAddress region1 = new CellRangeAddress(0, 0, 0, (columnName.Count() - 1));
sheet.AddMergedRegion(region1);
title.CreateCell(0).SetCellValue("庄信万丰（上海）化工有限公司装载机数据表");
ICellStyle titleStyle = workbook.CreateCellStyle();// 样式
// 设置单元格的样式：水平对齐居中
titleStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
// 将新的样式赋给单元格
title.GetCell(0).CellStyle = titleStyle;
#endregion
#region 创建日期
XSSFRow dateRow = (XSSFRow)sheet.CreateRow(1);
for (int j = 0; j < columnName.Count(); j++)
{
XSSFCell headCell = (XSSFCell)dateRow.CreateCell(j, CellType.String);
}
// 合并单元格
CellRangeAddress region3 = new CellRangeAddress(1, 1, 0, (columnName.Count() - 1));
sheet.AddMergedRegion(region3);
string tempDate = "导出时间:" + date;
dateRow.CreateCell(0).SetCellValue(tempDate);
#endregion
// 加载数据
XSSFRow headRow = (XSSFRow)sheet.CreateRow(2);
for (int i = 0; i < columnName.Count(); i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(columnName[i]);
headCell.CellStyle = AddStytle(workbook);
}
for (int i = 0; i < dictData.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 3);
for (int j = 0; j < dictData[i].Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
cell.CellStyle = AddStytle(workbook);
if (dictData[i][j] == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dictData[i][j] is string)
{
cell.SetCellValue(dictData[i][j].ToString());
}
else if (dictData[i][j] is decimal)
{
cell.SetCellValue(Convert.ToSingle(dictData[i][j]));
}
else if (dictData[i][j] is int)
{
cell.SetCellValue(Convert.ToInt32(dictData[i][j]));
}
else if (dictData[i][j] is double)
{
cell.SetCellValue(Convert.ToDouble(dictData[i][j]));
}
else if (dictData[i][j] is Decimal)
{
cell.SetCellValue(Convert.ToDouble(dictData[i][j]));
}
else if (dictData[i][j] is DateTime)
{
cell.SetCellValue(Convert.ToDateTime(dictData[i][j]).ToString("yyyy-MM-dd hh:ss:mm"));
}
}
}
}
for (int i = 0; i < columnName.Count(); i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
workbook.Write(fs);
}
#endregion
AutoDeleteMessageBox Auto = new AutoDeleteMessageBox(); // 自动关闭窗口
MessageBox.Show("恭喜，导出成功!", "MessageBox");
return(bRet);
}
//TODO 边框、单元格宽度、高度
public void exportExcel(VarietyReport varietyReport)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("Sheet1");
setColumnWidth(sheet);
initStyle(workbook);
//第一行，品种、起止时间
XSSFRow row0 = (XSSFRow)sheet.CreateRow(0);
setRowHeight(row0);
XSSFCell cell = (XSSFCell)row0.CreateCell(0);
cell.CellStyle = headerStyle;
cell.SetCellValue(varietyReport.varietyName + Variety.getAgreementName(varietyReport.agreement));
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 1, 2));
cell = (XSSFCell)row0.CreateCell(1);
cell.CellStyle = headerStyle;
cell.SetCellValue(Utils.formatDate(varietyReport.startingDate) + "-" + Utils.formatDate(varietyReport.endingDate));
List <CycleReport> cycleReports = varietyReport.cycleReports;
if (cycleReports != null)
{
for (int i = 0; i < cycleReports.Count; i++)
{
if (i % 2 == 0)
{
int colStartIndex = 0;
CycleReport cycleReport = cycleReports[i];
if (cycleReport != null)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(leftRowIndex);
setRowHeight(row);
leftRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
cell.CellStyle = headerStyle;
cell.SetCellValue(cycleReport.cycleName);
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算开始时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算结束时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号个数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = titleStyle;
cell.SetCellValue("最终权益");
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = titleStyle;
cell.SetCellValue("夏普比率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤");
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤比");
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = titleStyle;
cell.SetCellValue("风险率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手平均盈亏");
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = titleStyle;
cell.SetCellValue("胜率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = titleStyle;
cell.SetCellValue("模型得分");
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大盈利");
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续盈利次数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续亏损次数");
List <ModelReport> modelReports = cycleReport.modelReports;
if (modelReports != null)
{
foreach (ModelReport modelReport in modelReports)
{
row = (XSSFRow)sheet.CreateRow(leftRowIndex);
setRowHeight(row);
leftRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
if (modelReport.warning)
{
cell.CellStyle = warningStyle;
}
else
{
cell.CellStyle = commonStyle;
}
cell.SetCellValue(modelReport.modelName);
//期望模型名称和实际模型名称不一致，则不导出数据
bool isErrorData = false;
if (!modelReport.modelName.Equals(modelReport.realisticModelName))
{
isErrorData = true;
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.startingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.endingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.signalNumber);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.lastInterest);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.sharpeRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracement);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracementRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.hazardRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.avgProfitLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.winRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.score);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxProfit);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLoss);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousProfitabilityTimes);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousLossesTimes);
}
}
}
}
}
else
{
int colStartIndex = 18;
CycleReport cycleReport = cycleReports[i];
if (cycleReport != null)
{
XSSFRow row = (XSSFRow)sheet.GetRow(rightRowIndex);
rightRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
cell.CellStyle = headerStyle;
cell.SetCellValue(cycleReport.cycleName);
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算开始时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号计算结束时间");
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = titleStyle;
cell.SetCellValue("信号个数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = titleStyle;
cell.SetCellValue("最终权益");
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = titleStyle;
cell.SetCellValue("夏普比率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤");
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = titleStyle;
cell.SetCellValue("权益最大回撤比");
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = titleStyle;
cell.SetCellValue("风险率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = titleStyle;
cell.SetCellValue("每手平均盈亏");
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = titleStyle;
cell.SetCellValue("胜率");
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = titleStyle;
cell.SetCellValue("模型得分");
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大盈利");
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大亏损");
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续盈利次数");
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = titleStyle;
cell.SetCellValue("最大持续亏损次数");
List <ModelReport> modelReports = cycleReport.modelReports;
if (modelReports != null)
{
foreach (ModelReport modelReport in modelReports)
{
row = (XSSFRow)sheet.GetRow(rightRowIndex);
rightRowIndex++;
cell = (XSSFCell)row.CreateCell(colStartIndex + 0);
if (modelReport.warning)
{
cell.CellStyle = warningStyle;
}
else
{
cell.CellStyle = commonStyle;
}
cell.SetCellValue(modelReport.modelName);
//期望模型名称和实际模型名称不一致，则不导出数据
bool isErrorData = false;
if (!modelReport.modelName.Equals(modelReport.realisticModelName))
{
isErrorData = true;
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 1);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.startingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 2);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.endingDate);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 3);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.signalNumber);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 4);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.lastInterest);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 5);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.sharpeRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 6);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracement);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 7);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.interestMaxRetracementRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 8);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.hazardRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 9);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 10);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.avgProfitLossPerHand);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 11);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.winRatio);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 12);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.score);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 13);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxProfit);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 14);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxLoss);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 15);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousProfitabilityTimes);
}
cell = (XSSFCell)row.CreateCell(colStartIndex + 16);
cell.CellStyle = commonStyle;
if (isErrorData)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(modelReport.maxContinuousLossesTimes);
}
}
}
}
}
}
}
String filePath = Utils.getExportDir() + varietyReport.varietyName + "_" + Variety.getAgreementName(varietyReport.agreement) + "_" + Utils.getDate() + "_回测" + "_" + Utils.getTimeMillisecond() + ".xlsx";
FileStream file = new FileStream(filePath, FileMode.Create);
workbook.Write(file);
file.Close();
}
private static MemoryStream ExportExcel2007(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet() as XSSFSheet;
//格式日期
XSSFCellStyle dateStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat format = workbook.CreateDataFormat() as XSSFDataFormat;
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//格式数字
XSSFCellStyle decimelStyle = workbook.CreateCellStyle() as XSSFCellStyle;
XSSFDataFormat decimelformat = workbook.CreateDataFormat() as XSSFDataFormat;
decimelStyle.DataFormat = decimelformat.GetFormat("0.00####");
// 取得列宽
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)
{
if (rowIndex == 1048576 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
#region 表头及样式
{
XSSFRow headerRow = sheet.CreateRow(0) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
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 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(1) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = HorizontalAlignment.Center;
XSSFFont font = workbook.CreateFont() as XSSFFont;
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.IsLocked = true;
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] > 255 ? 254 : arrColWidth[column.Ordinal] + 1) * 256);
}
//sheet.CreateFreezePane(0, 2, 0, dtSource.Columns.Count - 1);
}
rowIndex = 2;
#endregion 列头及样式
rowIndex = 2;
}
#region 填充内容
XSSFRow dataRow = sheet.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
if (drValue.Length > 0)
{
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);
newCell.CellStyle = decimelStyle;
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion 填充内容
rowIndex++;
}
NpoiMemoryStream ms = new NpoiMemoryStream();
ms.AllowClose = false;
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
ms.Seek(0, SeekOrigin.Begin);
ms.AllowClose = true;
return(ms);
}
////////////////////////////////////////////////////////////////////
private void CreateRowValueSheet_1(PackingListItem item, DatabaseItem databaseItem)
{
NPOI.SS.UserModel.IRow r;
if (item.GetQuantity() < databaseItem.GetMaxPacketSize())
{
r = sheet_1.CreateRow(rowIndex_sheet_1);
rowIndex_sheet_1++;
numberPacket_sheet_1++;
double valueMerger = item.GetQuantity();
if (item.GetMergerList() != null && item.GetMergerList().Count > 0)
{
foreach (MergerItem merger in item.GetMergerList())
{
valueMerger += merger.GetQuantity();
}
}
for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
{
ICell cell = r.CreateCell(idex);
cell.CellStyle = sheet_1_headerStyle;
switch (idex)
{
case 0:
cell.SetCellValue(this.packingParser.GetDateTime());
break;
case 1:
cell.SetCellValue(item.GetNameProduct());
break;
case 2:
cell.SetCellValue(item.GetColor1());
break;
case 3:
cell.SetCellValue(item.GetColor2());
break;
case 4:
cell.SetCellValue(item.GetProductSize());
break;
case 5:
cell.SetCellValue(item.GetQuantity());
if (valueMerger > item.GetQuantity())
{
cell.CellStyle = sheet_1_headerStyle_2;
}
break;
case 6:
cell.SetCellValue(1);
break;
case 7:
cell.SetCellValue(numberPacket_sheet_1);
break;
case 8:
if (valueMerger <= item.GetQuantity())
{
cell.SetCellValue(valueMerger);
}
break;
case 9:
cell.SetCellValue(databaseItem.GetNetWeight());
break;
case 10:
cell.SetCellValue(databaseItem.GetAllWeight());
break;
case 11:
cell.SetCellValue(databaseItem.GetPacketInformation());
break;
default:
break;
}
}
}
else
{
for (int tmp = 0; tmp < (int)(item.GetQuantity() / databaseItem.GetMaxPacketSize()); tmp++)
{
numberPacket_sheet_1++;
r = sheet_1.CreateRow(rowIndex_sheet_1);
rowIndex_sheet_1++;
for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
{
ICell cell = r.CreateCell(idex);
cell.CellStyle = sheet_1_headerStyle;
switch (idex)
{
case 0:
cell.SetCellValue(this.packingParser.GetDateTime());
break;
case 1:
cell.SetCellValue(item.GetNameProduct());
break;
case 2:
cell.SetCellValue(item.GetColor1());
break;
case 3:
cell.SetCellValue(item.GetColor2());
break;
case 4:
cell.SetCellValue(item.GetProductSize());
break;
case 5:
cell.SetCellValue(databaseItem.GetMaxPacketSize());
break;
case 6:
cell.SetCellValue(1);
break;
case 7:
cell.SetCellValue(numberPacket_sheet_1);
break;
case 8:
cell.SetCellValue(databaseItem.GetMaxPacketSize());
break;
case 9:
cell.SetCellValue(databaseItem.GetNetWeight());
break;
case 10:
cell.SetCellValue(databaseItem.GetAllWeight());
break;
case 11:
cell.SetCellValue(databaseItem.GetPacketInformation());
break;
default:
break;
}
}
}
}
if (item.GetMergerList() != null && item.GetMergerList().Count > 0)
{
foreach (MergerItem merger in item.GetMergerList())
{
r = sheet_1.CreateRow(rowIndex_sheet_1);
rowIndex_sheet_1++;
for (int idex = 0; idex < Sheet_1_Header.Length; idex++)
{
ICell cell = r.CreateCell(idex);
cell.CellStyle = sheet_1_headerStyle;
switch (idex)
{
case 0:
cell.SetCellValue(this.packingParser.GetDateTime());
break;
case 1:
cell.SetCellValue(merger.GetNameProduct());
break;
case 2:
cell.SetCellValue(merger.GetColor1());
break;
case 3:
cell.SetCellValue(merger.GetColor2());
break;
case 4:
cell.SetCellValue(merger.GetProductSize());
break;
case 5:
cell.SetCellValue(merger.GetQuantity());
cell.CellStyle = sheet_1_headerStyle_2;
break;
case 6:
break;
case 7:
cell.SetCellValue(numberPacket_sheet_1);
break;
case 8:
break;
case 9:
//cell.SetCellValue(databaseItem.GetNetWeight());
break;
case 10:
//cell.SetCellValue(databaseItem.GetAllWeight());
break;
case 11:
//cell.SetCellValue(databaseItem.GetPacketInformation());
break;
default:
break;
}
}
}
r = sheet_1.GetRow(rowIndex_sheet_1 - (1 + item.GetMergerList().Count));
String fomular = "SUM(F" + (rowIndex_sheet_1 - item.GetMergerList().Count) + ":F" + rowIndex_sheet_1 + ")";
if (r.GetCell(8) != null)
{
r.GetCell(8).CellFormula = fomular;
}
NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count),
rowIndex_sheet_1 - 1, 6, 6);
NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(rowIndex_sheet_1 - (1 + item.GetMergerList().Count),
rowIndex_sheet_1 - 1, 8, 8);
sheet_1.AddMergedRegion(range_1);
sheet_1.AddMergedRegion(range_2);
}
}
private void CreateMergerItem(int type, ParkingParserItem item, PackingListItem packItem)
{
NPOI.SS.UserModel.IRow row;
int start_index;
int end_index;
int total = Sheet_2_GetNumberOfColum();
CombineMergerListItem combine = new CombineMergerListItem(packItem);
combine.RunCombineMergerListItem();
List <CombineItem> listCombine = combine.GetListCombineItem();
ICell cell;
ICell cellSum = null;
start_index = sheet_2_rowIndex;
for (int itemIndex = 0; itemIndex < listCombine.Count; itemIndex++)
{
//create new row
row = sheet_2.CreateRow(sheet_2_rowIndex);
sheet_2_rowIndex++;
for (int run = 0; run < Sheet_2_GetNumberOfColum(); run++)
{
cell = row.CreateCell(run);
cell.CellStyle = sheet_2_mainStyle_2;
switch (run)
{
case 0:
if (itemIndex == 0)
{
sheet_2_numberPacket++;
cell.SetCellValue(sheet_2_numberPacket + "");
}
break;
case 1:
if (itemIndex == 0)
{
cell.SetCellValue(1);
}
break;
case 2:
cell.SetCellValue(packItem.GetNameProduct());
break;
case 3:
cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor1());
break;
case 4:
cell.SetCellValue(listCombine.ElementAt(itemIndex).GetColor2());
break;
default:
if ((total - 4) == run)
{
if (itemIndex == 0)
{
cellSum = cell;
}
}
else if ((total - 3) == run)
{
if (itemIndex == 0)
{
cell.SetCellValue("PCS");
}
}
else if ((total - 2) == run)
{
if (itemIndex == 0)
{
String formula = item.GetDatabaseItem().GetNetWeight() + "*B" + sheet_2_rowIndex;
cell.SetCellFormula(formula);
}
}
else if ((total - 1) == run)
{
if (itemIndex == 0)
{
String formula = item.GetDatabaseItem().GetAllWeight() + "*B" + sheet_2_rowIndex;
cell.SetCellFormula(formula);
}
}
else
{
cell.SetCellValue("");
}
break;
}
}
foreach (CombineEle element in listCombine.ElementAt(itemIndex).GetListElement())
{
int size_index = Sheet_2_GetColumIndex(type, element.GetSize());
ICell cellSize = row.GetCell(size_index);
cellSize.SetCellValue(element.GetQuality());
}
}
end_index = sheet_2_rowIndex - 1;
String columName = Sheet_2_GetStringOfColum(Sheet_2_GetNumberOfColum() - 4);
String formulaSum = "SUM(F" + (start_index + 1) + ":" + columName + (end_index + 1) + ")";
if (cellSum != null)
{
cellSum.SetCellFormula(formulaSum);
}
if (end_index != start_index)
{
NPOI.SS.Util.CellRangeAddress range_1 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 0, 0);
NPOI.SS.Util.CellRangeAddress range_2 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, 1, 1);
NPOI.SS.Util.CellRangeAddress range_3 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 4, total - 4);
NPOI.SS.Util.CellRangeAddress range_4 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 3, total - 3);
NPOI.SS.Util.CellRangeAddress range_5 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 2, total - 2);
NPOI.SS.Util.CellRangeAddress range_6 = new NPOI.SS.Util.CellRangeAddress(start_index, end_index, total - 1, total - 1);
sheet_2.AddMergedRegion(range_1);
sheet_2.AddMergedRegion(range_2);
sheet_2.AddMergedRegion(range_3);
sheet_2.AddMergedRegion(range_4);
sheet_2.AddMergedRegion(range_5);
sheet_2.AddMergedRegion(range_6);
sheet_2.GetRow(start_index).GetCell(0).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(1).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 4).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 3).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 2).CellStyle = sheet_2_mergerStyle;
sheet_2.GetRow(start_index).GetCell(total - 1).CellStyle = sheet_2_mergerStyle;
}
}