public void TestFormulaString()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFCell cell = (XSSFCell)wb.CreateSheet().CreateRow(0).CreateCell(0);
CT_Cell ctCell = cell.GetCTCell(); //low-level bean holding cell's xml
cell.SetCellFormula("A2");
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//the value is not Set and cell's type='N' which means blank
Assert.AreEqual(ST_CellType.n, ctCell.t);
//set cached formula value
cell.SetCellValue("t='str'");
//we are still of 'formula' type
Assert.AreEqual(CellType.Formula, cell.CellType);
Assert.AreEqual(cell.CellFormula, "A2");
//cached formula value is Set and cell's type='STR'
Assert.AreEqual(ST_CellType.str, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "t='str'");
//now remove the formula, the cached formula result remains
cell.SetCellFormula(null);
Assert.AreEqual(CellType.String, cell.CellType);
Assert.AreEqual(ST_CellType.str, ctCell.t);
//the line below failed prior to fix of Bug #47889
Assert.AreEqual(cell.StringCellValue, "t='str'");
//revert to a blank cell
cell.SetCellValue((String)null);
Assert.AreEqual(CellType.Blank, cell.CellType);
Assert.AreEqual(ST_CellType.n, ctCell.t);
Assert.AreEqual(cell.StringCellValue, "");
}
private static void SetCellValue(XSSFCell oldCell, XSSFCell newCell)
{
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.SetCellFormula(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;
}
}
/// <summary>
/// Копирование содержимого ячеек с соранением типа данных
/// </summary>
/// <param name="oldCell"></param>
/// <param name="newCell"></param>
private void CopyCellValue(HSSFCell oldCell, XSSFCell newCell)
{
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellType(CellType.Blank);
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
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();
}
public void TestMultisheetFormulaEval()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;
// sheet1 A1
XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 A1
cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 B1
cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet3 A1
cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet1 A2 formulae
cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");
// sheet1 A3 formulae
cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
Assert.AreEqual(3.0, cell.NumericCellValue, 0);
cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
Assert.AreEqual(4.0, cell.NumericCellValue, 0);
}
finally
{
wb.Close();
}
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
public int WriteArray_To_Excel(int rowAvailableCell, int startingCol, DateTime?[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
var newDataFormat = workbook.CreateDataFormat();
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
if (infoArray[rowCounter, columnCounter] != null)
{
DateTime dateTime;
if (DateTime.TryParse(infoArray[rowCounter, columnCounter].ToString(), out dateTime))
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellValue(dateTime.Date);
cell.CellStyle = cellStyle;
cell.CellStyle.DataFormat = newDataFormat.GetFormat("MM/dd/yyyy");
}
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
public void TestCopyCellFrom_CellCopyPolicy_style()
{
setUp_testCopyCellFrom_CellCopyPolicy();
srcCell.SetCellValue((String)null);
// Paste styles only
CellCopyPolicy policy = new CellCopyPolicy.Builder().CellValue(false).Build();
destCell.CopyCellFrom(srcCell, policy);
Assert.AreEqual(srcCell.CellStyle, destCell.CellStyle);
// Old cell value should not have been overwritten
Assert.AreNotEqual(CellType.Blank, destCell.CellType);
Assert.AreEqual(CellType.Boolean, destCell.CellType);
Assert.AreEqual(true, destCell.BooleanCellValue);
}
public void TestMissingRAttribute()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFRow row = (XSSFRow)sheet.CreateRow(0);
XSSFCell a1 = (XSSFCell)row.CreateCell(0);
a1.SetCellValue("A1");
XSSFCell a2 = (XSSFCell)row.CreateCell(1);
a2.SetCellValue("B1");
XSSFCell a4 = (XSSFCell)row.CreateCell(4);
a4.SetCellValue("E1");
XSSFCell a6 = (XSSFCell)row.CreateCell(5);
a6.SetCellValue("F1");
assertCellsWithMissingR(row);
a2.GetCTCell().unsetR();
a6.GetCTCell().unsetR();
assertCellsWithMissingR(row);
wb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
assertCellsWithMissingR(row);
}
public void ExcelImportStart(DataTable datable, string sheetname, int rowint)
{
XSSFSheet ws = (XSSFSheet)hssfworkbook.GetSheet(sheetname);
//添加或修改WorkSheet里的数据
System.Data.DataTable dt = datable;
#region
if (dt.Rows.Count > 0)
{
var rowIndex = rowint;
foreach (DataRow row in dt.Rows)
{
XSSFRow dataRow = ws.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dt.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
newCell.SetCellValue(drValue);
}
rowIndex++;
}
}
#endregion
ws.ForceFormulaRecalculation = true;
}
public static XSSFWorkbook BuildSwitchData <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
XSSFRow row_Title = (XSSFRow)sheet.CreateRow(0); //创建列头行
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
XSSFCell cell_Title = (XSSFCell)row_Title.CreateCell(ii); //创建单元格
//cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
//sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
for (int i = 0; i < list.Count; i++)
{
XSSFRow row_Content = (XSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
XSSFCell cell_Conent = (XSSFCell)row_Content.CreateCell(jj); //创建单元格
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
/**
* @param oldCell
* @param newCell
* @param styleMap
*/
public static void copyCell(HSSFCell oldCell, XSSFCell newCell, Dictionary <int, HSSFCellStyle> styleMap)
{
if (styleMap != null)
{
int stHashCode = oldCell.CellStyle.GetHashCode();
HSSFCellStyle sourceCellStyle = styleMap[stHashCode];
XSSFCellStyle destnCellStyle = (XSSFCellStyle)newCell.CellStyle;
if (sourceCellStyle == null)
{
sourceCellStyle = (HSSFCellStyle)oldCell.Sheet.Workbook.CreateCellStyle();
}
destnCellStyle.CloneStyleFrom(oldCell.CellStyle);
styleMap.Add(stHashCode, sourceCellStyle);
newCell.CellStyle = destnCellStyle;
}
switch (oldCell.CellType)
{
case CellType.String:
newCell.SetCellValue(oldCell.StringCellValue);
break;
case CellType.Numeric:
newCell.SetCellValue(oldCell.NumericCellValue);
break;
case CellType.Blank:
newCell.SetCellValue(string.Empty); //!!!
break;
case CellType.Boolean:
newCell.SetCellValue(oldCell.BooleanCellValue);
break;
case CellType.Error:
newCell.SetCellErrorValue(oldCell.ErrorCellValue);
break;
case CellType.Formula:
newCell.SetCellFormula(oldCell.CellFormula);
break;
default:
break;
}
}
private void GetSalaryList(ISheet mainSheet)
{
FileStream salaryFileStream = new FileStream(@"e:/2018年3月发薪名单.xlsx", FileMode.Open);
XSSFWorkbook salaryWorkbook = new XSSFWorkbook(salaryFileStream);
XSSFSheet salarySheet = (XSSFSheet)salaryWorkbook.GetSheet("Sheet1");
for (int i = 0; i < salarySheet.PhysicalNumberOfRows; i++)
{
XSSFRow tRow = (XSSFRow)mainSheet.CreateRow(i);
XSSFRow sRow = (XSSFRow)salarySheet.GetRow(i);
if (sRow != null && tRow != null)
{
for (int j = 0; j < 2; j++)
{
XSSFCell sCell = (XSSFCell)sRow.GetCell(j);
if (sCell == null)
{
break;
}
string cellValue = sCell.ToString();
XSSFCell tCell = (XSSFCell)tRow.CreateCell(j);
//CopyCellStyle(mainWorkbook, salaryWorkbook, tCell, sCell);
XSSFCellStyle style = (XSSFCellStyle)sCell.CellStyle;
XSSFCellStyle style1 = (XSSFCellStyle)mainWorkbook.CreateCellStyle();
XSSFColor color = null;
if (style.FillForegroundColorColor != null)
{
byte[] pa = style.FillForegroundColorColor.RGB;
string key = pa[0] + "," + pa[1] + "," + pa[2];
if (dictionary.ContainsKey(key))
{
style1.FillForegroundColor = dictionary[key];
}
else
{
Console.WriteLine("找不到该颜色!" + key);
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
}
else
{
Console.WriteLine("找不到该颜色!");
style1.FillForegroundColor = HSSFColor.Automatic.Index;
}
//byte[] pa1 = style.FillBackgroundColorColor.RGB;
//style1.FillForegroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
style1.FillPattern = sCell.CellStyle.FillPattern;
//style1.FillBackgroundColor = 20;//GetColor(sCell.CellStyle.FillForegroundColor);
tCell.CellStyle = style1;
tCell.SetCellValue(cellValue);
}
}
}
salaryFileStream.Close();
salaryWorkbook.Close();
}
/// <summary>
/// Render DataTable to NPOI Excel 2003 MemoryStream
/// NOTE: Limitation of 65,536 rows suppored by XLS
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <returns>MemoryStream containing NPOI Excel workbook</returns>
public static Stream RenderDataTableToExcelXSSF(DataTable sourceTable)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
MemoryStream memoryStream = new MemoryStream();
// By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
XSSFSheet sheet = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
// Header Row
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// Detail Rows
int rowIndex = 1;
// 建立儲存格樣式。
XSSFCellStyle style1 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();//workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style1.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//HSSFCellStyle.SOLID_FOREGROUND;
XSSFFont f = (XSSFFont)xssfworkbook.CreateFont();
f.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
f.FontName = "宋体";
foreach (DataRow row in sourceTable.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
//HSSFCell cell1 = dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
cell.CellStyle = style1;
cell.CellStyle.SetFont(f);
cell.SetCellValue(row[column].ToString());
}
rowIndex++;
}
xssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0;
return(memoryStream);
}
public static XSSFWorkbook DataTabletoExcel(DataTable dt)
{
XSSFWorkbook xssfwb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)xssfwb.CreateSheet();
sheet.CreateRow(dt.Rows.Count);
for (int i = 0; i < dt.Rows.Count; i++)
{
DataRow dr = dt.Rows[i];
sheet.CreateRow(i);
XSSFRow row = (XSSFRow)sheet.GetRow(i);
for (int j = 0; j < dt.Columns.Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
cell.SetCellValue(dr[j].ToString());
}
}
return(xssfwb);
}
private void AnalysisCellString(XSSFCell cell)
{
XSSFRichTextString rich = (XSSFRichTextString)cell.RichStringCellValue;
string st2 = rich.String;
int formattingRuns = cell.RichStringCellValue.NumFormattingRuns;
if (formattingRuns > 0)
{
st2 = string.Empty;
}
XSSFFont font = (XSSFFont)_workBook.CreateFont();
for (int i = 0; i < formattingRuns; i++)
{
int startIdx = rich.GetIndexOfFormattingRun(i);
int length = rich.GetLengthOfFormattingRun(i);
string st = rich.String.Substring(startIdx, length);
if (i == 0)
{
short fontIndex = cell.CellStyle.FontIndex;
font = (XSSFFont)_workBook.GetFontAt(fontIndex);
}
else
{
font = (XSSFFont)rich.GetFontOfFormattingRun(i);
}
if (font.Color != IndexedColors.White.Index && font.Color != 0)
{
st2 += st;
}
}
font.Color = IndexedColors.Black.Index;
XSSFRichTextString rich2 = new XSSFRichTextString();
rich2.Append(st2, font);
cell.SetCellValue(rich2);
}
private static void CrearSeccionCabeceraDatos(XSSFWorkbook workbook, XSSFSheet HojaActual, PlantillaExcel Plantilla, PropertyInfo[] ColumnasDatos)
{
XSSFRow rowHeaderGrid = (XSSFRow)HojaActual.CreateRow(Plantilla.FilasCreadasIndice);
int ContadorColumna = 0;
foreach (var itemCols in ColumnasDatos)
{
XSSFCell colGridExcel = (XSSFCell)rowHeaderGrid.CreateCell(ContadorColumna);
if (Plantilla.EstiloCeldaCabecera != null)
{
colGridExcel.CellStyle = GenerarCellStyleCeldaCabecera(workbook, Plantilla);
}
string colName = Plantilla.CabeceraColumna.Any(x => x.Key == itemCols.Name) ? Plantilla.CabeceraColumna.FirstOrDefault(x => x.Key == itemCols.Name).Value : itemCols.Name;
int colWidth = Plantilla.TamanioColumna.Any(x => x.Key == itemCols.Name) ? Plantilla.TamanioColumna.FirstOrDefault(x => x.Key == itemCols.Name).Value : 80;
colGridExcel.SetCellValue(new XSSFRichTextString(colName));
colGridExcel.Sheet.DefaultColumnWidth = colWidth;
ContadorColumna++;
}
Plantilla.FilasCreadasIndice++;
}
public int WriteArray_To_Excel(int rowAvailableCell, int startingCol, string[,] infoArray)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int rowCounter = 0; rowCounter <= infoArray.GetUpperBound(0); rowCounter++)
{
for (int columnCounter = 0; columnCounter <= infoArray.GetUpperBound(1); columnCounter++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
cell.SetCellValue(infoArray[rowCounter, columnCounter]);
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
return(startingCol + infoArray.GetUpperBound(1) + 1);
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream DatagridviewToExcel(DataGridView myDgv, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
try
{
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
}
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
int columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
headerRow.CreateCell(columnIndex).SetCellValue(column.HeaderText);//If Caption not set, returns the ColumnName value
columnIndex++;
}
// handling value.
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataGridViewRow row in myDgv.Rows)
{
IRow dataRow = sheet.CreateRow(row.Index + 1);
columnIndex = 0;
foreach (DataGridViewColumn column in myDgv.Columns)
{
if (!column.Visible)
{
continue;
}
string drValue = myDgv.Rows[row.Index].Cells[column.Index].Value == null || !column.Visible ? "" :
myDgv.Rows[row.Index].Cells[column.Index].Value.ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(columnIndex);
if (column.ValueType == null)
{
column.ValueType = typeof(string);
}
switch (column.ValueType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
columnIndex++;
}
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return ms;
}
catch (Exception ex)
{
throw new Exception(ex.Message);
}
}
///
/// DataTable转换成Excel文档流
///
///
///
static MemoryStream RenderToExcel(DataTable table, CE_SystemFileType fileType)
{
MemoryStream ms = new MemoryStream();
using (table)
{
IWorkbook workbook = null;
ISheet sheet = null;
IRow headerRow = null;
if (fileType == CE_SystemFileType.Excel)
{
#region xls
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
HSSFCellStyle dateStyle = (HSSFCellStyle)workbook.CreateCellStyle();
HSSFDataFormat format = (HSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
HSSFCell newCell = (HSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
else if (fileType == CE_SystemFileType.Excel2010)
{
#region xlsx
workbook = new XSSFWorkbook();
sheet = workbook.CreateSheet();
headerRow = sheet.CreateRow(0);
// handling header.
foreach (DataColumn column in table.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.Caption);//If Caption not set, returns the ColumnName value
}
// handling value.
int rowIndex = 1;
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
foreach (DataRow row in table.Rows)
{
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in table.Columns)
{
string drValue = row[column] == null ? "" : row[column].ToString();
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
switch (column.DataType.ToString())
{
case "System.String"://字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime"://日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;//格式化显示
break;
case "System.Boolean"://布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16"://整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal"://浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull"://空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
rowIndex++;
}
#endregion
}
AutoSizeColumns(sheet);
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
}
return ms;
}
//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();
}
public void WriteList_To_Excel(int rowAvailableCell, int startingCol, int startingReadColumn, int lastReadColumn, List <List <object> > data, int type)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
var newDataFormat = workbook.CreateDataFormat();
for (int rowCounter = 0; rowCounter < data.Count; rowCounter++)
{
for (int columnCounter = 0, dataStartColumn = startingReadColumn; dataStartColumn <= lastReadColumn; columnCounter++, dataStartColumn++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(rowAvailableCell + rowCounter).CreateCell(columnCounter + startingCol);
if (data[rowCounter][dataStartColumn] != null)
{
switch (type)
{
case 0:
double value;
if (Double.TryParse(data[rowCounter][dataStartColumn].ToString(), out value))
{
cell.SetCellValue(value);
}
else
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
}
cell.CellStyle = cellStyle;
break;
case 1:
try
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
cell.CellStyle = cellStyle;
}
catch (Exception ex)
{
cell.SetCellValue("");
cell.CellStyle = cellStyle;
}
break;
case 2:
int dateValue;
DateTime dateTime;
cell.CellStyle = cellStyle;
cell.CellStyle.DataFormat = newDataFormat.GetFormat(defaultDateFormat);
if (int.TryParse(data[rowCounter][dataStartColumn].ToString(), out dateValue))
{
cell.SetCellValue(dateValue);
}
else if (DateTime.TryParse(data[rowCounter][dataStartColumn].ToString(), /*CultureInfo.CreateSpecificCulture("en-US"), DateTimeStyles.None*/ out dateTime))
{
cell.SetCellValue(dateTime);
}
else
{
cell.SetCellValue(data[rowCounter][dataStartColumn].ToString());
}
break;
}
}
}
catch (Exception ex)
{
logger.logException(ex);
}
}
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream DataTableToExcel(DataTable dtSource, string strHeaderText)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
//workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = System.DateTime.Now;
//workbook.SummaryInformation = si;
}
#endregion
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = (XSSFSheet)workbook.CreateSheet();
}
#region 表头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
// headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
// sheet.AddMergedRegion(new Region(0, 0, 0, dtSource.Columns.Count - 1));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(1);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 300);
}
// headerRow.Dispose();
}
#endregion
rowIndex = 2;
}
#endregion
#region 填充内容
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
System.DateTime dateV;
System.DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
static void ExportDT(DataTable dtSource, string strHeaderText, string sheetName, FileStream fs)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet;
if (string.IsNullOrEmpty(sheetName))
{
sheet = workbook.CreateSheet() as XSSFSheet;
}
else
{
sheet = workbook.CreateSheet(sheetName) 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.UTF8.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.UTF8.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 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(strHeaderText);
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.SS.UserModel.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));
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
XSSFRow headerRow = sheet.CreateRow(rowIndex++) as XSSFRow;
XSSFCellStyle headStyle = workbook.CreateCellStyle() as XSSFCellStyle;
headStyle.Alignment = Npoi.Core.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
}
#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.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV = false;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV = 0;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV = 0;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
workbook.Write(fs);
fs.Close();
}
public StringBuilder readXls(string file, EERRDataAndMethods eerr, XSSFWorkbook twb, bool applyRate, Double rate)
{
StringBuilder retVal = new StringBuilder("");
HSSFWorkbook wb;
wb = new HSSFWorkbook(new FileStream(file, FileMode.Open));
ISheet sheet = wb.GetSheetAt(0);
IRow r = sheet.GetRow(0);
ICell c = r.GetCell(0);
string company = c.StringCellValue;
XSSFSheet sh = (XSSFSheet)twb.GetSheet("Estado resultado");
string acct = "";
string acctDesc = "";
for (int i = 1; i < sheet.LastRowNum; i++)
{
r = sheet.GetRow(i);
if (r != null)
{
c = r.GetCell(0);
if (c != null)
{
if (c.CellType == CellType.String && (c.StringCellValue).StartsWith(C_STR_IN_ACCOUNT))
{
acct = c.StringCellValue;
acct = acct.Substring(C_STR_IN_ACCOUNT.Length).Trim();
int pos = acct.IndexOf(' ');
acctDesc = acct.Substring(pos + 1).Trim();
acct = acct.Substring(0, pos).Trim();
Console.WriteLine("Account: " + acct + " " + acctDesc);
}
else if (acct.Length > 0 && r.LastCellNum >= 16)
{
//"Estado" 1,"Empresa" 2,"Agrupacion" 3,"Marca," 4,"EERR" 5,"Detalle EERR" 6,"Cuenta" 7,"Desc Cuenta" 8,
//"Mes" 9,"Fecha" 10,"# Compte" 11,"Tipo;Glosa" 12,"Area" 13,"C.Costo" 14,"Item" 15,"Desc Item" 16, "F.Efec" 17,
//"Analisis" 18,"Refer" 19,"Fch Ref" 20,"Fch Vto" 21,"DEBE" 22,"HABER" 23,"SALDO" 24,"Sucursal" 25
string s = "";
XSSFRow row = (XSSFRow)sh.CreateRow(sh.LastRowNum + 1);
XSSFCell cell = (XSSFCell)row.CreateCell(C_OUT_STAT - 1);
cell.SetCellValue(C_DATA_STATUS);
cell = (XSSFCell)row.CreateCell(C_OUT_CIA - 1);
cell.SetCellValue(company);
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DESC_AREA - 1);
cell.SetCellValue(eerr.getAgrupacion(c.ToString()));
cell = (XSSFCell)row.CreateCell(C_OUT_BRAND - 1);
cell.SetCellValue(eerr.getBrand(getCellValue(c)));
cell = (XSSFCell)row.CreateCell(C_OUT_DET_EERR - 1);
cell.SetCellValue(eerr.getLinea(acct));
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_NUM - 1);
cell.SetCellValue(acct);
cell = (XSSFCell)row.CreateCell(C_OUT_ACCT_DESC - 1);
cell.SetCellValue(acctDesc);
c = r.GetCell(C_IN_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_MONTH - 1);
cell.SetCellValue(getMonth(c));
c = r.GetCell(C_IN_COMPTE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMPTE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_TYPE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_TYPE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COMMENT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COMMENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_AREA - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_AREA - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_COST_CENTER - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_COST_CENT - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_ITEM - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM - 1);
cell.SetCellValue(getCellValue(c));
cell = (XSSFCell)row.CreateCell(C_OUT_ITEM_DESC - 1);
cell.SetCellValue(eerr.getItem(getCellValue(c)));
c = r.GetCell(C_IN_EFF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EFF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_ANALISYS_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_ANALYSIS_DATE - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REFERENCE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF - 1);
cell.SetCellValue(getCellValue(c));
c = r.GetCell(C_IN_REF_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_REF_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
c = r.GetCell(C_IN_EXP_DATE - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_EXP_DATE - 1);
cell.SetCellValue(getCellDateValue(c));
XSSFCell deb = null;
short doubleFormat = HSSFDataFormat.GetBuiltinFormat("#,##0"); //wb.CreateDataFormat().GetFormat("#,##0");
double v = 0;
c = r.GetCell(C_IN_DEBIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
deb = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_DEBIT - 1);
cell.SetCellValue((applyRate?rate:1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
XSSFCell cred = null;
c = r.GetCell(C_IN_CREDIT - 1);
cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cred = cell;
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
//cell = (XSSFCell)row.CreateCell(C_OUT_CREDIT - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Numeric);
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BALANCE - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s) && Double.TryParse(s, out v))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BALANCE - 1);
cell.SetCellValue((applyRate ? rate : 1) * v);
cell.SetCellType(CellType.Formula);
cell.SetCellFormula(String.Format("{0}{1}-{2}{3}", C_COL_DEBIT, cell.Row.RowNum + 1, C_COL_CREDIT, cell.Row.RowNum + 1));
cell.CellStyle.DataFormat = doubleFormat;
}
}
c = r.GetCell(C_IN_BRANCH - 1);
if (c != null)
{
s = c.ToString();
if (!string.IsNullOrEmpty(s))
{
cell = (XSSFCell)row.CreateCell(C_OUT_BRANCH - 1);
cell.SetCellValue(eerr.getSucursal(s));
}
}
}
}
}
}
return(retVal);
}
/// <summary>
/// 导出期初模板
/// </summary>
/// <param name="dtSource"></param>
/// <param name="exportTemplateFilePath"></param>
/// <param name="fillRow"></param>
/// <param name="replaceCells"></param>
/// <returns></returns>
private static MemoryStream ExportOpeningTemplate(DataTable dtSource, string[] dropDowndtSource, string exportTemplateFilePath, int fillRow, int dropDownFillStartCell, int dropDownFillEndCell)
{
try
{
//打开Excle模板文件
IWorkbook workbook = null;
using (FileStream fileOne = new FileStream(exportTemplateFilePath, FileMode.Open, FileAccess.Read))
{
workbook = new XSSFWorkbook(fileOne); //获取第一个工作表
}
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(0); //获取第一个sheet
XSSFDataValidationHelper dvHelper = new XSSFDataValidationHelper(sheet);
XSSFDataValidationConstraint dvConstraint = (XSSFDataValidationConstraint)dvHelper
.CreateExplicitListConstraint(dropDowndtSource);
CellRangeAddressList addressList = new CellRangeAddressList(1, dtSource.Rows.Count, dropDownFillStartCell, dropDownFillEndCell);
XSSFDataValidation validation = (XSSFDataValidation)dvHelper.CreateValidation(dvConstraint, addressList);
sheet.AddValidationData(validation);
//格式日期
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####");
//单元格样式
ICellStyle style = workbook.CreateCellStyle();
//style.BorderBottom = BorderStyle.Thin;
//style.BorderLeft = BorderStyle.Thin;
//style.BorderRight = BorderStyle.Thin;
//style.BorderTop = BorderStyle.Thin;
int rowIndex = fillRow;
foreach (DataRow row in dtSource.Rows)
{
#region 填充内容
//sheet.ShiftRows(rowIndex, sheet.LastRowNum, 1, true, false);
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;
}
newCell.CellStyle = style;
}
#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);
}
catch (Exception ex)
{
throw ex;
}
}
private void DgvToXlsx(string fileName, DataGridView dgv)
{
if (dgv.Rows.Count == 0)
{
return;
}
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "Excel 2007格式文件（*.xlsx）|*.xlsx";
sfd.FileName = fileName + DateTime.Now.ToString("yyyyMMddHHmmssms");
if (sfd.ShowDialog() != DialogResult.OK)
{
return;
}
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(fileName);
XSSFRow headRow = (XSSFRow)sheet.CreateRow(0);
for (int i = 0; i < dgv.Columns.Count; i++)
{
XSSFCell headCell = (XSSFCell)headRow.CreateCell(i, CellType.String);
headCell.SetCellValue(dgv.Columns[i].HeaderText);
}
for (int i = 0; i < dgv.Rows.Count; i++)
{
XSSFRow row = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dgv.Columns.Count; j++)
{
XSSFCell cell = (XSSFCell)row.CreateCell(j);
if (dgv.Rows[i].Cells[j].Value == null)
{
cell.SetCellType(CellType.Blank);
}
else
{
if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Int32"))
{
cell.SetCellValue(Convert.ToInt32(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.String"))
{
cell.SetCellValue(dgv.Rows[i].Cells[j].Value.ToString());
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Single"))
{
cell.SetCellValue(Convert.ToSingle(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Double"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.Decimal"))
{
cell.SetCellValue(Convert.ToDouble(dgv.Rows[i].Cells[j].Value));
}
else if (dgv.Rows[i].Cells[j].ValueType.FullName.Contains("System.DateTime"))
{
cell.SetCellValue(Convert.ToDateTime(dgv.Rows[i].Cells[j].Value).ToString("yyyy-MM-dd"));
}
}
}
}
for (int i = 0; i < dgv.Columns.Count; i++)
{
sheet.AutoSizeColumn(i);
}
#region 保存到Excel
using (FileStream fs = new FileStream(sfd.FileName, FileMode.Create))
{
wb.Write(fs);
}
#endregion
MessageBox.Show("恭喜，导出成功");
}
public static bool DataSetToExcel(DataSet ds, string Path)
{
bool result = false;
FileStream fs = null;
XSSFWorkbook workbook = new XSSFWorkbook();
for (int i = 0; i < ds.Tables.Count; i++)
{
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet(ds.Tables[i].TableName);
XSSFCellStyle dateStyle = (XSSFCellStyle)workbook.CreateCellStyle();
XSSFDataFormat format = (XSSFDataFormat)workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
int rowIndex = 0;
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 0)
{
#region 列头及样式
{
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
XSSFCellStyle headStyle = (XSSFCellStyle)workbook.CreateCellStyle();
//headStyle.Alignment = CellHorizontalAlignment.CENTER;
XSSFFont font = (XSSFFont)workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
////自定义表头
string name = ds.Tables[i].TableName;
//for (var j = 0; j < dataList[name].ToArray().Count(); j++)
//{
// headerRow.CreateCell(j).SetCellValue(dataList[name].ToArray()[j]);
// headerRow.GetCell(j).CellStyle = headStyle;
//}
}
#endregion
rowIndex = 1;
}
#endregion
foreach (DataRow row in ds.Tables[i].Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
#region 填充内容
foreach (DataColumn column in ds.Tables[i].Columns)
{
XSSFCell newCell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
string type = row[column].GetType().FullName.ToString();
newCell.SetCellValue(GetValue(row[column].ToString(), type));
}
#endregion
rowIndex++;
}
}
using (fs = File.OpenWrite(Path))
{
workbook.Write(fs);//向打开的这个xls文件中写入数据
result = true;
}
return(result);
}
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);
}
