public CellStyleCache(IWorkbook xlWorkbook)
{
this.xlWorkbook = xlWorkbook;
this.cellStyles = new Dictionary<string, CellStyleWrapper>();
this.cellStyle = xlWorkbook.CreateCellStyle();
this.defaultCellStyle = xlWorkbook.CreateCellStyle();
}
private static int InsertCell(IRow row, int cellIndex, string value, ICellStyle cellStyle)
{
var cell = row.CreateCell(cellIndex);
cell.SetCellValue(value);
cell.CellStyle = cellStyle;
cellIndex++;
return cellIndex;
}
internal static void columnMerge(ISheet sheet, ICellStyle cellStyle, int row, int startColumn, int endColumn)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
}
public ValidationAdder(ISheet fSheet, ICellStyle style_1, ICellStyle style_2,
ICellStyle cellStyle, int validationType)
{
_sheet = fSheet;
_style_1 = style_1;
_style_2 = style_2;
_cellStyle = cellStyle;
_validationType = validationType;
_currentRowIndex = fSheet.PhysicalNumberOfRows;
}
public static void AttachedCellStyle(this IWorkbook owner, string propertyName, ICellStyle value)
{
Dictionary<string, ICellStyle> values;
if (!_table.TryGetValue(owner, out values))
{
values = new Dictionary<string, ICellStyle>();
_table.Add(owner, values);
}
values[propertyName] = value;
}
protected ICell WriteCell(IRow row, int column, Action<ICell> setValue, ICellStyle style = null)
{
var cell = row.CreateCell(column);
setValue(cell);
if (style != null)
{
cell.CellStyle = style;
}
return cell;
}
/// <summary>
/// 解析为Npoi单元格样式
/// </summary>
public ICellStyle Resolve() {
_result = _workbook.CreateCellStyle();
_result.Alignment = GetHorizontalAlignment();
_result.VerticalAlignment = GetVerticalAlignment();
SetBackgroundColor();
SetFillPattern();
SetBorderColor();
SetFont();
_result.WrapText = _style.IsWrap;
return _result;
}
private static void CreateStyle(XSSFWorkbook workbook)
{
//获取字体
_normalFont = GetFontStyle(workbook, "宋体", null, 14);
_scoreFont = GetFontStyle(workbook, "宋体", _scoreColor, 24);
_winRateFont = GetFontStyle(workbook, "宋体", _winRateColor, 14);
//获取样式
_normalLeftCellStyle = GetCellStyle(workbook, _normalFont, null, FillPattern.NoFill, null, HorizontalAlignment.Left, VerticalAlignment.Center);
_normalCenterCellStyle = GetCellStyle(workbook, _normalFont, null, FillPattern.NoFill, null, HorizontalAlignment.Center, VerticalAlignment.Center);
_aquaCellStyle = GetCellStyle(workbook, _normalFont, _bgColor1, FillPattern.SolidForeground, null, HorizontalAlignment.Left, VerticalAlignment.Center);
_greycellStyle = GetCellStyle(workbook, _normalFont, _bgColor2, FillPattern.SolidForeground, null, HorizontalAlignment.Center, VerticalAlignment.Center);
}
public void SetStyle(
int hauteur,
int longeur,
int beginHauteurCadre,
int beginLongeurCadre,
int hauteurCadre,
int longeurCadre,
ICellStyle style,
bool forcedRightBorder)
{
if (hauteur == beginHauteurCadre && longeur == beginLongeurCadre)
{
SetLeftTopCornerStyle(style);
}
else if (hauteur == beginHauteurCadre && longeur == longeurCadre)
{
SetRightTopCornerStyle(style);
}
else if (hauteur == beginHauteurCadre)
{
SetOnlyTopStyle(style);
}
else if (hauteur == hauteurCadre && longeur == beginLongeurCadre)
{
SetBottomLeftCornerStyle(style);
}
else if (hauteur == hauteurCadre && longeur == longeurCadre)
{
SetBottomRightCornerStyle(style);
}
else if (hauteur == hauteurCadre)
{
SetOnlyBottomStyle(style);
}
else if (longeur == beginLongeurCadre)
{
SetOnlyLeftStyle(style);
}
else if (longeur == longeurCadre)
{
SetOnlyRightStyle(style);
}
if (forcedRightBorder)
{
style.BorderRight = _tailleBorder;
}
else
{
style.BorderRight = style.BorderRight;
}
}
public IRow CreateRowCell(ISheet sheet, int rowIdx, int fromColIdx, int toColIdx, ICellStyle cellStyle)
{
IRow row = sheet.CreateRow(rowIdx);
for (int i = fromColIdx; i <= toColIdx; i++)
{
ICell cell = row.CreateCell(i);
if (cellStyle != null)
{
cell.CellStyle = cellStyle;
}
}
return row;
}
public ExcelCellVisualValue(IWorkbook workbook)
{
_orange = GetBaseStyle(workbook);
_orange.FillForegroundColor = IndexedColors.Orange.Index;
_green = GetBaseStyle(workbook);
_green.FillForegroundColor = IndexedColors.Green.Index;
_red = GetBaseStyle(workbook);
_red.FillForegroundColor = IndexedColors.Red.Index;
_neutral = GetBaseStyle(workbook);
}
public void SetUp()
{
wb = new HSSFWorkbook();
ISheet sheet = wb.CreateSheet("new sheet");
style = wb.CreateCellStyle();
IDataFormat fmt = wb.CreateDataFormat();
style.DataFormat=(fmt.GetFormat("hh:mm:ss"));
cell11 = sheet.CreateRow(0).CreateCell(0);
form = new DataFormatter();
Evaluator = new HSSFFormulaEvaluator(wb);
}
/// <summary>
/// Applies the style by copying the fluent style to the NPOI style object,.
/// </summary>
/// <param name="workbook">The workbook.</param>
/// <param name="destination">The destination NPOI style object to apply the FluentStyle to.</param>
public void ApplyStyle(IWorkbook workbook, ICellStyle destination)
{
// If users sets format string this overrides the DataFormat property.
if (Format != null)
{
var dataFormat = workbook.CreateDataFormat();
DataFormat = dataFormat.GetFormat(Format);
}
if (Alignment != null) destination.Alignment = Alignment.Value;
if (BorderBottom != null) destination.BorderBottom = BorderBottom.Value;
if (BorderDiagonal != null) destination.BorderDiagonal = BorderDiagonal.Value;
if (BorderDiagonalColor != null) destination.BorderDiagonalColor = BorderDiagonalColor.Value;
if (BorderDiagonalLineStyle != null) destination.BorderDiagonalLineStyle = BorderDiagonalLineStyle.Value;
if (BorderLeft != null) destination.BorderLeft = BorderLeft.Value;
if (BorderRight != null) destination.BorderRight = BorderRight.Value;
if (BorderTop != null) destination.BorderTop = BorderTop.Value;
if (BottomBorderColor != null) destination.BottomBorderColor = BottomBorderColor.Value;
if (DataFormat != null) destination.DataFormat = DataFormat.Value;
if (FillBackgroundColor != null) destination.FillBackgroundColor = FillBackgroundColor.Value;
if (FillForegroundColor != null) destination.FillForegroundColor = FillForegroundColor.Value;
if (FillPattern != null) destination.FillPattern = FillPattern.Value;
if (Indention != null) destination.Indention = Indention.Value;
if (LeftBorderColor != null) destination.LeftBorderColor = LeftBorderColor.Value;
if (RightBorderColor != null) destination.RightBorderColor = RightBorderColor.Value;
if (Rotation != null) destination.Rotation = Rotation.Value;
if (ShrinkToFit != null) destination.ShrinkToFit = ShrinkToFit.Value;
if (TopBorderColor != null) destination.TopBorderColor = TopBorderColor.Value;
if (VerticalAlignment != null) destination.VerticalAlignment = VerticalAlignment.Value;
if (WrapText != null) destination.WrapText = WrapText.Value;
if (FontIsNeeded)
{
var font = workbook.CreateFont();
if (FontWeight != null) font.Boldweight = (short)FontWeight.Value;
if (Charset != null) font.Charset = Charset.Value;
if (Color != null) font.Color = Color.Value;
if (FontHeight != null) font.FontHeight = FontHeight.Value;
if (FontHeightInPoints != null) font.FontHeightInPoints = FontHeightInPoints.Value;
if (FontName != null) font.FontName = FontName;
if (Italic != null) font.IsItalic = Italic.Value;
if (Strikeout != null) font.IsStrikeout = Strikeout.Value;
if (SuperScript != null) font.TypeOffset = SuperScript.Value;
if (Underline != null) font.Underline = Underline.Value;
destination.SetFont(font);
}
}
public Report(string fileName)
{
this.fileName = fileName;
workbook = new XSSFWorkbook();
listSumRtkGts = new List<int>();
cellStyles = new CellStyles(workbook);
styleDouble = cellStyles.Double();
styleDoubleBold = cellStyles.DoubleBold();
styleTable = cellStyles.Table();
styleCaption = cellStyles.Caption();
styleDoubleGts = cellStyles.DoubleGts();
styleGts = cellStyles.Gts();
styleDoubleRtk = cellStyles.DoubleRtk();
styleRtk = cellStyles.Rtk();
}
public void SetCellStyle(ICell cell, ICellStyle npoiCellStyle)
{
CellStyleWrapper cellStyle = new CellStyleWrapper(npoiCellStyle);
string cellStyleKey = cellStyle.GetKey();
if (cellStyles.ContainsKey(cellStyleKey)) {
// reuse cached styles
CellStyleWrapper cachedCellStyle = cellStyles[cellStyleKey];
cell.CellStyle = cachedCellStyle.CellStyle;
} else {
// If the style does not exist create a new one
ICellStyle newCellStyle = xlWorkbook.CreateCellStyle();
CopyCellStyle(xlWorkbook, npoiCellStyle, newCellStyle);
Add(new CellStyleWrapper(newCellStyle));
cell.CellStyle = newCellStyle;
}
}
public SheetLimits(IWorkbook workbook, string date)
{
this.workbook = workbook;
this.date = date;
sheet = workbook.CreateSheet(date + "-Лимиты");
summedCells = new List<int>();
cellStyles = new CellStyles(workbook);
styleInnerHeading = cellStyles.InnerHeadline();
styleHeading = cellStyles.Heading();
styleHeadingSum = cellStyles.HeadingSum();
styleHeadline = cellStyles.Headline();
styleTable = cellStyles.LimitsTable();
styleDouble = cellStyles.LimitsDouble();
styleDoubleBold = cellStyles.LimitsDoubleBold();
styleCorpColor = cellStyles.CorpColor();
styleLimitsDoubleSum = cellStyles.LimitsDoubleSum();
styleResultHeadline = cellStyles.ResultHeadline();
}
public XlsModel(string sheetName)
{
SheetName = sheetName;
// Create a new workbook and a sheet named "User Accounts"
_workbook = new HSSFWorkbook();
_sheet = _workbook.CreateSheet(SheetName);
var boldFont = _workbook.CreateFont();
boldFont.FontHeightInPoints = 10;
boldFont.FontName = "Arial";
boldFont.Boldweight = (short) FontBoldWeight.Bold;
_dateStyle = _workbook.CreateCellStyle();
_dateStyle.DataFormat = _workbook.CreateDataFormat().GetFormat("dd/mm/yyyy");
_nullStyle = _workbook.CreateCellStyle();
_nullStyle.FillForegroundColor = HSSFColor.Grey40Percent.Index;
_nullStyle.FillPattern = FillPattern.SolidForeground;
_headStyle = _workbook.CreateCellStyle();
_headStyle.SetFont(boldFont);
}
/// <summary>
/// Copy c1 into c2
/// </summary>
public static void CopyCellStyle(IWorkbook wb, ICellStyle c1, ICellStyle c2)
{
c2.Alignment = c1.Alignment;
c2.BorderBottom = c1.BorderBottom;
c2.BorderLeft = c1.BorderLeft;
c2.BorderRight = c1.BorderRight;
c2.BorderTop = c1.BorderTop;
c2.BottomBorderColor = c1.BottomBorderColor;
c2.DataFormat = c1.DataFormat;
c2.FillBackgroundColor = c1.FillBackgroundColor;
c2.FillForegroundColor = c1.FillForegroundColor;
c2.FillPattern = c1.FillPattern;
c2.SetFont(wb.GetFontAt(c1.FontIndex));
// HIDDEN ?
c2.Indention = c1.Indention;
c2.LeftBorderColor = c1.LeftBorderColor;
// LOCKED ?
c2.RightBorderColor = c1.RightBorderColor;
c2.Rotation = c1.Rotation;
c2.TopBorderColor = c1.TopBorderColor;
c2.VerticalAlignment = c1.VerticalAlignment;
c2.WrapText = c1.WrapText;
}
internal ICellStyle GetStyleWithFormat(ICellStyle baseStyle, string dataFormat)
{
ICellStyle newStyle = CreateCellStyle();
newStyle.CloneStyleFrom(baseStyle);
if (StringMethods.IsNullOrWhiteSpace(dataFormat))
return newStyle;
// check if this is a built-in format
var builtinFormatId = GetBuiltIndDataFormat(dataFormat);
if (builtinFormatId != -1)
{
newStyle.DataFormat = builtinFormatId;
}
else
{
// not a built-in format, so create a new one
var newDataFormat = CreateDataFormat();
newStyle.DataFormat = newDataFormat.GetFormat(dataFormat);
}
return newStyle;
}
/**
* Clones all the style information from another
* HSSFCellStyle, onto this one. This
* HSSFCellStyle will then have all the same
* properties as the source, but the two may
* be edited independently.
* Any stylings on this HSSFCellStyle will be lost!
*
* The source HSSFCellStyle could be from another
* HSSFWorkbook if you like. This allows you to
* copy styles from one HSSFWorkbook to another.
*/
public void CloneStyleFrom(ICellStyle source)
{
if (source is HSSFCellStyle)
{
this.CloneStyleFrom((HSSFCellStyle)source);
}
else
{
throw new ArgumentException("Can only clone from one HSSFCellStyle to another, not between HSSFCellStyle and XSSFCellStyle");
}
}
/**
* Returns a map containing the format properties of the given cell style.
*
* @param style cell style
* @return map of format properties (String -> Object)
* @see #setFormatProperties(org.apache.poi.ss.usermodel.CellStyle, org.apache.poi.ss.usermodel.Workbook, java.util.Map)
*/
private static Dictionary<String, Object> GetFormatProperties(ICellStyle style)
{
Dictionary<String, Object> properties = new Dictionary<String, Object>();
PutShort(properties, ALIGNMENT, (short)style.Alignment);
PutShort(properties, BORDER_BOTTOM, (short)style.BorderBottom);
PutShort(properties, BORDER_LEFT, (short)style.BorderLeft);
PutShort(properties, BORDER_RIGHT, (short)style.BorderRight);
PutShort(properties, BORDER_TOP, (short)style.BorderTop);
PutShort(properties, BOTTOM_BORDER_COLOR, style.BottomBorderColor);
PutShort(properties, DATA_FORMAT, style.DataFormat);
PutShort(properties, FILL_BACKGROUND_COLOR, style.FillBackgroundColor);
PutShort(properties, FILL_FOREGROUND_COLOR, style.FillForegroundColor);
PutShort(properties, FILL_PATTERN, (short)style.FillPattern);
PutShort(properties, FONT, style.FontIndex);
PutBoolean(properties, HIDDEN, style.IsHidden);
PutShort(properties, INDENTION, style.Indention);
PutShort(properties, LEFT_BORDER_COLOR, style.LeftBorderColor);
PutBoolean(properties, LOCKED, style.IsLocked);
PutShort(properties, RIGHT_BORDER_COLOR, style.RightBorderColor);
PutShort(properties, ROTATION, style.Rotation);
PutShort(properties, TOP_BORDER_COLOR, style.TopBorderColor);
PutShort(properties, VERTICAL_ALIGNMENT, (short)style.VerticalAlignment);
PutBoolean(properties, WRAP_TEXT, style.WrapText);
return properties;
}
/// <summary>
/// 数据验证
/// </summary>
/// <param name="value"></param>
/// <param name="validator"></param>
private bool Validate(string fileName, SHFYDataValidator validator, Hashtable specialParameters, int needAddedColumn, int lastValueColumnIndex)
{
bool result = true;
TreeValue root = InitTreeNode();
Hashtable treeDir = GetTreeDir(root);
Hashtable columnHeader = new Hashtable();
Hashtable startIndex = new Hashtable();
string destFile = string.Format("{0}\\{1}_{2}.xlsx", Path.GetDirectoryName(fileName), Path.GetFileNameWithoutExtension(fileName), DateTime.Now.ToString("MMddHHmmss"));
File.Copy(fileName, destFile);
XSSFWorkbook destWorkbook = null;
using (FileStream destStream = new FileStream(destFile, FileMode.Open, FileAccess.Read))
{
destWorkbook = new XSSFWorkbook(destStream);
}
ISheet destSheet = destWorkbook.GetSheetAt(0);
InsertColumn(lastValueColumnIndex, needAddedColumn, destSheet);
IEnumerator rowEnumerator = destSheet.GetRowEnumerator();
// 验证失败单元格格式
ICellStyle abnormalStyle = CreateAbnormalStyle(destWorkbook);
// 验证失败单元格同节点格式
ICellStyle normalStyle = CreateNormalStyle(destWorkbook);
if (rowEnumerator.MoveNext())
{
IRow destRow = destSheet.GetRow(0);
for (int i = 0; i < needAddedColumn; i++)
{
ICell cell = destRow.GetCell(lastValueColumnIndex + i + 1);
cell.SetCellValue(GetCellValue(destRow.GetCell(lastValueColumnIndex)));
CopyCell(destRow.GetCell(lastValueColumnIndex), cell, destWorkbook);
}
for (int columnIndex = destRow.FirstCellNum; columnIndex < destRow.LastCellNum - 1 && columnIndex <= lastValueColumnIndex + needAddedColumn; columnIndex++)
{
ICell cell = destRow.GetCell(columnIndex);
columnHeader.Add(cell.ColumnIndex, cell == null ? "" : GetCellValue(cell));
}
}
int lastDirIndex = -1, appendColumn = lastValueColumnIndex, currentBufferIndex = 0;
string key = "", value = "";
string[] buffer = null;
List <int> columnIndexes = new List <int>();
while (rowEnumerator.MoveNext())
{
IRow destRow = rowEnumerator.Current as IRow;
IEnumerator cellEnumerator = destRow.GetEnumerator();
for (int columnIndex = destRow.FirstCellNum; columnIndex < destRow.LastCellNum - 1 && columnIndex <= lastValueColumnIndex + needAddedColumn; columnIndex++)
{
ICell destCell = destRow.GetCell(columnIndex);
if (destCell == null)
{
continue;
}
switch (columnHeader[columnIndex] as string)
{
case "序号":
key = GetCellValue(destCell);
break;
case "参数项的值":
value = GetCellValue(destCell);
int index = 0;
bool flag = false;
bool isTitle = false;
if (key != null && specialParameters.ContainsKey(key))
{
if (value.IndexOf(',') >= 0)
{
buffer = value.Split(',');
}
else if (value.IndexOf('，') >= 0)
{
buffer = value.Split('，');
}
else if (value.IndexOf(';') >= 0)
{
buffer = value.Split(';');
}
//if (buffer == null)
// buffer = value.Split(',');
if (buffer != null && currentBufferIndex < buffer.Length)
{
columnIndexes.Add(destCell.ColumnIndex);
destCell.SetCellValue(buffer[currentBufferIndex]);
currentBufferIndex++;
}
}
flag = validator.IsValid(key, GetCellValue(destCell));
if (flag == false || validator.IsSpecial(key, GetCellValue(destCell)))
{
if (startIndex.Contains(destCell.ColumnIndex))
{
index = (int)startIndex[destCell.ColumnIndex];
isTitle = false;
}
else
{
index = lastDirIndex;
isTitle = true;
}
SetAbnormalCell(index, destCell.RowIndex, destCell.ColumnIndex, destSheet, abnormalStyle, normalStyle, isTitle);
startIndex[destCell.ColumnIndex] = destCell.RowIndex;
}
result = result && flag;
break;
case "参数项名称":
buffer = null;
string str = GetCellValue(destCell);
Match match = Default3CDataProvider.paraNameMatcher.Match(str);
if (match.Success && match.Groups["name"].Success)
{
str = match.Groups["name"].Value.Trim();
}
else
{
str = str.Trim();
}
if (treeDir.ContainsKey(str))
{
if (columnIndexes.Count > 1)
{
CopyCell(lastDirIndex, destCell.RowIndex, columnIndexes, destSheet);
}
columnIndexes.Clear();
lastDirIndex = destCell.RowIndex;
foreach (DictionaryEntry entry in startIndex)
{
SetAbnormalCell((int)entry.Value, lastDirIndex - 1, (int)entry.Key, destSheet, normalStyle, normalStyle, false);
}
startIndex.Clear();
}
currentBufferIndex = 0;
break;
}
}
}
File.Delete(destFile);
destFile = string.Format("{0}\\{3}_{1}_{2}.xlsx", Path.GetDirectoryName(fileName), Path.GetFileNameWithoutExtension(fileName), DateTime.Now.ToString("MMddHHmmss"), result ? "已验证" : "验证失败");
using (FileStream fileStream = new FileStream(destFile, FileMode.OpenOrCreate, FileAccess.Write))
{
destWorkbook.Write(fileStream);
}
return(result);
}
public static int SaveToExcel(DataTable dgv, string title, string filePath)
{
if (dgv == null || (dgv.Rows.Count == 0 && dgv.Columns.Count == 0))
{
return(1);
}
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet = hssfworkbook.CreateSheet("Sheet1");
hssfworkbook.CreateSheet("Sheet2");
hssfworkbook.CreateSheet("Sheet3");
//Title
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
cell.SetCellValue(title);
ICellStyle style = hssfworkbook.CreateCellStyle();
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
IFont font = hssfworkbook.CreateFont();
font.FontHeight = 20 * 20;
style.SetFont(font);
cell.CellStyle = style;
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dgv.Columns.Count - 1));
//Header
int r, c;
row = sheet.CreateRow(1);
for (c = 0; c < dgv.Columns.Count; c++)
{
row.CreateCell(c).SetCellValue("");
}
// content
for (r = 0; r < dgv.Rows.Count; r++)
{
row = sheet.CreateRow(r + 1);
for (c = 0; c < dgv.Columns.Count; c++)
{
cell = row.CreateCell(c);
if (dgv.Columns[c] != null)
{
cell.SetCellValue(dgv.Columns[c].ToString());
}
}
}
r = 0;
FileStream file = null;
try
{
file = new FileStream(filePath, FileMode.Create);
hssfworkbook.Write(file);
}
catch (Exception ex)
{
r = 1;
}
finally
{
if (file != null)
{
file.Close();
}
}
return(r);
}
/// <summary>
/// Method para exportar un datatable a excel
/// </summary>
/// <param name="sourceTable">dataTable</param>
/// <param name="fileName">nombre del archivo excel</param>
/// <remarks></remarks>
public static void ExportDataTableToExcelFormatoCP(DataTable sourceTable, string fileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream memoryStream = new MemoryStream();
HSSFSheet sheet = (HSSFSheet)workbook.CreateSheet("Hoja1");
HSSFRow headerRow = (HSSFRow)sheet.CreateRow(7);
int rowIndex = 8;
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Calidda";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation calidda = PropertySetFactory.CreateSummaryInformation();
calidda.Subject = "CALIDDA";
calidda.Author = "Soporte Aplicaciones";
calidda.ApplicationName = "NOMINACIONES WEB";
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFClientAnchor anchor = new HSSFClientAnchor(100, 255, 800, 0, 0, 0, 1, 6);
anchor.AnchorType = (NPOI.SS.UserModel.AnchorType) 2;
string dirPicture = context.Current.Server.MapPath("~") + "\\Content\\src\\assets\\content\\Images\\logo_publica.png";
HSSFPicture img = (HSSFPicture)patriarch.CreatePicture(anchor, LoadImageExcel(dirPicture, workbook));
IFont fontHeaders = workbook.CreateFont();
var withBlock = fontHeaders;
withBlock.Color = HSSFColor.Black.Index;
withBlock.Boldweight = (short)FontBoldWeight.Bold;
withBlock.FontName = "Arial";
withBlock.FontHeightInPoints = 10;
HSSFColor color = setColor(workbook, Convert.ToByte(116), Convert.ToByte(171), Convert.ToByte(235));
ICellStyle styleHeader = workbook.CreateCellStyle();
var withstyleHeader = styleHeader;
withstyleHeader.SetFont(fontHeaders);
withstyleHeader.Alignment = HorizontalAlignment.Center;
withstyleHeader.BorderRight = BorderStyle.Thin;
withstyleHeader.BorderBottom = BorderStyle.Thin;
withstyleHeader.BorderLeft = BorderStyle.Thin;
withstyleHeader.BorderTop = BorderStyle.Thin;
withstyleHeader.FillForegroundColor = HSSFColor.Blue.Index;
withstyleHeader.FillPattern = FillPattern.SolidForeground;
foreach (DataColumn column in sourceTable.Columns)
{
var withheader = headerRow.CreateCell(column.Ordinal);
withheader.SetCellValue(column.Caption);
withheader.CellStyle = styleHeader;
}
headerRow.HeightInPoints = 15;
ICellStyle styleRows = workbook.CreateCellStyle();
var withStyelRow = styleRows;
withStyelRow.Alignment = HorizontalAlignment.Center;
withStyelRow.BorderRight = BorderStyle.Thin;
withStyelRow.BorderBottom = BorderStyle.Thin;
withStyelRow.BorderLeft = BorderStyle.Thin;
withStyelRow.BorderTop = BorderStyle.Thin;
foreach (DataRow row in sourceTable.Rows)
{
HSSFRow dataRow = (HSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
dataRow.GetCell(column.Ordinal).CellStyle = styleRows;
}
rowIndex += 1;
}
for (int i = 0; i <= headerRow.LastCellNum; i++)
{
sheet.AutoSizeColumn(i);
}
sheet.DisplayGridlines = false;
sheet.FitToPage = false;
ExportToExcelFormatoCamposPrincipales(workbook, fileName);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
public static MemoryStream Export(DataTable dtSource, string strHeaderText, string strSecondText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "NPOI";
workbook.DocumentSummaryInformation = dsi;
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "文件作者信息"; //填加xls文件作者信息
si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
si.Comments = "作者信息"; //填加xls文件作者信息
si.Title = "标题信息"; //填加xls文件标题信息
si.Subject = "主题信息"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
workbook.SummaryInformation = si;
}
#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
int[] arrColWidth = new int[dtSource.Columns.Count];
foreach (DataColumn item in dtSource.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString().Trim()).Length;
}
for (int i = 0; i < dtSource.Rows.Count; i++)
{
for (int j = 0; j < dtSource.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
CreateTile(dtSource, strHeaderText, workbook, sheet, 0);
CreateTile(dtSource, strSecondText, workbook, sheet, 1);
//headerRow.Dispose();
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(2);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
foreach (DataColumn column in dtSource.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
headerRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
//headerRow.Dispose();
}
#endregion
rowIndex = 3;
}
#endregion
#region 填充内容
HSSFRow dataRow = sheet.CreateRow(rowIndex) as HSSFRow;
foreach (DataColumn column in dtSource.Columns)
{
HSSFCell newCell = dataRow.CreateCell(column.Ordinal) as HSSFCell;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
{
double doubV = 0;
if (double.TryParse(drValue, out doubV))
{
newCell.SetCellValue(doubV);
}
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++;
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
public BDiseaseExportExcelUtil(string fileName, int columnSize)
{
//FileStream fs = new FileStream(System.Web.Hosting.HostingEnvironment.MapPath("~/template.xls"), FileMode.Open, FileAccess.ReadWrite);
//workbook = new HSSFWorkbook(fs);
workbook = new HSSFWorkbook();
//fs.Close();
this.fileName = fileName;
//this.tableHeader = tableHeader;
//this.data = data;
this.columnSize = columnSize;
IFont headerFont = workbook.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
headerFont.FontHeightInPoints = 12;
IFont dataFont = workbook.CreateFont();
dataFont.FontHeightInPoints = 12;
headerCenterStyle = workbook.CreateCellStyle();
headerCenterStyle.Alignment = HorizontalAlignment.Center;
headerCenterStyle.SetFont(headerFont);
headerLeftStyle = workbook.CreateCellStyle();
headerLeftStyle.SetFont(headerFont);
headerRightStyle = workbook.CreateCellStyle();
headerRightStyle.Alignment = HorizontalAlignment.Right;
headerRightStyle.SetFont(headerFont);
tableHeaderStyle = workbook.CreateCellStyle();
tableHeaderStyle.WrapText = true;
tableHeaderStyle.Alignment = HorizontalAlignment.Center;
tableHeaderStyle.VerticalAlignment = VerticalAlignment.Center;
tableHeaderStyle.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;
tableHeaderStyle.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
tableHeaderStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
tableHeaderStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
tableHeaderStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
tableHeaderStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
tableHeaderStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableHeaderStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableHeaderStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableHeaderStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableHeaderStyle.SetFont(headerFont);
tableDataStyle = workbook.CreateCellStyle();
tableDataStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
tableDataStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
tableDataStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
tableDataStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
tableDataStyle.BottomBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableDataStyle.LeftBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableDataStyle.RightBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableDataStyle.TopBorderColor = NPOI.HSSF.Util.HSSFColor.Black.Index;
tableDataStyle.SetFont(dataFont);
worksheet = workbook.CreateSheet(fileName);
//指定直式或橫式 true=橫式 false=直式
worksheet.PrintSetup.Landscape = true;
//指定紙張大小 A3=8, A4=9, Letter=1
worksheet.PrintSetup.PaperSize = 9;
//設定邊距置中
worksheet.HorizontallyCenter = true;
//sheet.VerticallyCenter = true;
//設定一頁寬
worksheet.Autobreaks = true;
worksheet.PrintSetup.FitHeight = (short)0;
worksheet.PrintSetup.FitWidth = (short)1;
//Water Print
worksheet.Header.Center = "&50 &K808080 \n\n\n &D &T " + AuthServer.GetLoginUser().UserName;
worksheet.ProtectSheet(AuthServer.GetLoginUser().LoginName);
}
private void PutNamesWithValidations(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[][] names)
{
var rn0 = ws.PhysicalNumberOfRows;
var rn = rn0;
for (var i = 0; i < names.Length; ++i)
{
var rnc = rn++;
var r = ws.CreateRow(rnc);
if (1 < names[i].Length)
{
r.RowStyle = cs3;
var vh = ws.GetDataValidationHelper();
var vl = names[i].Skip(1).ToList();
vl.Sort();
var vd = vh.CreateValidation(
vh.CreateExplicitListConstraint(vl.ToArray()),
new CellRangeAddressList(rnc, rnc, 1, _excelVer.LastColumnIndex)
);
vd.ShowErrorBox = false;
vd.ShowPromptBox = true;
ws.AddValidationData(vd);
}
var c = r.CreateCell(0);
c.SetCellValue(names[i][0]);
if (0 == i)
{
c.CellStyle = cs1;
r.RowStyle = cs1;
}
else
{
c.CellStyle = cs2;
}
}
if (1 < names.Length) ws.GroupRow(rn0 + 1, rn - 1);
//ws.SetRowGroupCollapsed(rn0 + 1, true);
ws.AddMergedRegion(new CellRangeAddress(rn0, rn0, 0, _excelVer.LastColumnIndex));
}
public void Getstockdata()
{
try
{
XmlDocument xml = new XmlDocument();
String exepath = AppDomain.CurrentDomain.BaseDirectory;
xml.Load(exepath + @"config.xml");
XmlNode tokenst = xml.SelectSingleNode("/configuration/token");
string token = tokenst.InnerText;
StreamReader stocklist = new StreamReader("stocklist.txt", Encoding.Default);
while ((stockl = stocklist.ReadLine()) != null)
{
textb1.Dispatcher.BeginInvoke((Action)(() => textb1.Text = "Symbol: " + stockl));
var symbol = stockl;
if (size.Equals("TIME_SERIES_DAILY"))
{
var dailyprices = $"https://www.alphavantage.co/query?function=TIME_SERIES_DAILY&symbol={symbol}&outputsize={period}&apikey={token}&datatype=csv"
.GetStringFromUrl().FromCsv <List <Program.AlphaVantageData> >();
if (format.Equals("tab"))
{
//StreamWriter connect5s = new StreamWriter(@symbol + "_daily_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".txt", false, Encoding.Default);
StreamWriter connect5s = new StreamWriter(@symbol + "_" + size + "_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".txt", false, Encoding.Default);
for (int x = 0; x < dailyprices.Count; x++)
{
connect5s.WriteLine(symbol + "\t" + dailyprices[x].Timestamp + "\t" + dailyprices[x].Volume + "\t" + dailyprices[x].Open + "\t" + dailyprices[x].High + "\t" + dailyprices[x].Low + "\t" + dailyprices[x].Close);
}
connect5s.Close();
}
else if (format.Equals("xls"))
{
HSSFWorkbook wb;
HSSFSheet sh;
wb = new HSSFWorkbook();
// create sheet
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
IDataFormat dataFormatCustom = wb.CreateDataFormat();
ICellStyle style1 = wb.CreateCellStyle();
style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM");
for (int i = 0; i < dailyprices.Count; i++)
{
var r = sh.CreateRow(i);
for (int j = 0; j < 7; j++)
{
IRow row = sh.GetRow(i);
if (j == 0)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(symbol);
}
else if (j == 1)
{
r.CreateCell(j).CellStyle = style1;
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(dailyprices[i].Timestamp);
}
else if (j == 2)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Volume);
}
else if (j == 3)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Open);
}
else if (j == 4)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].High);
}
else if (j == 5)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Low);
}
else if (j == 6)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Close);
}
}
}
//using (var fs = new FileStream(@symbol + "_daily_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xls", FileMode.Create, FileAccess.Write))
using (var fs = new FileStream(@symbol + "_" + size + "_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xls", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
else if (format.Equals("xlsx"))
{
XSSFWorkbook wb;
XSSFSheet sh;
wb = new XSSFWorkbook();
// create sheet
sh = (XSSFSheet)wb.CreateSheet("Sheet1");
IDataFormat dataFormatCustom = wb.CreateDataFormat();
ICellStyle style1 = wb.CreateCellStyle();
style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM");
for (int i = 0; i < dailyprices.Count; i++)
{
var r = sh.CreateRow(i);
for (int j = 0; j < 7; j++)
{
IRow row = sh.GetRow(i);
if (j == 0)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(symbol);
}
else if (j == 1)
{
r.CreateCell(j).CellStyle = style1;
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(dailyprices[i].Timestamp);
}
else if (j == 2)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Volume);
}
else if (j == 3)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Open);
}
else if (j == 4)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].High);
}
else if (j == 5)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Low);
}
else if (j == 6)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Close);
}
}
}
using (var fs = new FileStream(@symbol + "_" + size + "_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xlsx", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
}
else
{
var dailyprices = $"https://www.alphavantage.co/query?function=TIME_SERIES_INTRADAY&symbol={symbol}&outputsize={period}&interval={min}&apikey={token}&datatype=csv"
.GetStringFromUrl().FromCsv <List <Program.AlphaVantageData> >();
if (format.Equals("tab"))
{
//StreamWriter connect5s = new StreamWriter(@symbol + "_intraday_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".txt", false, Encoding.Default);
StreamWriter connect5s = new StreamWriter(@symbol + "_" + size + "_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".txt", false, Encoding.Default);
for (int x = 0; x < dailyprices.Count; x++)
{
connect5s.WriteLine(symbol + "\t" + dailyprices[x].Timestamp + "\t" + dailyprices[x].Volume + "\t" + dailyprices[x].Open + "\t" + dailyprices[x].High + "\t" + dailyprices[x].Low + "\t" + dailyprices[x].Close);
}
connect5s.Close();
}
else if (format.Equals("xls"))
{
HSSFWorkbook wb;
HSSFSheet sh;
wb = new HSSFWorkbook();
// create sheet
sh = (HSSFSheet)wb.CreateSheet("Sheet1");
IDataFormat dataFormatCustom = wb.CreateDataFormat();
ICellStyle style1 = wb.CreateCellStyle();
style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM");
for (int i = 0; i < dailyprices.Count; i++)
{
var r = sh.CreateRow(i);
for (int j = 0; j < 7; j++)
{
IRow row = sh.GetRow(i);
if (j == 0)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(symbol);
}
else if (j == 1)
{
r.CreateCell(j).CellStyle = style1;
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(dailyprices[i].Timestamp);
}
else if (j == 2)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Volume);
}
else if (j == 3)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Open);
}
else if (j == 4)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].High);
}
else if (j == 5)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Low);
}
else if (j == 6)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Close);
}
}
}
//using (var fs = new FileStream(@symbol + "_intraday_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xls", FileMode.Create, FileAccess.Write))
using (var fs = new FileStream(@symbol + "_" + size + "_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xls", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
else if (format.Equals("xlsx"))
{
XSSFWorkbook wb;
XSSFSheet sh;
wb = new XSSFWorkbook();
// create sheet
sh = (XSSFSheet)wb.CreateSheet("Sheet1");
IDataFormat dataFormatCustom = wb.CreateDataFormat();
ICellStyle style1 = wb.CreateCellStyle();
style1.DataFormat = dataFormatCustom.GetFormat("MM/dd/yyyy HH:mm:ss AM/PM");
for (int i = 0; i < dailyprices.Count; i++)
{
var r = sh.CreateRow(i);
for (int j = 0; j < 7; j++)
{
IRow row = sh.GetRow(i);
if (j == 0)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(symbol);
}
else if (j == 1)
{
r.CreateCell(j).CellStyle = style1;
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue(dailyprices[i].Timestamp);
}
else if (j == 2)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Volume);
}
else if (j == 3)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Open);
}
else if (j == 4)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].High);
}
else if (j == 5)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Low);
}
else if (j == 6)
{
r.CreateCell(j);
ICell cell1 = row.GetCell(j, MissingCellPolicy.CREATE_NULL_AS_BLANK);
cell1.SetCellValue((double)dailyprices[i].Close);
}
}
}
//using (var fs = new FileStream(@symbol + "_intraday_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xlsx", FileMode.Create, FileAccess.Write))
using (var fs = new FileStream(@symbol + "_" + size + "_" + min + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(" ", "_").Replace(":", "_") + ".xlsx", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
}
}
stocklist.Close();
}
catch (Exception e)
{
Console.WriteLine($"Generic Exception Handler: {e}");
}
finally
{
}
}
public void CopyRow(HSSFWorkbook workbook, ISheet worksheet, int sourceRowNum, int destinationRowNum)
{
// Get the source / new row
IRow newRow = worksheet.GetRow(destinationRowNum);
IRow sourceRow = worksheet.GetRow(sourceRowNum);
// If the row exist in destination, push down all rows by 1 else create a new row
if (newRow != null)
{
worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
}
else
{
newRow = worksheet.CreateRow(destinationRowNum);
}
// Loop through source columns to add to new row
for (int i = 0; i < sourceRow.LastCellNum; i++)
{
// Grab a copy of the old/new cell
ICell oldCell = sourceRow.GetCell(i);
ICell newCell = newRow.CreateCell(i);
// 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
ICellStyle newCellStyle = workbook.CreateCellStyle();
newCellStyle.CloneStyleFrom(oldCell.CellStyle);
newCell.CellStyle = newCellStyle;
// 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 type
newCell.SetCellType(oldCell.CellType);
// 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.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;
}
}
// If there are are any merged regions in the source row, copy to new row
for (int i = 0; i < worksheet.NumMergedRegions; i++)
{
CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
if (cellRangeAddress.FirstRow == sourceRow.RowNum)
{
CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
(newRow.RowNum +
(cellRangeAddress.FirstRow -
cellRangeAddress.LastRow)),
cellRangeAddress.FirstColumn,
cellRangeAddress.LastColumn);
worksheet.AddMergedRegion(newCellRangeAddress);
}
}
}
static void WriteMeasurement(Measurement measurement, ISheet sheet, int rowIndex, ICellStyle cellDateStyle)
{
var row = sheet.CreateRow(rowIndex);
int i = 0;
row.CreateCell(i).SetCellValue(measurement.WellName);
var cell = row.CreateCell(++i, CellType.Numeric);
cell.SetCellValue(measurement.Date);
cell.CellStyle = cellDateStyle;
row.CreateCell(++i, CellType.Numeric).SetCellValue(measurement.FlnaDepth);
row.CreateCell(++i, CellType.Numeric).SetCellValue(measurement.WaterDepth);
row.CreateCell(++i).SetCellValue(measurement.Comment);
}
static void ExportMeasurements(List <Measurement> measurements, ISheet sheet, ICellStyle cellDateStyle)
{
WriteMeasurementHeader(sheet);
int row = 1;
foreach (var w in measurements)
{
WriteMeasurement(w, sheet, row, cellDateStyle);
row++;
}
}
static void WritePrecipitation(Precipitation precipitation, ISheet sheet, int rowIndex, ICellStyle cellDateStyle)
{
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(precipitation.Date);
var cell = row.CreateCell(0, CellType.Numeric);
cell.SetCellValue(precipitation.Date);
cell.CellStyle = cellDateStyle;
row.CreateCell(1, CellType.Numeric).SetCellValue(precipitation.Millimeters);
}
public PackingListDetailSheet2Generator(XSSFWorkbook wb, XSSFSheet sheet_2, Database databaseInfor, ParkingListParser packingParser)
{
this.wb = wb;
this.sheet_2 = sheet_2;
this.databaseInfor = databaseInfor;
this.packingParser = packingParser;
sheet_2_rowIndex = 0;
sheet_2_headerStyle_1 = wb.CreateCellStyle();
IFont headerbold_1 = wb.CreateFont();
headerbold_1.Boldweight = (short)FontBoldWeight.Bold;
headerbold_1.FontHeight = 13;
sheet_2_headerStyle_1.SetFont(headerbold_1);
sheet_2_headerStyle_1.Alignment = HorizontalAlignment.Center;
sheet_2_headerStyle_1.VerticalAlignment = VerticalAlignment.Center;
sheet_2_headerStyle_1.BorderTop = BorderStyle.Thin;
sheet_2_headerStyle_1.BorderRight = BorderStyle.Thin;
sheet_2_headerStyle_1.BorderBottom = BorderStyle.Thin;
sheet_2_headerStyle_2 = wb.CreateCellStyle();
IFont headerbold_2 = wb.CreateFont();
headerbold_2.Boldweight = (short)FontBoldWeight.Bold;
headerbold_2.FontHeight = 12;
headerbold_2.Underline = FontUnderlineType.Single;
sheet_2_headerStyle_2.SetFont(headerbold_2);
sheet_2_headerStyle_2.Alignment = HorizontalAlignment.Center;
sheet_2_headerStyle_2.VerticalAlignment = VerticalAlignment.Center;
sheet_2_headerStyle_2.BorderTop = BorderStyle.Thin;
sheet_2_headerStyle_2.BorderRight = BorderStyle.Thin;
sheet_2_headerStyle_2.BorderBottom = BorderStyle.Thin;
sheet_2_bottomStyle_1 = wb.CreateCellStyle();
sheet_2_bottomStyle_1.FillForegroundColor = IndexedColors.Aqua.Index;
sheet_2_bottomStyle_1.FillPattern = FillPattern.SolidForeground;
sheet_2_bottomStyle_1.BorderBottom = BorderStyle.Thin;
sheet_2_bottomStyle_1.BorderLeft = BorderStyle.Thin;
sheet_2_bottomStyle_1.BorderRight = BorderStyle.Thin;
IFont boldFont_1 = wb.CreateFont();
boldFont_1.Boldweight = (short)FontBoldWeight.Bold;
boldFont_1.FontHeight = 13;
boldFont_1.Color = IndexedColors.Red.Index;
sheet_2_bottomStyle_1.SetFont(boldFont_1);
sheet_2_bottomStyle_2 = wb.CreateCellStyle();
sheet_2_bottomStyle_2.FillForegroundColor = IndexedColors.Aqua.Index;
sheet_2_bottomStyle_2.FillPattern = FillPattern.SolidForeground;
sheet_2_bottomStyle_2.BorderBottom = BorderStyle.Thin;
sheet_2_bottomStyle_2.BorderLeft = BorderStyle.Thin;
sheet_2_bottomStyle_2.BorderRight = BorderStyle.Thin;
IFont boldFont_2 = wb.CreateFont();
boldFont_2.Boldweight = (short)FontBoldWeight.Bold;
boldFont_2.FontHeight = 13;
boldFont_2.Color = IndexedColors.Black.Index;
sheet_2_bottomStyle_2.SetFont(boldFont_2);
sheet_2_mainStyle_2 = wb.CreateCellStyle();
sheet_2_mainStyle_2.BorderBottom = BorderStyle.Dotted;
sheet_2_mainStyle_2.BorderRight = BorderStyle.Thin;
sheet_2_mergerStyle = wb.CreateCellStyle();
sheet_2_mergerStyle.BorderBottom = BorderStyle.Dotted;
sheet_2_mergerStyle.BorderRight = BorderStyle.Thin;
sheet_2_mergerStyle.VerticalAlignment = VerticalAlignment.Center;
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = IndexedColors.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = IndexedColors.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = IndexedColors.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
private static void SetDefaultCellStyle(this IWorkbook wb, ICellStyle cs)
{
wb.GetDefaultCellStyle().CloneStyleFrom(cs);
}
public void SetRowGrey(int row)
{
var theRow = Worksheet.GetRow(row);
foreach (var cell in theRow.Cells)
{
if (GreyCellStyleCache == null)
{
var newStyle = Workbook.CreateCellStyle();
newStyle.CloneStyleFrom(cell.CellStyle);
//newStyle.FillBackgroundColor = colorIndex;
newStyle.FillPattern = FillPattern.Diamonds;
GreyCellStyleCache = newStyle;
}
cell.CellStyle = GreyCellStyleCache;
}
}
static void ExportPrecipitations(List <Precipitation> precipitations, ISheet sheet, ICellStyle cellDateStyle)
{
WritePrecipitationHeader(sheet);
int row = 1;
foreach (var w in precipitations)
{
WritePrecipitation(w, sheet, row, cellDateStyle);
row++;
}
}
private void setCellStyle(IRow irow, int cellno, ICellStyle ics)
{
for (int i = 0; i < cellno; i++)
{
irow.GetCell(i).CellStyle = ics;
}
}
protected void Button3_Click(object sender, EventArgs e)
{
// 导出所有
//导出
HSSFWorkbook hssfworkbook = new HSSFWorkbook();
ISheet sheet1 = hssfworkbook.CreateSheet("Sheet1");
// 获取单元格 并设置样式
ICellStyle styleCell = hssfworkbook.CreateCellStyle();
//居中
styleCell.Alignment = HorizontalAlignment.Center;
//垂直居中
styleCell.VerticalAlignment = VerticalAlignment.Center;
//设置字体
IFont fontColorRed = hssfworkbook.CreateFont();
fontColorRed.FontHeight = 17 * 17;
styleCell.SetFont(fontColorRed);
styleCell.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin; //下边框为细线边框
styleCell.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin; //左边框
styleCell.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin; //上边框
styleCell.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin; //右边框
ICellStyle styleCell1 = hssfworkbook.CreateCellStyle();
styleCell1.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //居中
styleCell1.VerticalAlignment = VerticalAlignment.Center; //垂直居中
IFont fontColorRed1 = hssfworkbook.CreateFont();
fontColorRed1.FontHeight = 20 * 20;
styleCell1.SetFont(fontColorRed1);
int row = 0;
DataTable dt;
IRow row1; ICell cell;
for (int ii = 0; ii < DropDownList1.Items.Count; ++ii)
{
for (int jj = 0; jj < DropDownList2.Items.Count; ++jj)
{
dt = getDtFromDB(DropDownList1.Items[ii].Value.ToString(), DropDownList2.Items[jj].Value.ToString());
row1 = sheet1.CreateRow(row++);
cell = row1.CreateCell(0);
cell.CellStyle = styleCell1;
cell.SetCellValue(DropDownList2.Items[jj].Value.ToString() + "级" + DropDownList1.Items[ii].Value.ToString());
sheet1.AddMergedRegion(new CellRangeAddress(row - 1, row - 1, 0, 2));// 合并单元格
int k = 0;
for (int i = 0; i < 5; i++)
{
int tt = row;
for (int j = 0; j < 4; j++)
{
k = i * 4 + j; // 当前索引
row1 = sheet1.CreateRow(row++);
cell = row1.CreateCell(0);
cell.CellStyle = styleCell;
cell.SetCellValue(dt.Rows[k][0].ToString());
cell = row1.CreateCell(1);
cell.CellStyle = styleCell;
cell.SetCellValue(dt.Rows[k][1].ToString());
cell = row1.CreateCell(2);
cell.CellStyle = styleCell;
cell.SetCellValue(dt.Rows[k][2].ToString());
}
sheet1.AddMergedRegion(new CellRangeAddress(tt, tt + 3, 0, 0));
}
++k;
row1 = sheet1.CreateRow(row++);
cell = row1.CreateCell(0);
cell.SetCellValue(dt.Rows[k][0].ToString());
cell.CellStyle = styleCell;
cell = row1.CreateCell(1); cell.CellStyle = styleCell; cell = row1.CreateCell(2); cell.CellStyle = styleCell;
++k;
row1 = sheet1.CreateRow(row++);
cell = row1.CreateCell(0);
cell.SetCellValue(dt.Rows[k][0].ToString());
cell.CellStyle = styleCell;
cell = row1.CreateCell(1); cell.CellStyle = styleCell; cell = row1.CreateCell(2); cell.CellStyle = styleCell;
row1 = sheet1.CreateRow(row++);
row1 = sheet1.CreateRow(row++);
}
}
// 设置行宽度
sheet1.SetColumnWidth(0, 20 * 256); // 设置第二列的宽度
sheet1.SetColumnWidth(1, 20 * 256); // 设置第二列的宽度
sheet1.SetColumnWidth(2, 100 * 256); // 设置第二列的宽度
// 输出Excel
string filename = "coursetable_all.xls";
var context = HttpContext.Current;
context.Response.ContentType = "application/vnd.ms-excel";
context.Response.AddHeader("Content-Disposition", string.Format("attachment;filename={0}", context.Server.UrlEncode(filename)));
context.Response.Clear();
MemoryStream file = new MemoryStream();
hssfworkbook.Write(file);
context.Response.BinaryWrite(file.GetBuffer());
context.Response.End();
}
private byte[] ReportGenerationData(DepositDto depositDto)
{
string path = Path.ChangeExtension(Path.GetTempFileName(), Guid.NewGuid().ToString()); // Создает на диске временный пустой файл с уникальным именем и возвращает полный путь этого файла.
using (FileStream output = new FileStream(path, FileMode.Create, FileAccess.Write))
{
IWorkbook workbook = new HSSFWorkbook(); //Рабочая книга Excel
ISheet sheet = workbook.CreateSheet("Депозит"); // лист exsel
//Font heading // создание шрифта используем 12 шрифт для заголовка
NPOI.SS.UserModel.IFont fontHeading = workbook.CreateFont();
fontHeading.FontHeightInPoints = 12;
fontHeading.Boldweight = (short)FontBoldWeight.Bold;
// имена шрифтов 10 шрифт
NPOI.SS.UserModel.IFont fontNames = workbook.CreateFont();
fontNames.FontHeightInPoints = 10;
fontNames.Boldweight = (short)FontBoldWeight.Bold;
NPOI.SS.UserModel.IFont fontStorageName = workbook.CreateFont();
fontStorageName.FontHeightInPoints = 11;
fontStorageName.Boldweight = (short)FontBoldWeight.Bold;
ICellStyle borderedStyle = workbook.CreateCellStyle();
borderedStyle.BorderBottom = BorderStyle.Thin;
borderedStyle.BorderLeft = BorderStyle.Thin;
borderedStyle.BorderRight = BorderStyle.Thin;
borderedStyle.BorderTop = BorderStyle.Thin;
ICellStyle borderedBottomStyle = workbook.CreateCellStyle();
borderedBottomStyle.BorderBottom = BorderStyle.Thin;
////////heading style
ICellStyle styleHeading = workbook.CreateCellStyle();
styleHeading.SetFont(fontHeading); // задаем заголовку 12 шрифт
styleHeading.Alignment = HorizontalAlignment.Center; // расположение центр
//styleHeading.BorderBottom = BorderStyle.Thin;
ICellStyle styleNames = workbook.CreateCellStyle();
styleNames.CloneStyleFrom(borderedStyle);
styleNames.SetFont(fontNames);
styleNames.Alignment = HorizontalAlignment.Left;
styleNames.VerticalAlignment = VerticalAlignment.Center;
ICellStyle styleNameContractor = workbook.CreateCellStyle();
styleNameContractor.CloneStyleFrom(borderedStyle);
styleNameContractor.SetFont(fontNames);
styleNameContractor.Alignment = HorizontalAlignment.Left;
styleNameContractor.VerticalAlignment = VerticalAlignment.Center;
styleNameContractor.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Grey25Percent.Index;
styleNameContractor.FillPattern = FillPattern.SolidForeground;
ICellStyle styleStorageName = workbook.CreateCellStyle();
styleStorageName.CloneStyleFrom(borderedStyle);
styleStorageName.SetFont(fontStorageName);
styleStorageName.Alignment = HorizontalAlignment.Center;
styleStorageName.VerticalAlignment = VerticalAlignment.Center;
styleStorageName.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.LightYellow.Index;
styleStorageName.FillPattern = FillPattern.SolidForeground;
ICellStyle hederTableStyle = workbook.CreateCellStyle();
hederTableStyle.CloneStyleFrom(borderedStyle);
hederTableStyle.Alignment = HorizontalAlignment.Center;
hederTableStyle.SetFont(fontNames);
hederTableStyle.VerticalAlignment = VerticalAlignment.Center;
hederTableStyle.WrapText = true;
ICellStyle totalNameStyle = workbook.CreateCellStyle();
totalNameStyle.SetFont(fontNames);
totalNameStyle.Alignment = HorizontalAlignment.Right;
totalNameStyle.VerticalAlignment = VerticalAlignment.Center;
ICellStyle leftNameStyle = workbook.CreateCellStyle();
leftNameStyle.CloneStyleFrom(totalNameStyle);
leftNameStyle.Alignment = HorizontalAlignment.Left;
ICellStyle rightBorderedStyle = workbook.CreateCellStyle();
rightBorderedStyle.CloneStyleFrom(borderedStyle);
totalNameStyle.SetFont(fontNames);
rightBorderedStyle.Alignment = HorizontalAlignment.Right;
//////DegitalStyle
ICellStyle digitalStyle = workbook.CreateCellStyle();
digitalStyle.CloneStyleFrom(borderedStyle);
digitalStyle.VerticalAlignment = VerticalAlignment.Top;
digitalStyle.WrapText = true;
// digitalStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("#,##0");
//dataTableByCenter
ICellStyle dataTableByCenter = workbook.CreateCellStyle();
dataTableByCenter.CloneStyleFrom(digitalStyle);
dataTableByCenter.Alignment = HorizontalAlignment.Center;
dataTableByCenter.VerticalAlignment = VerticalAlignment.Center;
// default settings of sheet
sheet.DefaultRowHeight = 300;
short rowHeightStorage = 400;
short rowHeightCell = 300;
//print settings
sheet.RepeatingRows = CellRangeAddress.ValueOf("$3:$4");
sheet.Footer.Center = "&P";
sheet.PrintSetup.FitWidth = 1;
sheet.PrintSetup.FitHeight = 0;
sheet.FitToPage = true;
sheet.PrintSetup.PaperSize = (short)PaperSize.A4_EXTRA;
sheet.PrintSetup.Landscape = false;
sheet.SetMargin(MarginType.TopMargin, 0.5);
sheet.SetMargin(MarginType.BottomMargin, 0.8);
sheet.SetMargin(MarginType.LeftMargin, 1);
sheet.SetMargin(MarginType.RightMargin, 0.2);
// найдем базовый депозит
Transaction deposit = _dalFactory.DbContext.Transactions.FirstOrDefault(f => f.Id == depositDto.AccountId);
Transaction deposit2;
if (deposit?.status != null)
{
deposit2 = deposit;
deposit = _dalFactory.DbContext.Transactions.Where(w => w.status == null).FirstOrDefault(f => f.Contract == deposit2.Contract);
}
else
{
deposit2 = _dalFactory.DbContext.Transactions.Where(w => w.status != null).FirstOrDefault(f => f.Contract == deposit.Contract && f.dateUpdate == null);
}
List <Transaction> listOperation = _dalFactory.DbContext.Transactions
.Where(w => w.Contract == deposit2.Contract && w.status != null).OrderBy(o => o.dateUpdate).ToList();
/////////////////Rows
int indexRow = 0;
sheet.CreateRow(indexRow).Height = 300;
int numberOfColumn = 3;
//формируем заголовок
sheet.GetRow(indexRow)
.CreateCell(0)
.SetCellValue(string.Format("Операции по депозиту счет: ".ToUpper() + deposit?.Account));
sheet.GetRow(indexRow).GetCell(0).CellStyle = styleHeading;
sheet.AddMergedRegion(new CellRangeAddress(indexRow, indexRow, 0, numberOfColumn));
DateTime dataNav = DateTime.Now;
decimal summa = deposit.Balance + deposit2.Balance;
indexRow++; indexRow++;
sheet.CreateRow(indexRow).Height = 300;
//формируем заголовок
sheet.GetRow(indexRow)
.CreateCell(0)
.SetCellValue(string.Format("По состоянию на " + dataNav.ToString("dd/MM/yyyy") + " Сумма вклада равна: " + summa));
sheet.GetRow(indexRow).GetCell(0).CellStyle = styleHeading;
sheet.AddMergedRegion(new CellRangeAddress(indexRow, indexRow, 0, numberOfColumn));
indexRow++; indexRow++;
sheet.CreateRow(indexRow).Height = 300;
sheet.GetRow(indexRow)
.CreateCell(0)
.SetCellValue(string.Format("Депозит открыт : " + deposit?.DateBegin.ToString("dd/MM/yyyy")));
sheet.GetRow(indexRow).GetCell(0).CellStyle = styleHeading;
sheet.AddMergedRegion(new CellRangeAddress(indexRow, indexRow, 0, numberOfColumn));
indexRow++; indexRow++;
sheet.CreateRow(indexRow).Height = 300;
sheet.GetRow(indexRow)
.CreateCell(0)
.SetCellValue(string.Format("Операции по депозиту: "));
sheet.GetRow(indexRow).GetCell(0).CellStyle = styleHeading;
sheet.AddMergedRegion(new CellRangeAddress(indexRow, indexRow, 0, numberOfColumn));
indexRow++;
sheet.CreateRow(indexRow).Height = 700;
for (int i = 0; i < numberOfColumn + 1; i++)
{
sheet.GetRow(indexRow).CreateCell(i).CellStyle = hederTableStyle;
}
sheet.GetRow(indexRow).GetCell(0).SetCellValue("№");
sheet.GetRow(indexRow).GetCell(1).SetCellValue("Сумма дебет");
sheet.GetRow(indexRow).GetCell(2).SetCellValue("Дата");
sheet.GetRow(indexRow).GetCell(3).SetCellValue("Описание");
////width columns
sheet.SetColumnWidth(0, 3000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 6000);
sheet.SetColumnWidth(3, 9000);
var count = 0;
foreach (var operation in listOperation)
{
if (operation.Balance == 0)
{
continue;
}
DateTime date;
if (operation.dateUpdate != null)
{
date = (DateTime)operation.dateUpdate;
date = date.AddMonths(-1);
}
else
{
date = operation.DateCreat.AddMonths(-1);
}
indexRow++;
sheet.CreateRow(indexRow).Height = rowHeightCell;
if (count == 0)
{
for (int i = 0; i < numberOfColumn + 1; i++)
{
sheet.GetRow(indexRow).CreateCell(i).CellStyle = digitalStyle;
}
sheet.GetRow(indexRow).GetCell(0).SetCellValue(count);
sheet.GetRow(indexRow).GetCell(1).SetCellValue((double)deposit.Balance);
sheet.GetRow(indexRow).GetCell(2).SetCellValue(" " + deposit.DateBegin.ToString("dd/MM/yyyy"));
sheet.GetRow(indexRow).GetCell(3).SetCellValue(" Открытие вклада");
count++;
indexRow++;
}
sheet.CreateRow(indexRow).Height = rowHeightCell;
for (int i = 0; i < numberOfColumn + 1; i++)
{
sheet.GetRow(indexRow).CreateCell(i).CellStyle = digitalStyle;
}
sheet.GetRow(indexRow).GetCell(0).SetCellValue(count);
sheet.GetRow(indexRow).GetCell(1).SetCellValue((double)operation.Credit);
sheet.GetRow(indexRow).GetCell(2).SetCellValue(" " + date.ToString("dd/MM/yyyy"));
sheet.GetRow(indexRow).GetCell(3).SetCellValue(" Начисление процентов");
count++;
}
workbook.Write(output);
output.Close();
}
var result = File.ReadAllBytes(path);
if (File.Exists(path))
{
File.Delete(path);
}
return(result);
}
private static void SetAbnormalCell(int startRowIndex, int endRowIndex, int columnIndex, ISheet sheet, ICellStyle abnormalStyle, ICellStyle normalStyle, bool isTitle)
{
if (isTitle)
{
IRow row = sheet.GetRow(startRowIndex);
if (row != null)
{
ICell cell = row.GetCell(columnIndex);
if (cell != null)
{
cell.CellStyle = normalStyle;
}
}
}
for (int index = startRowIndex + 1; index <= endRowIndex; index++)
{
IRow row = sheet.GetRow(index);
if (row != null)
{
ICell cell = row.GetCell(columnIndex);
if (cell != null)
{
cell.CellStyle = (index == endRowIndex ? abnormalStyle : normalStyle);
}
}
}
}
static void ExportFlnaAnalyses(List <FlnaAnalysis> flnaAnalyses, ISheet sheet, ICellStyle cellDateStyle)
{
WriteFlnaAnalysisHeader(sheet);
int row = 1;
foreach (var w in flnaAnalyses)
{
WriteFlnaAnalysis(w, sheet, row, cellDateStyle);
row++;
}
}
public static string Make(DAL.MatchResult entity)
{
string err = string.Empty;
try
{
string pathmy = @"D:\SheBaoHeXiao\App";
HSSFWorkbook _book = new HSSFWorkbook();
string xlsPath = pathmy + entity.BaseFullPath;
FileStream file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
IWorkbook workbook = WorkbookFactory.Create(file);
ISheet sheet = workbook.GetSheetAt(0);
ICell cell;
//获取设置的规则
var detail = new MatchDetailBLL().GetByRefRuleId(entity.RuleId);
var matchs = detail.Where(w => w.BaseMatch == "匹配项设定").ToList();
//读取excel，第一个，基础文件
List <StandardMatch> listBase = new List <StandardMatch>();
StandardFirst standardBase = null;
//循环excel的行数
for (int i = 2; i <= sheet.LastRowNum; i++)
{
standardBase = new StandardFirst();
//标准模板
foreach (var item in detail)
{
int lie = (int)item.BaseExcel;
standardBase.list.Add(lie, new CalculateResult());
standardBase.Row = i;
cell = sheet.GetRow(i).GetCell(lie - 1);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Unknown:
break;
case CellType.Numeric:
var formatCode = cell.CellStyle.GetDataFormatString();
if (formatCode.EndsWith("%"))
{
standardBase[lie].Value += string.Format("{0:" + formatCode + "}", cell.NumericCellValue); //得到5.88%
standardBase[lie].Percent = formatCode;
}
else
{
standardBase[lie].Value += cell.NumericCellValue;
}
break;
case CellType.String:
standardBase[lie].Value += cell.StringCellValue;
break;
case CellType.Formula:
standardBase[lie].Value += cell.NumericCellValue;
break;
case CellType.Blank:
break;
case CellType.Boolean:
break;
case CellType.Error:
break;
default:
break;
}
}
else
{
err = "没有这一列";
}
}
//进行计算
standardBase.Calculate(matchs);
listBase.Add(standardBase);
}
//读取excel，第2个，对比文件
xlsPath = pathmy + entity.GoldTempFullPath;
file = new FileStream(xlsPath, FileMode.Open, FileAccess.Read);
workbook = WorkbookFactory.Create(file);
var sheet2 = workbook.GetSheetAt(0);
List <StandardMatch> listMatch = new List <StandardMatch>();
StandardSecond standardMatch = null;
//循环excel的行数
for (int i = 2; i <= sheet2.LastRowNum; i++)
{
standardMatch = new StandardSecond();
//标准模板
foreach (var item in detail)
{
int lie = (int)item.MatchExcel;
standardMatch.Row = i;
standardMatch.list.Add(lie, new CalculateResult());
cell = sheet2.GetRow(i).GetCell(lie - 1);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Unknown:
break;
case CellType.Numeric:
var formatCode = cell.CellStyle.GetDataFormatString();
if (formatCode.EndsWith("%"))
{
standardMatch[lie].Value += string.Format("{0:" + formatCode + "}", cell.NumericCellValue); //得到5.88%
standardMatch[lie].Percent = formatCode;
}
else
{
standardMatch[lie].Value += cell.NumericCellValue;
}
break;
case CellType.String:
standardMatch[lie].Value += cell.StringCellValue;
break;
case CellType.Formula:
standardMatch[lie].Value += cell.NumericCellValue;
break;
case CellType.Blank:
break;
case CellType.Boolean:
break;
case CellType.Error:
break;
default:
break;
}
}
else
{
err = "没有这一列";
}
}
//进行计算
standardMatch.Calculate(matchs);
listMatch.Add(standardMatch);
}
// 基础集合
var dataBase = from m in listBase select m.Condition;
var dataMatch = from m in listMatch select m.Condition;
//查询结果集
List <StandardMatch> manyBase = new List <StandardMatch>();//相同匹配项存在多条数据，基础文件
var conditions = (from n in dataBase
group n by n into g
where g.Count() >= 2
select g.Key);
foreach (var item in conditions)
{
var data = from m in listBase where m.Condition == item select m;
foreach (var it in data)
{
manyBase.Add(it);
}
}
List <StandardMatch> manyMatch = new List <StandardMatch>();//相同匹配项存在多条数据，对比文件
conditions = (from n in dataMatch
group n by n into g
where g.Count() >= 2
select g.Key);
foreach (var item in conditions)
{
var data = from m in listMatch where m.Condition == item select m;
foreach (var it in data)
{
manyMatch.Add(it);
}
}
List <StandardMatch> onlyBase = new List <StandardMatch>(); //仅基础文件存在数据
conditions = dataBase.Except(dataMatch); //差集,
foreach (var item in conditions)
{
var data = from m in listBase where m.Condition == item select m;
foreach (var it in data)
{
onlyBase.Add(it);
}
}
List <StandardMatch> onlyMatch = new List <StandardMatch>(); //仅对比文件存在数据
conditions = dataMatch.Except(dataBase); //差集,
foreach (var item in conditions)
{
var data = from m in listMatch where m.Condition == item select m;
foreach (var it in data)
{
onlyMatch.Add(it);
}
}
var purBase = listBase.Except(manyBase).Except(onlyBase);
var purMatch = listMatch.Except(manyMatch).Except(onlyMatch);
List <StandardMatchBaseTogether> newSame = new List <StandardMatchBaseTogether>(); //完全一致数据
List <StandardMatchBaseTogether> newDiffrent = new List <StandardMatchBaseTogether>(); //存在差异项目数据
var goldExcel = detail.Where(w => w.BaseMatch == "对比项设定");
foreach (var item in purBase)
{
var match = (from m in purMatch where m.Condition == item.Condition select m).First();
StandardMatchBaseTogether bm = new BLL.StandardMatchBaseTogether();
bm.Base = item;
bm.Match = match;
if (IsSame(goldExcel, item, match))
{
newSame.Add(bm);
}
else
{
newDiffrent.Add(bm);
}
}
//写入excel
string xlsxPath = pathmy + @"\up\standard\result.xls";
FileStream fileStandard = new FileStream(xlsxPath, FileMode.Open, FileAccess.Read);
IWorkbook workbookStandard = WorkbookFactory.Create(fileStandard);
ISheet sheetfileStandard = workbookStandard.GetSheetAt(0);
for (int i = 0; i < 2; i++)//复制表头
{
CopyRow(workbookStandard, sheetfileStandard, sheet, i, i);
}
for (int i = 0; i < onlyBase.Count; i++)
{
CopyRow(workbookStandard, sheetfileStandard, sheet, onlyBase[i].Row, i + 2);
}
ICellStyle style = null;//红色单元格
ISheet sheetfileStandard1 = workbookStandard.GetSheetAt(1);
for (int i = 0; i < 2; i++)//复制表头
{
CopyRow(workbookStandard, sheetfileStandard1, sheet2, i, i);
}
for (int i = 0; i < onlyMatch.Count; i++)
{
CopyRow(workbookStandard, sheetfileStandard1, sheet2, onlyMatch[i].Row, i + 2);
}
ISheet sheetfileStandard2 = workbookStandard.GetSheetAt(2);
for (int i = 0; i < newDiffrent.Count; i++)
{
var dataRow = sheetfileStandard2.CreateRow(i + 2);
if (null != (newDiffrent[i]))
{
int j = 0;
foreach (var item in newDiffrent[i].Base.list.OrderBy(o => o.Key))
{
j++;
var cellStandard = dataRow.CreateCell(j);
//红色单元格
style = workbookStandard.CreateCellStyle();
if (item.Value.Red || string.IsNullOrWhiteSpace(item.Value.Value))
{
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
cellStandard.CellStyle = style;
}
if ((!string.IsNullOrWhiteSpace(item.Value.Value)) && item.Value.Value.Contains('%'))
{
cellStandard.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(item.Value.Percent);
}
cellStandard.SetCellValue(item.Value.Value);
}
foreach (var item in newDiffrent[i].Match.list.OrderBy(o => o.Key))
{
j++;
var cellStandard = dataRow.CreateCell(j);
//红色单元格
style = workbookStandard.CreateCellStyle();
if (item.Value.Red || string.IsNullOrWhiteSpace(item.Value.Value))
{
style.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style.FillPattern = FillPattern.SolidForeground;
cellStandard.CellStyle = style;
}
if ((!string.IsNullOrWhiteSpace(item.Value.Value)) && item.Value.Value.Contains('%'))
{
cellStandard.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(item.Value.Percent);
}
cellStandard.SetCellValue(item.Value.Value);
}
}
}
ISheet sheetfileStandard3 = workbookStandard.GetSheetAt(3);
for (int i = 0; i < newSame.Count; i++)
{
var dataRow = sheetfileStandard3.CreateRow(i + 2);
if (null != (newSame[i]))
{
int j = 0;
foreach (var item in newSame[i].Base.list.OrderBy(o => o.Key))
{
j++;
var cellStandard = dataRow.CreateCell(j);
if ((!string.IsNullOrWhiteSpace(item.Value.Value)) && item.Value.Value.Contains('%'))
{
cellStandard.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(item.Value.Percent);
}
cellStandard.SetCellValue(item.Value.Value);
}
foreach (var item in newSame[i].Match.list.OrderBy(o => o.Key))
{
j++;
var cellStandard = dataRow.CreateCell(j);
if ((!string.IsNullOrWhiteSpace(item.Value.Value)) && item.Value.Value.Contains('%'))
{
cellStandard.CellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat(item.Value.Percent);
}
cellStandard.SetCellValue(item.Value.Value);
}
}
}
ISheet sheetfileStandard4 = workbookStandard.GetSheetAt(4);
for (int i = 0; i < 2; i++)//复制表头
{
CopyRow(workbookStandard, sheetfileStandard4, sheet, i, i);
}
for (int i = 0; i < manyBase.Count; i++)
{
CopyRow(workbookStandard, sheetfileStandard4, sheet, manyBase[i].Row, i + 2);
}
ISheet sheetfileStandard5 = workbookStandard.GetSheetAt(5);
for (int i = 0; i < 2; i++)//复制表头
{
CopyRow(workbookStandard, sheetfileStandard5, sheet2, i, i);
}
for (int i = 0; i < manyMatch.Count; i++)
{
CopyRow(workbookStandard, sheetfileStandard5, sheet2, manyMatch[i].Row, i + 2);
}
string guid = Common.Result.GetNewId();
var saveFileName = entity.GoldTempFullPath.Path(guid);
entity.Result = saveFileName;
var xlsPathFileName = pathmy + @"\up\Result\" + saveFileName;
using (FileStream fileWrite = new FileStream(xlsPathFileName, FileMode.Create))
{
workbookStandard.Write(fileWrite);
}
return(saveFileName);
}
catch (Exception ex)
{
throw;
}
}
static IRow CreateAnalysisRow(ChemicalAnalysis analysis, ISheet sheet, int rowIndex, ICellStyle cellDateStyle)
{
var row = sheet.CreateRow(rowIndex);
row.CreateCell(0).SetCellValue(analysis.WellName);
var cell = row.CreateCell(1, CellType.Numeric);
cell.SetCellValue(analysis.Date);
cell.CellStyle = cellDateStyle;
return(row);
}
public FileResult Export(int pagesize, int pagenum, string orgname, string orgcode, string year)
{
try
{
FuncResult fr = new FuncResult()
{
IsSuccess = true, Message = "Ok"
};
var summarydata = gasBll.GetList(new SearchExcelModel()
{
orgname = orgname, orgcode = orgcode, year = year
});
var data = (List <ReturnPollutantModel>)((dynamic)summarydata).Content;
string TempletFileName = $"{hosting.WebRootPath}\\template\\企业用气情况取数表格式-佛山市高明燃气有限公司.xls";
FileStream file = new FileStream(TempletFileName, FileMode.Open, FileAccess.Read);
var xssfworkbook = new HSSFWorkbook(file);
ISheet sheet1 = xssfworkbook.GetSheet("Sheet1");
ICellStyle Style = xssfworkbook.CreateCellStyle();
Style.Alignment = HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.BorderTop = BorderStyle.Thin;
Style.BorderRight = BorderStyle.Thin;
Style.BorderLeft = BorderStyle.Thin;
Style.BorderBottom = BorderStyle.Thin;
Style.DataFormat = 0;
for (int i = 5; i < data.Count + 5; i++)
{
var row = sheet1.CreateRow(i);
row.Height = 35 * 20;
row.CreateCell(0).SetCellValue(i - 4);
row.Cells[0].CellStyle = Style;
row.CreateCell(1).SetCellValue(data[i - 5].OrgName);
row.Cells[1].CellStyle = Style;
row.CreateCell(2).SetCellValue(data[i - 5].Town);
row.Cells[2].CellStyle = Style;
row.CreateCell(3).SetCellValue(data[i - 5].OrgCode);
row.Cells[3].CellStyle = Style;
row.CreateCell(4).SetCellValue(data[i - 5].RegistrationType);
row.Cells[4].CellStyle = Style;
row.CreateCell(5).SetCellValue(data[i - 5].Address);
row.Cells[5].CellStyle = Style;
row.CreateCell(6).SetCellValue(Convert.ToDouble(data[i - 5].Gas));
row.Cells[6].CellStyle = Style;
row.CreateCell(7).SetCellValue(Convert.ToDouble(data[i - 5].Other));
row.Cells[7].CellStyle = Style;
row.CreateCell(8).SetCellValue(data[i - 5].Remark);
row.Cells[8].CellStyle = Style;
}
//转为字节数组
var stream = new MemoryStream();
xssfworkbook.Write(stream);
var buf = stream.ToArray();
return(File(buf, "application/ms-excel", $"{DateTime.Now.ToString("yyyy-MM-dd:hh:mm:ss")}.xls"));
}
catch (Exception ex)
{
throw new Exception("error", ex);
}
}
private static void CreateOneRowTwoColumnCellsWithColumn1Style(ISheet sheet, ref int rowIndex, string column0_text, ICellStyle style)
{
IRow row = sheet.CreateRow(rowIndex);
ICell cell = (ICell)row.CreateCell(0);
cell.SetCellValue(column0_text);
cell = (ICell)row.CreateCell(1);
cell.CellStyle = style;
rowIndex++;
}
/**
* Creates a cell, gives it a value, and applies a style if provided
*
* @param row the row to create the cell in
* @param column the column index to create the cell in
* @param value The value of the cell
* @param style If the style is not null, then set
* @return A new Cell
*/
public static ICell CreateCell(IRow row, int column, String value, ICellStyle style)
{
ICell cell = GetCell(row, column);
cell.SetCellValue(cell.Row.Sheet.Workbook.GetCreationHelper()
.CreateRichTextString(value));
if (style != null)
{
cell.CellStyle = (style);
}
return cell;
}
public static MemoryStream ToMemoryStram(SummaryInfo summaryInfo,
List <Matrix <InspectionDataReport> > list_InspectionDataReportMatrix,
List <DefectTypeInfoReport> list_DefectTypeInfoReport,
DefectStatistics defectStatistics,
int ReportModel)
{
IWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet(ExcelText.SheetName_SummaryOperator);
#region SummaryOperator
int rowIndex = 0;
int code2drowIndex = 0;
//1、产品编号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode);
//2、批次号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot);
//3、设备号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine);
//4、操作员
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator);
//5、开始日期
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartDate, summaryInfo.StartDate);
//6、开始时间
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.StartTime, summaryInfo.StartTime);
//7、结束日期
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndDate, summaryInfo.EndDate);
//8、结束时间
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.EndTime, summaryInfo.EndTime);
rowIndex++;
//10、批次理论盘数 没有输入默认10 已屏蔽
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.TotalNumberOfStrips, summaryInfo.TotalNumberOfStrips.ToString());
//11、已检测的盘数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsInspected, summaryInfo.NumberOfStripsInspected.ToString());
//12、未检测的盘数 已屏蔽
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStripsNotInspected, summaryInfo.NumberOfStripsNotInspected.ToString());
//13、单盘芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.QuantityOfDevicesPerStrip, summaryInfo.QuantityOfDevicesPerStrip.ToString());
//14、批次理论芯片数
//CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfStartQuantity, summaryInfo.NumberOfStartQuantity.ToString());
//15、批次已检测芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesInspected, summaryInfo.NumberOfDevicesInspected.ToString());
//16、每小时检测芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DevicesPerHour, string.Format("{0:0.##}", summaryInfo.DevicesPerHour));
rowIndex++;
//17、合格芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesPassed, summaryInfo.NumberOfDevicesPassed.ToString());
//18、不合格的芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesRejected, summaryInfo.NumberOfDevicesRejected.ToString());
//19、误检芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfDevicesFalseCalled, summaryInfo.NumberOfDevicesFalseCalled.ToString());
//新增 OK复看为NG数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "OK复看NG数", summaryInfo.NumberOfReviewNG.ToString());
//20、跳过的芯片数
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.NumberOfNoDies, summaryInfo.NumberOfNoDies.ToString());
//21、合格率 存在二维码时根据二维码总数
if (ReportModel == 1)
{
//二维码总数 只在武汉二维码报表模式生成
CreateOneRowTwoColumnCells(sheet, ref rowIndex, string.Format("{0}", "二维码总数"), summaryInfo.CodeNumber.ToString());
double temp = summaryInfo.NumberOfDevicesPassed / (double)summaryInfo.CodeNumber;
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", temp.ToString()));
}
else
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.YieldByDevice, string.Format("{0:0.##}", summaryInfo.YieldByDevice));
}
//22、误检率
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.FalseCallDevicePercent, string.Format("{0:0.##}", summaryInfo.FalseCallDevicePercent));
//新增 当存在 K2N时显示算上复看OK到KG的误检率
if (summaryInfo.NumberOfReviewNG != 0)
{
// CreateOneRowTwoColumnCells(sheet, ref rowIndex, "误检率（%包含复看不合格）", string.Format("{0:0.##}", summaryInfo.DevicePercentOfK2N));
}
rowIndex++;
//23、错误种类出现数量
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType, ExcelText.Count);
foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport)
{
if (defectTypeInfoReport.Count > 0)
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", defectTypeInfoReport.Index, defectTypeInfoReport.DefectType),
defectTypeInfoReport.Count.ToString());
}
}
rowIndex++;
//24、错误种类根据错误优先级统计数量
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.DefectType + "(根据错误优先级表)", ExcelText.Count);
if (defectStatistics.Flag == false)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告，存在未规定的错误优先级", "");
}
else
{
foreach (KeyValuePair <int, int> ds in defectStatistics.CountDefectResult)
{
if (defectStatistics.CountN2KDefectResult.ContainsKey(ds.Key))
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType),
string.Format("{0}-{1}={2}", ds.Value, defectStatistics.CountN2KDefectResult[ds.Key], ds.Value - defectStatistics.CountN2KDefectResult[ds.Key]));
}
else
{
CreateOneRowTwoColumnCells(sheet,
ref rowIndex,
string.Format("{0}:{1}", ds.Key, list_DefectTypeInfoReport.Find(ls => ls.Index == ds.Key).DefectType),
ds.Value.ToString());
}
}
//25、是否存在优先级相同并且一同输出的
if (defectStatistics.List_RepeatPriority.Count != 0)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, "警告 存在优先级相同情况", "");
for (int i = 0; i < defectStatistics.List_RepeatPriority.Count; i++)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectStatistics.List_RepeatPriority[i].ToString(), "");
}
}
}
sheet.SetColumnWidth(0, 80 * 256);
sheet.SetColumnWidth(0, 30 * 256);
#endregion
workbook.CreateSheet(ExcelText.SheetName_MapOperator);
workbook.CreateSheet(ExcelText.SheetName_UDD);
int T = 0;
#region 创建错误图片Sheet
sheet = workbook.CreateSheet("错误图片");
int sheetIndex = workbook.GetSheetIndex(sheet);
sheet.SetColumnWidth(1, 30 * 256);
sheet.SetColumnWidth(2, 30 * 256);
int ImageSheetNum = 0;
T = T + rowIndex;
rowIndex = 0;
foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix)
{
if (rowIndex >= 65000)
{
ImageSheetNum++;
sheet = workbook.CreateSheet("错误图片续_" + ImageSheetNum);
sheetIndex = workbook.GetSheetIndex(sheet);
rowIndex = 0;
}
HSSFPatriarch sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
HSSFCell cell = null;
int defectCountInFrame = 0;
//int sheetContinuedIndexEachStrip = 0;
int ImageCol = 0;
foreach (InspectionDataReport dataReport in inspectionDataReportMatrix)
{
defectCountInFrame++;
int TempImage_row = 0;
ImageCol = 5;
int startRow = 0;
if (dataReport == null)
{
continue;
}
if (dataReport.List_DefectData.Count == 0)
{
if (dataReport.InspectionResult != InspectionResult.K2N)
{
continue;
}
}
startRow = rowIndex;
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0);
cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1));
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
cell.SetCellValue(string.Format("第{0}条", dataReport.FrameIndex));
if (dataReport.Code2D != "null")
{
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(2);
cell.SetCellValue(string.Format("二维码：{0}", dataReport.Code2D));
}
// 创建图片 所有缺陷在一张图中 如果存在拍多次的情况 进行横排 第一张图 col为5
System.Drawing.Image[] images = new System.Drawing.Image[dataReport.List_GeneralImageTempPath.Count];
for (int i = 0; i < images.Count(); i++)
{
//创建图片单元格
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(ImageCol);
if (File.Exists(dataReport.List_GeneralImageTempPath[i]))
{
byte[] bytes = File.ReadAllBytes(dataReport.List_GeneralImageTempPath[i]);
int index = workbook.AddPicture(bytes, PictureType.JPEG);
System.Drawing.Image image;
using (MemoryStream ms = new MemoryStream(bytes))
{
image = System.Drawing.Image.FromStream(ms);
}
double imageWidth = image.Width;
double imageHeight = image.Height;
image.Dispose();
double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
int imageInCellColumns = (int)(imageWidth / cellWidth);
int imageInCellRows = (int)(imageHeight / cellHeight);
double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, ImageCol, rowIndex, imageInCellColumns + ImageCol, rowIndex + imageInCellRows);
sheetPatriarch.CreatePicture(anchor, index);
//计算图片高占多少个单元格
TempImage_row = (int)Math.Ceiling(imageHeight / cellHeight) + 1;
NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex + TempImage_row, 1);
dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString());
}
else
{
cell.SetCellValue("图片不存在");
}
ImageCol = ImageCol + 10;
}
rowIndex = rowIndex + 1;
//记录错误信息
foreach (var defectReort in dataReport.List_DefectData)
{
rowIndex = rowIndex + 1;
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
if (defectReort.DefectTypeIndex <= 0 || defectReort.DefectTypeIndex > list_DefectTypeInfoReport.Count)
{
cell.SetCellValue(string.Format("{0}:{1}", defectReort.DefectTypeIndex, defectReort.ErrorDetail));
}
else
{
cell.SetCellValue(string.Format("{0}:{1}", list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType, defectReort.ErrorDetail));
}
}
rowIndex = rowIndex + 1;
string[] show_defecttype = new string[dataReport.List_DefectData.Count];
for (int i = 0; i < dataReport.List_DefectData.Count; i++)
{
show_defecttype[i] = dataReport.List_DefectData[i].DefectTypeIndex.ToString();
}
sheet.CreateRow(rowIndex);
cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
cell.SetCellValue(string.Format("详细错误码：{0}", string.Join(";", show_defecttype)));
//判断 图片所占行数多 还是写入的错误信息行数多
if (rowIndex - startRow <= TempImage_row)
{
rowIndex = startRow + TempImage_row + 2;
}
#region 每个缺陷对应一张图 已屏蔽
//foreach (var defectReort in dataReport.List_DefectData)
//{
// if (rowIndex>= 32757)
// {
// sheetContinuedIndexEachStrip++;
// sheet = workbook.CreateSheet(inspectionDataReportMatrix.FrameName + " " + ExcelText.Continued + sheetContinuedIndexEachStrip.ToString());
// sheetIndex = workbook.GetSheetIndex(sheet);
// sheetPatriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
// rowIndex = 0;
// }
// defectCountInFrame++;
// sheet.CreateRow(rowIndex);
// sheet.CreateRow(rowIndex + 1);
// HSSFCell cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(0);
// cell.SetCellValue(string.Format("{0}-{1}", dataReport.RowIndex + 1, dataReport.ColumnIndex + 1));
// cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
// cell.SetCellValue(list_DefectTypeInfoReport[defectReort.DefectTypeIndex - 1].DefectType);
// cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(1);
// cell.SetCellValue(defectReort.ErrorDetail);
// rowIndex += 2;
// sheet.CreateRow(rowIndex);
// cell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(1);
// if (File.Exists(defectReort.ImageTempPath))
// {
// byte[] bytes = File.ReadAllBytes(defectReort.ImageTempPath);
// int index = workbook.AddPicture(bytes, PictureType.JPEG);
// System.Drawing.Image image;
// using (MemoryStream ms = new MemoryStream(bytes))
// {
// image = System.Drawing.Image.FromStream(ms);
// }
// double imageWidth = image.Width;
// double imageHeight = image.Height;
// image.Dispose();
// double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// int imageInCellColumns = (int)(imageWidth / cellWidth);
// int imageInCellRows = (int)(imageHeight / cellHeight);
// double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
//
// //HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// //commentAnchor.AnchorType = AnchorType.MoveDontResize;
// //HSSFComment comment = (HSSFComment)sheetPatriarch.CreateCellComment(commentAnchor);
// //comment.SetBackgroundImage(index);
// //cell.CellComment = (comment);
// HSSFClientAnchor anchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 1, rowIndex, imageInCellColumns + 1, rowIndex + imageInCellRows);
// sheetPatriarch.CreatePicture(anchor, index);
// if (!hasRecordLink)
// {
// NPOI.SS.Util.CellReference cellReference = new NPOI.SS.Util.CellReference(rowIndex, 1);
// dataReport.ExcelDefectImageLink = string.Format("'{0}'!{1}", sheet.SheetName, cellReference.FormatAsString());
// hasRecordLink = true;
// }
// rowIndex += (int)Math.Ceiling(imageHeight / cellHeight) + 1;
// }
// else
// {
// cell.SetCellValue("图片不存在");
// }
//}
#endregion
}
if (defectCountInFrame == 0)
{
workbook.RemoveSheetAt(sheetIndex);
}
}
#endregion
#region MapOperator
sheet = workbook.GetSheet(ExcelText.SheetName_MapOperator);
if (ReportModel != 1)
{
sheet.DefaultColumnWidth = 1;
}
//sheet = workbook.GetSheetAt(1);
rowIndex = 0;
//1、产品编号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.ProductCode, summaryInfo.ProductCode);
//2、批次号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Lot, summaryInfo.Lot);
//3、设备号
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.Machine, summaryInfo.Machine);
//4、操作员
CreateOneRowTwoColumnCells(sheet, ref rowIndex, ExcelText.AIOperator, summaryInfo.AIOperator);
rowIndex++;
//5、检测结果样例
Dictionary <InspectionResult, ICellStyle> dict_result_style = new Dictionary <InspectionResult, ICellStyle>();
ICellStyle exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Green;
dict_result_style.Add(InspectionResult.OK, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.OK), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Red;
dict_result_style.Add(InspectionResult.NG, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.NG), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Yellow;
dict_result_style.Add(InspectionResult.N2K, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.N2K), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.Orange;
dict_result_style.Add(InspectionResult.K2N, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.K2N), exampleStyle);
exampleStyle = workbook.CreateCellStyle();
exampleStyle.BorderBottom = BorderStyle.Thin;
exampleStyle.BorderTop = BorderStyle.Thin;
exampleStyle.BorderLeft = BorderStyle.Thin;
exampleStyle.BorderRight = BorderStyle.Thin;
exampleStyle.FillPattern = FillPattern.SolidForeground;
exampleStyle.Alignment = HorizontalAlignment.Center;
exampleStyle.FillForegroundColor = (short)ExcelColors.SkyBlue;
dict_result_style.Add(InspectionResult.SKIP, exampleStyle);
CreateOneRowTwoColumnCellsWithColumn1Style(sheet, ref rowIndex, InspectionResultToString(InspectionResult.SKIP), exampleStyle);
rowIndex++;
//6、分盘绘制图谱
HSSFPatriarch patriarch = (HSSFPatriarch)sheet.CreateDrawingPatriarch();
ICellStyle centerAlignmentStyle = workbook.CreateCellStyle();
centerAlignmentStyle.Alignment = HorizontalAlignment.Center;
int sheetContinuedIndex = 0;
foreach (Matrix <InspectionDataReport> inspectionDataReportMatrix in list_InspectionDataReportMatrix)
{
if (inspectionDataReportMatrix.ColumnCount < 254)
{
//创建单元格
for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++)
{
IRow createRow = sheet.CreateRow(rowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++)
{
createRow.CreateCell(j);
}
}
//赋值条号
sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
{
ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = centerAlignmentStyle;
}
//创建二维码列表
if (ReportModel == 1)
{
code2drowIndex = rowIndex + inspectionDataReportMatrix.RowCount + 2;
//创建单元格
for (int i = 0; i < inspectionDataReportMatrix.RowCount + 1; i++)
{
IRow createRow = sheet.CreateRow(code2drowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.ColumnCount + 2; j++)
{
createRow.CreateCell(j);
}
}
//赋值二维码
sheet.GetRow(code2drowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", "二维码", inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
sheet.GetRow(code2drowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
//for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
//{
// ICell cell = sheet.GetRow(code2drowIndex + inspectionDataReportMatrix.RowCount).GetCell(i + 2);
// //cell.SetCellValue((i + 1).ToString());
// cell.CellStyle = centerAlignmentStyle;
//}
foreach (InspectionDataReport data1 in inspectionDataReportMatrix)
{
if (data1 == null)
{
continue;
}
ICell cell;
if (inspectionDataReportMatrix.ColumnCount < 254)
{
cell = sheet.GetRow(code2drowIndex + data1.RowIndex).GetCell(data1.ColumnIndex + 2);
}
else
{
cell = sheet.GetRow(code2drowIndex + data1.ColumnIndex).GetCell(data1.RowIndex + 2);
}
cell.SetCellValue(data1.Code2D);
}
}
}
else
{
if (rowIndex + inspectionDataReportMatrix.ColumnCount + 1 >= 32757)
{
sheetContinuedIndex++;
sheet = workbook.CreateSheet(ExcelText.SheetName_MapOperator + " " + ExcelText.Continued + sheetContinuedIndex.ToString());
rowIndex = 0;
}
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount + 1; i++)
{
IRow createRow = sheet.CreateRow(rowIndex + i);
for (int j = 0; j < inspectionDataReportMatrix.RowCount + 2; j++)
{
createRow.CreateCell(j);
}
}
sheet.GetRow(rowIndex).GetCell(0).SetCellValue(string.Format("{0}:{1}", ExcelText.Strip, inspectionDataReportMatrix.FrameName));
for (int i = 0; i < inspectionDataReportMatrix.ColumnCount; i++)
{
sheet.GetRow(rowIndex + i).GetCell(1).SetCellValue((i + 1).ToString());
}
for (int i = 0; i < inspectionDataReportMatrix.RowCount; i++)
{
ICell cell = sheet.GetRow(rowIndex + inspectionDataReportMatrix.ColumnCount).GetCell(i + 2);
cell.SetCellValue((i + 1).ToString());
cell.CellStyle = centerAlignmentStyle;
}
}
foreach (InspectionDataReport data in inspectionDataReportMatrix)
{
if (data == null)
{
continue;
}
ICell cell;
if (inspectionDataReportMatrix.ColumnCount < 254)
{
cell = sheet.GetRow(rowIndex + data.RowIndex).GetCell(data.ColumnIndex + 2);
}
else
{
cell = sheet.GetRow(rowIndex + data.ColumnIndex).GetCell(data.RowIndex + 2);
}
cell.CellStyle = dict_result_style[data.InspectionResult];
if (data.InspectionResult == InspectionResult.NG)
{
//string[] defectTypes = new string[data.List_DefectData.Count];
//for (int i = 0; i < data.List_DefectData.Count; i++)
//{
// defectTypes[i] = data.List_DefectData[i].DefectTypeIndex.ToString();
//}
string[] noRepeatDefectTyoes = new string[data.Priority_DetectType.Count];
for (int i = 0; i < data.Priority_DetectType.Count; i++)
{
noRepeatDefectTyoes[i] = data.Priority_DetectType[i].ToString();
}
//string[] noRepeatDefectTyoes = data.List_DefectData.Select(d => d.DefectTypeIndex.ToString()).Distinct().ToArray();
cell.SetCellValue(string.Join(";", noRepeatDefectTyoes));
}
if (data.InspectionResult == InspectionResult.NG || data.InspectionResult == InspectionResult.N2K || data.InspectionResult == InspectionResult.K2N)
{
if (data.ExcelDefectImageLink == null)
{
continue;
}
HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
link.Address = data.ExcelDefectImageLink;
cell.Hyperlink = link;
}
#region 对NG图 以及 N2K图添加批注 鼠标悬停可以显示图片 已屏蔽
//if (data.InspectionResult == InspectionResults.NG || data.InspectionResult == InspectionResults.N2K)
//{
// //int defectCount = data.List_DefectData.Count;
// //System.Drawing.Image[] images = new System.Drawing.Image[defectCount];
// System.Drawing.Image[] images = new System.Drawing.Image[data.List_GeneralImageTempPath.Count];
// double imageMaxWidth = 0;
// double imageTotalHeight = 0;
// //for (int i = 0; i < defectCount; i++)
// //{
// // if (!File.Exists(data.List_DefectData[i].ImageTempPath)) break;
// // byte[] bytesOfImage = File.ReadAllBytes(data.List_DefectData[i].ImageTempPath);
// // using (MemoryStream ms = new MemoryStream(bytesOfImage))
// // {
// // images[i] = System.Drawing.Image.FromStream(ms);
// // }
// // if (images[i].Width > imageMaxWidth)
// // {
// // imageMaxWidth = images[i].Width;
// // }
// // imageTotalHeight += images[i].Height;
// //}
// for (int i = 0; i < images.Count(); i++)
// {
// if (!File.Exists(data.List_GeneralImageTempPath[i])) break;
// byte[] bytesOfImage = File.ReadAllBytes(data.List_GeneralImageTempPath[i]);
// using (MemoryStream ms = new MemoryStream(bytesOfImage))
// {
// images[i] = System.Drawing.Image.FromStream(ms);
// }
// if (images[i].Width > imageMaxWidth)
// {
// imageMaxWidth = images[i].Width;
// }
// imageTotalHeight += images[i].Height;
// }
// if (imageMaxWidth == 0 || imageTotalHeight == 0) continue;
// Bitmap concatImage = new Bitmap((int)imageMaxWidth, (int)imageTotalHeight);
// concatImage.SetResolution(300, 300);
// Graphics g = Graphics.FromImage(concatImage);
// float drawStartHeight = 0;
// for (int i = 0; i < images.Count(); i++)
// {
// g.DrawImage(images[i], 0, drawStartHeight);
// drawStartHeight += images[i].Height;
// images[i].Dispose();
// }
// byte[] bytesOfConcatImage;
// using (MemoryStream ms = new MemoryStream())
// {
// concatImage.Save(ms, System.Drawing.Imaging.ImageFormat.Jpeg);
// bytesOfConcatImage = ms.ToArray();
// }
// int index = workbook.AddPicture(bytesOfConcatImage, PictureType.JPEG);
// double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip)
// //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI
// //要计算Excel的行高，就先把它行转换到英寸，再乘小DPI就可以得到像素
// //像素 = (Excel的行高度 / 72) * DPI
// double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// int imageInCellColumns = (int)(imageMaxWidth / cellWidth);
// int imageInCellRows = (int)(imageTotalHeight / cellHeight);
// double offsetX = (imageMaxWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// double offsetY = (imageTotalHeight - cellHeight * imageInCellRows) / cellHeight * 256;
// IClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// commentAnchor.AnchorType = AnchorType.MoveDontResize;
// HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor);
// comment.SetBackgroundImage(index);
// cell.CellComment = comment;
// HSSFHyperlink link = new HSSFHyperlink(HyperlinkType.Document);
// link.Address = data.ExcelDefectImageLink;
// cell.Hyperlink = link;
// //NPOI.SS.Util.CellReference cr = new NPOI.SS.Util.CellReference("A1");
// //ICellStyle hlink_style = hssfworkbook.CreateCellStyle();
// //IFont hlink_font = hssfworkbook.CreateFont();
// //hlink_font.Underline = FontUnderlineType.Single;
// //hlink_font.Color = HSSFColor.Blue.Index;
// //hlink_style.SetFont(hlink_font);
// //cell.CellStyle = (hlink_style);
// //HSSFCell picIndexCell = (HSSFCell)sheet.GetRow(rowIndex).CreateCell(picColumnIndex);
// //picIndexCell.SetCellValue(string.Format("{0}-{1}", data.Row + 1, data.Column + 1));
// //picIndexCell.CellStyle = centerAlignmentStyle;
// //if (!data.PicturePath.Equals(string.Empty))
// //{
// // if (File.Exists(data.PicturePath))
// // {
// // byte[] bytes = File.ReadAllBytes(data.PicturePath);
// // int index = workbook.AddPicture(bytes, PictureType.JPEG);
// // System.Drawing.Image image;
// // using (MemoryStream ms = new MemoryStream(bytes))
// // {
// // image = System.Drawing.Image.FromStream(ms);
// // }
// // double imageWidth = image.Width;
// // double imageHeight = image.Height;
// // image.Dispose();
// // double cellWidth = (double)sheet.GetColumnWidthInPixels(cell.ColumnIndex);
// // //EXCEL列高度的单位是磅,Apache POI的行高度单位是缇(twip)
// // //DPI = 1英寸内可显示的像素点个数。通常电脑屏幕是96DPI, IPhone4s的屏幕是326DPI, 普通激光黑白打印机是400DPI
// // //要计算Excel的行高，就先把它行转换到英寸，再乘小DPI就可以得到像素
// // //像素 = (Excel的行高度 / 72) * DPI
// // double cellHeight = sheet.DefaultRowHeightInPoints / 72 * 96;
// // int imageInCellColumns = (int)(imageWidth / cellWidth);
// // int imageInCellRows = (int)(imageHeight / cellHeight);
// // double offsetX = (imageWidth - cellWidth * imageInCellColumns) / cellWidth * 1024;
// // double offsetY = (imageHeight - cellHeight * imageInCellRows) / cellHeight * 256;
// // HSSFClientAnchor commentAnchor = new HSSFClientAnchor(0, 0, (int)offsetX, (int)offsetY, 0, 0, imageInCellColumns, imageInCellRows);
// // commentAnchor.AnchorType = AnchorType.MoveDontResize;
// // HSSFComment comment = (HSSFComment)patriarch.CreateCellComment(commentAnchor);
// // comment.SetBackgroundImage(index);
// // cell.CellComment = (comment);
// // //HSSFClientAnchor anchor = new HSSFClientAnchor(100, 0, 923, 0, picColumnIndex, rowIndex + 1, picColumnIndex, rowIndex + dieDataSQLMatrix.RowCount);
// // //patriarch.CreatePicture(anchor, index);
// // }
// //}
// //else
// //{
// // cell = (HSSFCell)sheet.GetRow(rowIndex + 1).CreateCell(picColumnIndex);
// // cell.SetCellValue("图片不存在");
// // cell.CellStyle = centerAlignmentStyle;
// //}
// //picColumnIndex++;
//}
#endregion
}
if (inspectionDataReportMatrix.ColumnCount < 254)
{
if (ReportModel == 1)
{
rowIndex = code2drowIndex;
}
rowIndex += inspectionDataReportMatrix.RowCount + 2;
}
else
{
rowIndex += inspectionDataReportMatrix.ColumnCount + 2;
}
}
sheet.SetColumnWidth(0, 20 * 256);
#endregion
#region UDD
sheet = workbook.GetSheet(ExcelText.SheetName_UDD);
//sheet = workbook.GetSheetAt(2);
rowIndex = 0;
foreach (DefectTypeInfoReport defectTypeInfoReport in list_DefectTypeInfoReport)
{
CreateOneRowTwoColumnCells(sheet, ref rowIndex, defectTypeInfoReport.Index.ToString(), defectTypeInfoReport.DefectType);
}
#endregion
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
//sheet.Dispose();
return(ms);
}
}
/**
* Sets the format properties of the given style based on the given map.
*
* @param style cell style
* @param workbook parent workbook
* @param properties map of format properties (String -> Object)
* @see #getFormatProperties(CellStyle)
*/
private static void SetFormatProperties(ICellStyle style, IWorkbook workbook, Dictionary<String, Object> properties)
{
style.Alignment=(HorizontalAlignment)(GetShort(properties, ALIGNMENT));
style.BorderBottom=(BorderStyle)(GetShort(properties, BORDER_BOTTOM));
style.BorderLeft=(BorderStyle)(GetShort(properties, BORDER_LEFT));
style.BorderRight=(BorderStyle)(GetShort(properties, BORDER_RIGHT));
style.BorderTop=(BorderStyle)(GetShort(properties, BORDER_TOP));
style.BottomBorderColor=(GetShort(properties, BOTTOM_BORDER_COLOR));
style.DataFormat=(GetShort(properties, DATA_FORMAT));
style.FillBackgroundColor=(GetShort(properties, FILL_BACKGROUND_COLOR));
style.FillForegroundColor=(GetShort(properties, FILL_FOREGROUND_COLOR));
style.FillPattern=(FillPatternType)(GetShort(properties, FILL_PATTERN));
style.SetFont(workbook.GetFontAt(GetShort(properties, FONT)));
style.IsHidden=(GetBoolean(properties, HIDDEN));
style.Indention=(GetShort(properties, INDENTION));
style.LeftBorderColor=(GetShort(properties, LEFT_BORDER_COLOR));
style.IsLocked=(GetBoolean(properties, LOCKED));
style.RightBorderColor=(GetShort(properties, RIGHT_BORDER_COLOR));
style.Rotation=(GetShort(properties, ROTATION));
style.TopBorderColor=(GetShort(properties, TOP_BORDER_COLOR));
style.VerticalAlignment=(VerticalAlignment)(GetShort(properties, VERTICAL_ALIGNMENT));
style.WrapText=(GetBoolean(properties, WRAP_TEXT));
}
/// <summary>
/// DataTable导出到Excel的MemoryStream
/// </summary>
/// <param name="dtSource">源DataTable</param>
/// <param name="strHeaderText">表头文本</param>
private static MemoryStream Export(DataTable dtSource, Hashtable Columns, string strHeaderText)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
//#region 右击文件 属性信息
//{
// DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
// dsi.Company = "NPOI";
// workbook.DocumentSummaryInformation = dsi;
// SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
// si.Author = "文件作者信息"; //填加xls文件作者信息
// si.ApplicationName = "创建程序信息"; //填加xls文件创建程序信息
// si.LastAuthor = "最后保存者信息"; //填加xls文件最后保存者信息
// si.Comments = "作者信息"; //填加xls文件作者信息
// si.Title = "标题信息"; //填加xls文件标题信息
// si.Subject = "主题信息";//填加文件主题信息
// si.CreateDateTime = DateTime.Now;
// workbook.SummaryInformation = si;
//}
//#endregion
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
//取得列宽
//int[] arrColWidth = new int[dtSource.Columns.Count];
//foreach (DataColumn item in dtSource.Columns)
//{
// arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
//}
//for (int i = 0; i < dtSource.Rows.Count; i++)
//{
// for (int j = 0; j < dtSource.Columns.Count; j++)
// {
// int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length;
// if (intTemp > arrColWidth[j])
// {
// arrColWidth[j] = intTemp;
// }
// }
//}
int rowIndex = 0;
IRow excelRow;
ICellStyle headStyle = workbook.CreateCellStyle();
IFont font;
ICell newCell;
foreach (DataRow row in dtSource.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == MaxRowCount || rowIndex == 0)
{
if (rowIndex != 0)
{
rowIndex = 0;
sheet = workbook.CreateSheet();
}
#region 表头及样式
if (!string.IsNullOrEmpty(strHeaderText))
{
excelRow = sheet.CreateRow(0);
excelRow.HeightInPoints = 25;
excelRow.CreateCell(0).SetCellValue(strHeaderText);
headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
excelRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1));
// headerRow.Dispose();
rowIndex++;
}
#endregion
#region 列头及样式
excelRow = sheet.CreateRow(rowIndex);
headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
string columnName;
foreach (DataColumn column in dtSource.Columns)
{
columnName = GetColumnName(column.ColumnName, Columns);
excelRow.CreateCell(column.Ordinal).SetCellValue(columnName);
excelRow.GetCell(column.Ordinal).CellStyle = headStyle;
//设置列宽
// sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
// headerRow.Dispose();
rowIndex++;
#endregion
// rowIndex = 2;
}
#endregion
#region 填充内容
excelRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in dtSource.Columns)
{
newCell = excelRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
if (DateTime.TryParse(drValue, out dateV))
{
newCell.SetCellValue(dateV.ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
newCell.SetCellValue("");
}
// 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++;
int maxRowCount = dtSource.Rows.Count;
maxRowCount = string.IsNullOrEmpty(strHeaderText) ? maxRowCount + 1 : maxRowCount + 2;
maxRowCount = Math.Min(maxRowCount, 65535);
if (maxRowCount == rowIndex)
{
for (int i = 0; i < sheet.GetRow(0).PhysicalNumberOfCells; i++)
{
sheet.AutoSizeColumn(i);
}
}
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
// sheet.Dispose();
//workbook.Dispose();//一般只用写这一个就OK了，他会遍历并释放所有资源，但当前版本有问题所以只释放sheet
return(ms);
}
}
/// <summary>
/// Append the required format depending on the COBieAllowedType
/// </summary>
/// <param name="type">COBieAllowedType</param>
/// <param name="cellStyle">ICellStyle, style to ally formate to</param>
private void AppendFormat(COBieAllowedType type, ICellStyle cellStyle)
{
string formatString = null;
switch (type)
{
case COBieAllowedType.ISODate:
formatString = "yyyy-MM-dd";
break;
case COBieAllowedType.ISODateTime:
formatString = "yyyy-MM-ddThh:mm:ss";
break;
case COBieAllowedType.AlphaNumeric:
break;
case COBieAllowedType.Email:
break;
case COBieAllowedType.Numeric:
break;
case COBieAllowedType.Text:
break;
case COBieAllowedType.AnyType:
break;
default:
break;
}
if (formatString != null)
{
IDataFormat dataFormat = ExcelWorkbook.CreateDataFormat();
cellStyle.DataFormat = dataFormat.GetFormat(formatString);
}
}
/// <summary>
/// 功能： 导出到Excel
/// </summary>
/// <param name="workbook"></param>
/// <param name="title">表头标题</param>
/// <param name="dt">导出的数据</param>
/// <param name="sheetname">sheet表格的名字</param>
public static bool CreateExcel(NPOI.SS.UserModel.IWorkbook workbook, List <string> title, List <int> iColumns, DataTable dt, string sheetname, string sheetTitle, string subTitle)
{
if (iColumns.Count() != title.Count() || iColumns.Max() >= dt.Columns.Count)
{
return(false);
}
int rowRecord = 0;
ISheet sheet = workbook.CreateSheet(sheetname);
#region 定义Sheet标题及子标题
IRow titleRow = sheet.CreateRow(0);
ICell titleCell = titleRow.CreateCell(0);
titleCell.SetCellValue(sheetTitle);
//ICellStyle titleStyle = workbook.CreateCellStyle();
//titleStyle.Alignment = HorizontalAlignment.Center;
//IFont titleFont = workbook.CreateFont();
//titleFont.Boldweight = 200;
//titleFont.FontHeight = 16 * 16;
//titleStyle.SetFont(titleFont);
//titleCell.CellStyle = titleStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, title.Count));
IRow subTitleRow = sheet.CreateRow(1);
ICell subTitleCell = subTitleRow.CreateCell(0);
subTitleCell.SetCellValue(subTitle);
//ICellStyle subTitleStyle = workbook.CreateCellStyle();
//subTitleStyle.Alignment = HorizontalAlignment.Center;
//IFont subTitleFont = workbook.CreateFont();
//subTitleFont.Boldweight = 100;
//subTitleFont.FontHeight = 15 * 15;
//subTitleFont.Color = HSSFColor.Grey80Percent.Index;
//subTitleStyle.SetFont(subTitleFont);
//subTitleCell.CellStyle = subTitleStyle;
sheet.AddMergedRegion(new CellRangeAddress(1, 1, 0, title.Count));
#endregion
#region 初始化表格标题
ICellStyle headerStyle = workbook.CreateCellStyle();
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.FillForegroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillBackgroundColor = HSSFColor.Grey50Percent.Index;
headerStyle.FillPattern = FillPattern.Squares;
IFont headerFont = workbook.CreateFont();
//headerFont.Boldweight = 200;
//headerFont.FontHeight = 15 * 15;
headerFont.Color = HSSFColor.White.Index;
headerStyle.SetFont(headerFont);
ICellStyle valueStyle = workbook.CreateCellStyle();
//valueStyle.Alignment = HorizontalAlignment.Center;
valueStyle.BorderBottom = BorderStyle.Thin;
valueStyle.BorderLeft = BorderStyle.Thin;
valueStyle.BorderRight = BorderStyle.Thin;
valueStyle.BorderTop = BorderStyle.Thin;
//IFont valueFont = workbook.CreateFont();
//valueFont.FontHeight = 15 * 15;
//valueStyle.SetFont(valueFont);
int rowIndex = 3;
IRow headerRow = sheet.CreateRow(rowIndex);
for (int j = 0; j <= title.Count; j++)
{
ICell headerCell = headerRow.CreateCell(j);
if (j == 0)
{
headerCell.SetCellValue("序号");
}
else
{
headerCell.SetCellValue(title[j - 1]);
}
headerCell.CellStyle = headerStyle;
}
#endregion
if (dt.Rows.Count > 0)
{
#region 数据写入
rowIndex += 1;
for (int k = rowRecord; k < dt.Rows.Count; k++)
{
rowRecord = k;
if (rowIndex > 65535)
{
break;
}
IRow dtRow = sheet.CreateRow(rowIndex);
DataRow dr = dt.Rows[k];
for (int l = 0; l <= dt.Columns.Count; l++)
{
for (int cl = 0; cl <= iColumns.Count(); cl++)
{
ICell valueCell = dtRow.CreateCell(cl);
if (cl == 0)
{
valueCell.SetCellValue(k + 1);
}
else
{
string columnValue = dr[iColumns[cl - 1]].ConvertTo <string>();
columnValue = columnValue.IsNullOrEmpty() || dt.Columns[iColumns[cl - 1]].DataType.Name != "DateTime" ? columnValue : DateTime.Parse(columnValue).ToString("yyyy-MM-dd");
valueCell.SetCellValue(columnValue);
}
valueCell.CellStyle = valueStyle;
}
}
rowIndex += 1;
}
#endregion
}
else
{
rowIndex += 1;
IRow dtRow = sheet.CreateRow(rowIndex);
for (int colIndex = 0; colIndex <= title.Count; colIndex++)
{
ICell valueCell = dtRow.CreateCell(colIndex);
if (colIndex == 0)
{
valueCell.SetCellValue("没有数据");
}
else
{
valueCell.SetCellValue("");
}
valueCell.CellStyle = valueStyle;
}
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, 0, title.Count));
}
#region 自动伸展列宽
for (int colIndex = 0; colIndex <= title.Count; colIndex++)
{
sheet.AutoSizeColumn(colIndex);
}
#endregion
//}
return(true);
}
private void PutNames(ISheet ws, ICellStyle cs1, ICellStyle cs2, ICellStyle cs3, params string[] names)
{
PutNamesWithValidations(ws, cs1, cs2, cs3, names.Select(s => new[] { s }).ToArray());
}
/**
* Applies a whole-row cell styling to the row.
* If the value is null then the style information is Removed,
* causing the cell to used the default workbook style.
*/
public void SetRowStyle(ICellStyle style)
{
}
internal void SaveToExcel()
{
int rows = 0;
int tallRow = 14;
int medRow = 12;
IWorkbook wb = new XSSFWorkbook();
IRow row;
ICell cell;
wsheet = wb.CreateSheet("Sheet1");
IFont font;
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
ICellStyle DefaultStyle = wb.CreateCellStyle();
DefaultStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
DefaultStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 14;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle TitleStyle = wb.CreateCellStyle();
TitleStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 12;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle InvoiceStyle = wb.CreateCellStyle();
InvoiceStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 8;
font.FontName = "Arial";
ICellStyle SmallStyle = wb.CreateCellStyle();
SmallStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
ICellStyle MoneyStyle = wb.CreateCellStyle();
MoneyStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
MoneyStyle.SetFont(font);
MoneyStyle.DataFormat = 2;
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle MoneyBoldStyle = wb.CreateCellStyle();
MoneyBoldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
MoneyBoldStyle.SetFont(font);
MoneyBoldStyle.DataFormat = 2;
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle BoldStyle = wb.CreateCellStyle();
BoldStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
font.IsBold = true;
ICellStyle RightAlignBoldStyle = wb.CreateCellStyle();
RightAlignBoldStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
RightAlignBoldStyle.SetFont(font);
font = wb.CreateFont();
font.FontHeight = 10;
font.FontName = "Arial";
ICellStyle RightAlignStyle = wb.CreateCellStyle();
RightAlignStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Right;
RightAlignStyle.SetFont(font);
wsheet.SetColumnWidth(0, CharW * 5);
wsheet.SetColumnWidth(1, CharW * 3);
wsheet.SetColumnWidth(2, CharW * 3);
wsheet.SetColumnWidth(3, CharW * 8);
wsheet.SetColumnWidth(4, CharW * 7);
wsheet.SetColumnWidth(5, CharW * 20);
wsheet.SetColumnWidth(6, CharW * 2 + 100);
wsheet.SetColumnWidth(7, CharW * 2 + 100);
wsheet.SetColumnWidth(8, CharW * 6);
wsheet.SetColumnWidth(9, CharW * 9);
wsheet.SetColumnWidth(10, CharW);
wsheet.DefaultRowHeight = 250;
row = wsheet.CreateRow(rows++);
row.HeightInPoints = 15;
cell = row.CreateCell(0);
cell.CellStyle = TitleStyle;
cell.SetCellValue("GREAT WEST WHOLESALE LTD");
cell = row.CreateCell(8);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(InvoiceID);
cell = row.CreateCell(9);
cell.CellStyle = SmallStyle;
cell.SetCellValue("gst no. R102186178");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(0);
cell.CellStyle = BoldStyle;
cell.SetCellValue("1670 PANDORA ST. VANCOUVER, BC V5L 1L6");
cell = row.CreateCell(9);
cell.CellStyle = InvoiceStyle;
cell.SetCellValue("INVOICE");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("604-255-9588 fax 604-255-9589 1-800-901-9588 [email protected]");
rows++;
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(0);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("Ship to:");
cell = row.CreateCell(3);
cell.CellStyle = BoldStyle;
cell.SetCellValue(customer.StoreName);
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("INVOICE NO.");
cell = row.CreateCell(9);
cell.CellStyle = BoldStyle;
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue(customer.StoreDetails);
}
else
{
cell.SetCellValue(customer.StreetAddress);
}
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("DATE:");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
//Char[] delim = { ' ' };
//String[] date = System.DateTime.Today.ToLongDateString().Split(delim, 2);
//String zz = date[1].ToUpper();
cell.SetCellValue(System.DateTime.Today.ToLongDateString().ToUpper());
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue(customer.StreetAddress);
}
else
{
cell.SetCellValue(customer.CityAddress + ", " + customer.ProvinceAddress + " " + customer.PostalCodeAddress);
}
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("TERMS:");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(customer.PaymentTerms);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue(customer.CityAddress + ", " + customer.ProvinceAddress + " " + customer.PostalCodeAddress);
}
else
{
cell.SetCellValue("tel: " + customer.PhoneNumber);
}
cell = row.CreateCell(6);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("SHIP:");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(customer.ShippingInstructions);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
if (customer.StoreDetails.Length != 0)
{
cell.SetCellValue("tel: " + customer.PhoneNumber);
}
else
{
if (customer.Rep.Length != 0)
{
cell.CellStyle = BoldStyle;
cell.SetCellValue("rep: " + customer.Rep);
}
}
cell = row.CreateCell(6);
cell.CellStyle = BoldStyle;
cell.SetCellValue("PURCHASE ORDER: PO " + PurchaseOrder);
if (customer.StoreDetails.Length != 0)
{
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
if (customer.Rep.Length != 0)
{
cell = row.CreateCell(3);
cell.CellStyle = BoldStyle;
cell.SetCellValue("rep: " + customer.Rep);
}
}
else
{
rows++;
}
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(1);
cell.CellStyle = InvoiceStyle;
cell.SetCellValue(" ** " + InvoiceTemplateNoteDatabase.GetNote());
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(1);
cell.CellStyle = InvoiceStyle;
cell.SetCellValue(" " + customer.StoreSpecialNotes);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("QUANTITY");
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("ITEM NO.");
cell = row.CreateCell(5);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("DESCRIPTION");
cell = row.CreateCell(8);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("COST");
cell = row.CreateCell(9);
cell.CellStyle = DefaultStyle;
cell.SetCellValue("AMOUNT");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = BoldStyle;
cell.SetCellValue("-------------------------------------------------------------------------------------------------------------------");
int itemCount = Items.Count;
/////////////////////////////////////////////////////////////////////////////////////////////////////////
Items = Items.OrderBy(o => o.Location).ToList();
for (int i = 0; i < itemCount; i++)
{
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(0);
cell.CellStyle = RightAlignStyle;
cell.SetCellValue(Items[i].Quantity);
cell = row.CreateCell(9);
cell.CellStyle = MoneyStyle;
cell.SetCellFormula("ROUND(A" + rows + "*I" + rows + ",2)");
if (Items[i].Quantity / Items[i].PerCarton >= 1)
{
cell = row.CreateCell(2);
cell.CellStyle = DefaultStyle;
//cell.SetCellFormula("ROUND(" + Items[i].Quantity + "/" + Items[i].PerCarton + ",2)");
cell.SetCellValue(((Items[i].Quantity * 1.0) / (Items[i].PerCarton * 1.0)).ToString("0.0"));
}
cell = row.CreateCell(3);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].ItemNo);
cell = row.CreateCell(4);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].Location);
cell = row.CreateCell(5);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].ItemDesc);
cell = row.CreateCell(7);
cell.CellStyle = RightAlignStyle;
cell.SetCellValue(Items[i].PerCarton);
cell = row.CreateCell(8);
cell.CellStyle = MoneyStyle;
cell.SetCellValue(Items[i].SellPrice);
if (Items[i].SpecialNotes.Length != 0)
{
cell = row.CreateCell(11);
cell.CellStyle = DefaultStyle;
cell.SetCellValue(Items[i].SpecialNotes);
}
}
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(9);
cell.CellStyle = RightAlignBoldStyle;
cell.SetCellValue("---------------");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
cell.SetCellFormula(String.Format("SUM(J15:J" + (rows - 2) + ")"));
ProvinceTax pt = ProvinceTaxDatabase.GetProvinceByName(customer.Province);
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(5);
cell.CellStyle = BoldStyle;
cell.SetCellValue("GST " + pt.gst + "%");
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
cell.SetCellFormula("ROUND(J" + (rows - 1) + "*" + (pt.gst / 100.0) + ",2)");
//cell.SetCellFormula(String.Format("J" + (rows - 1) + " * " + (pt.gst/ 100.0)));
if (pt.pst != 0)
{
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(5);
cell.CellStyle = BoldStyle;
cell.SetCellValue("PST " + pt.pst + "%");
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
cell.SetCellFormula("ROUND(J" + (rows - 2) + "*" + (pt.pst / 100.0) + ",2)");
//cell.SetCellFormula(String.Format("J" + (rows - 2) + " * " + (pt.pst / 100.0)));
}
row = wsheet.CreateRow(rows++);
row.HeightInPoints = medRow;
cell = row.CreateCell(9);
cell.CellStyle = RightAlignBoldStyle;
cell.SetCellValue("---------------");
row = wsheet.CreateRow(rows++);
row.HeightInPoints = tallRow;
cell = row.CreateCell(5);
cell.CellStyle = BoldStyle;
cell.SetCellValue("INVOICE TOTAL");
cell = row.CreateCell(9);
cell.CellStyle = MoneyBoldStyle;
if (pt.pst != 0)
{
cell.SetCellFormula(String.Format("SUM(J" + (rows - 4) + ":J" + (rows - 2) + ")"));
}
else
{
cell.SetCellFormula(String.Format("SUM(J" + (rows - 3) + ":J" + (rows - 2) + ")"));
}
int rowEnd = 400;
int lastColumn = 25;
IRow r;
for (int rowNum = 0; rowNum < rows; rowNum++)
{
if ((r = wsheet.GetRow(rowNum)) != null)
{
for (int cn = 0; cn < lastColumn; cn++)
{
if ((cell = r.GetCell(cn)) == null)
{
cell = r.CreateCell(cn);
cell.CellStyle = DefaultStyle;
}
}
}
else
{
r = wsheet.CreateRow(rowNum); //getRow(rowNum);
r.HeightInPoints = medRow;
for (int cn = 0; cn < lastColumn; cn++)
{
cell = r.CreateCell(cn);
cell.CellStyle = DefaultStyle;
}
}
}
for (int rowNum = rows; rowNum < rowEnd; rowNum++)
{
r = wsheet.CreateRow(rowNum); //getRow(rowNum);
r.HeightInPoints = medRow;
for (int cn = 0; cn < lastColumn; cn++)
{
cell = r.CreateCell(cn);
cell.CellStyle = DefaultStyle;
}
}
string deskPath = Environment.GetFolderPath(Environment.SpecialFolder.Desktop);
FileStream stream = new FileStream(@"" + deskPath + "\\Invoices\\Progress\\" + InvoiceID + customer.StoreName + ".xlsx", FileMode.Create, FileAccess.Write);
wb.Write(stream);
FileStream stream2 = new FileStream(@"" + deskPath + "\\Invoices\\Original\\" + InvoiceID + customer.StoreName + ".xlsx", FileMode.Create, FileAccess.Write);
wb.Write(stream2);
PrintExcel();
}
static void WriteFlnaAnalysis(FlnaAnalysis analysis, ISheet sheet, int rowIndex, ICellStyle cellDateStyle)
{
var row = CreateAnalysisRow(analysis, sheet, rowIndex, cellDateStyle);
int i = 2;
foreach (var p in FlnaAnalysis.DoubleProperties.Keys.ToList())
{
row.CreateCell(i, CellType.Numeric).SetCellValue((double)Interaction.CallByName(analysis, FlnaAnalysis.DoubleProperties[p].Name, CallType.Get));
i += 1;
}
}
/// <summary>
/// DataTable生成Excel
/// </summary>
/// <param name="dt">数据表</param>
/// <param name="strExcelFileName">物理路径 + 文件名称 + 格式</param>
/// <returns>返回生成状态</returns>
public static bool DataTableToExcel(DataTable dt, string strExcelFileName)
{
try
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet("Sheet1");
//标题样式
ICellStyle HeadercellStyle = workbook.CreateCellStyle();
HeadercellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
HeadercellStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
HeadercellStyle.VerticalAlignment = VerticalAlignment.Center;
//字体
NPOI.SS.UserModel.IFont headerfont = workbook.CreateFont();
headerfont.Boldweight = (short)FontBoldWeight.Bold;
HeadercellStyle.SetFont(headerfont);
//用column name 作为列名
int icolIndex = 0;
IRow headerRow = sheet.CreateRow(0);
headerRow.Height = 20 * 22;
foreach (DataColumn item in dt.Columns)
{
ICell cell = headerRow.CreateCell(icolIndex);
cell.SetCellValue(item.ColumnName);
cell.CellStyle = HeadercellStyle;
icolIndex++;
}
//单元格样式
ICellStyle cellStyle = workbook.CreateCellStyle();
//为避免日期格式被Excel自动替换，所以设定 format 为 『@』 表示一率当成text來看
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("@");
cellStyle.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
cellStyle.VerticalAlignment = VerticalAlignment.Center;
NPOI.SS.UserModel.IFont cellfont = workbook.CreateFont();
cellfont.Boldweight = (short)FontBoldWeight.Normal;
cellStyle.SetFont(cellfont);
//建立内容行
int iRowIndex = 1;
int iCellIndex = 0;
foreach (DataRow Rowitem in dt.Rows)
{
IRow DataRow = sheet.CreateRow(iRowIndex);
DataRow.Height = 20 * 16;
foreach (DataColumn Colitem in dt.Columns)
{
ICell cell = DataRow.CreateCell(iCellIndex);
cell.SetCellValue(Rowitem[Colitem].ToString());
cell.CellStyle = cellStyle;
iCellIndex++;
}
iCellIndex = 0;
iRowIndex++;
}
//自适应列宽度
for (int i = 0; i < icolIndex; i++)
{
sheet.AutoSizeColumn(i);
}
//写Excel
FileStream file = new FileStream(strExcelFileName, FileMode.OpenOrCreate);
workbook.Write(file);
file.Flush();
file.Close();
return(true);
}
catch (Exception)
{
return(false);
}
}
/// <summary>
/// DataTable导出到Excel文件(*.xlsx)
/// </summary>
/// <param name="file">文件名</param>
/// <param name="dataTable">DataTable数据源</param>
/// <param name="fields">字段模型</param>
/// <param name="header">Excel表头文本（例如：车辆列表）</param>
/// <param name="sheet">文件名</param>
/// <returns>导出成功返回true，失败返回false</returns>
private bool SaveXlsx(string file, DataTable dataTable, List <FieldModel> fields = null, string header = "", string sheet = "Sheet1")
{
if (dataTable == null)
{
return(false);
}
file = file.ToFullName(Dir);
bool bl = false;
XSSFWorkbook wk = new XSSFWorkbook();
try
{
ISheet isheet = wk.CreateSheet(sheet);
#region 右击文件 属性信息
{
DocumentSummaryInformation dsi = PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "MM";
SummaryInformation si = PropertySetFactory.CreateSummaryInformation();
si.Author = "超级美眉工作室"; //填加xls文件作者信息
si.ApplicationName = "MM"; //填加xls文件创建程序信息
si.LastAuthor = "邱文武"; //填加xls文件最后保存者信息
si.Comments = "这是使用超级美眉的类库导出的Excel文件"; //填加xls文件作者信
si.Title = "超级美眉Excel"; //填加xls文件标题信息
si.Subject = "超级美眉专用主题"; //填加文件主题信息
si.CreateDateTime = DateTime.Now;
}
#endregion
ICellStyle dateStyle = wk.CreateCellStyle();
IDataFormat format = wk.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd HH:mm:ss");
//取得列宽
int[] arrColWidth = new int[dataTable.Columns.Count];
foreach (DataColumn item in dataTable.Columns)
{
arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length;
}
for (int i = 0; i < dataTable.Rows.Count; i++)
{
for (int j = 0; j < dataTable.Columns.Count; j++)
{
int intTemp = Encoding.GetEncoding(936).GetBytes(dataTable.Rows[i][j].ToString()).Length;
if (intTemp > arrColWidth[j])
{
arrColWidth[j] = intTemp;
}
}
}
int rowIndex = 0;
foreach (DataRow row in dataTable.Rows)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
isheet = wk.CreateSheet(sheet);
isheet.IsPrintGridlines = false;
}
// 表头及样式
if (!string.IsNullOrEmpty(header))
{
IRow headerRow = isheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(header);
ICellStyle headStyle = wk.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = wk.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
isheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(0, 0, 0, dataTable.Columns.Count - 1));
rowIndex++;
}
//设置页头
IRow twoRow = isheet.CreateRow(rowIndex);
ICellStyle style = wk.CreateCellStyle();
IFont fontB = wk.CreateFont();
//fontB.FontHeightInPoints = 12;
fontB.Boldweight = 700;
style.SetFont(fontB);
style.Alignment = HorizontalAlignment.Center;
twoRow.RowStyle = style;
foreach (DataColumn column in dataTable.Columns)
{
ICell cell = twoRow.CreateCell(column.Ordinal);
if (fields == null)
{
cell.SetCellValue(column.ColumnName);
}
else
{
var colName = GetColName(fields, column.ColumnName);
cell.SetCellValue(colName);
}
cell.CellStyle = style;
//设置列宽
isheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256);
}
rowIndex++;
}
#endregion
#region 填充内容
IRow dataRow = isheet.CreateRow(rowIndex);
foreach (DataColumn column in dataTable.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime.TryParse(drValue, out DateTime dateV);
newCell.SetCellValue(dateV);
newCell.CellStyle = dateStyle;
break;
case "System.Boolean": //布尔型
bool.TryParse(drValue, out bool boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int.TryParse(drValue, out int intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double.TryParse(drValue, out double doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
#endregion
rowIndex++;
}
using (FileStream sw = File.Create(file))
{
wk.Write(sw);
sheet = null;
bl = true;
}
}
catch (Exception ex)
{
Ex = ex.Message;
}
finally
{
if (wk != null)
{
wk.Close();
}
}
return(bl);
}
/**
* Clones all the style information from another
* XSSFCellStyle, onto this one. This
* XSSFCellStyle will then have all the same
* properties as the source, but the two may
* be edited independently.
* Any stylings on this XSSFCellStyle will be lost!
*
* The source XSSFCellStyle could be from another
* XSSFWorkbook if you like. This allows you to
* copy styles from one XSSFWorkbook to another.
*/
public void CloneStyleFrom(ICellStyle source)
{
if (source is XSSFCellStyle)
{
XSSFCellStyle src = (XSSFCellStyle)source;
// Is it on our Workbook?
if (src._stylesSource == _stylesSource)
{
// Nice and easy
_cellXf = src.GetCoreXf();
_cellStyleXf = src.GetStyleXf();
}
else
{
// Copy the style
try
{
// Remove any children off the current style, to
// avoid orphaned nodes
if (_cellXf.IsSetAlignment())
_cellXf.UnsetAlignment();
if (_cellXf.IsSetExtLst())
_cellXf.UnsetExtLst();
// Create a new Xf with the same contents
_cellXf =
src.GetCoreXf().Copy();
// Swap it over
_stylesSource.ReplaceCellXfAt(_cellXfId, _cellXf);
}
catch (XmlException e)
{
throw new POIXMLException(e);
}
// Copy the format
String fmt = src.GetDataFormatString();
DataFormat=(
(new XSSFDataFormat(_stylesSource)).GetFormat(fmt)
);
// Copy the font
try
{
CT_Font ctFont =
src.GetFont().GetCTFont().Clone();
XSSFFont font = new XSSFFont(ctFont);
font.RegisterTo(_stylesSource);
SetFont(font);
}
catch (XmlException e)
{
throw new POIXMLException(e);
}
}
// Clear out cached details
_font = null;
_cellAlignment = null;
}
else
{
throw new ArgumentException("Can only clone from one XSSFCellStyle to another, not between HSSFCellStyle and XSSFCellStyle");
}
}
/// <summary>
///
/// </summary>
/// <param name="sheet"></param>
/// <param name="rowIndex"></param>
/// <param name="row"></param>
private static void FillData(ISheet sheet, int rowIndex, DataRow row)
{
ICellStyle contentStyle = sheet.Workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex);
foreach (DataColumn column in row.Table.Columns)
{
ICell newCell = dataRow.CreateCell(column.Ordinal);
newCell.CellStyle = contentStyle;
string drValue = row[column].ToString();
switch (column.DataType.ToString())
{
case "System.String": //字符串类型
newCell.SetCellValue(drValue);
break;
case "System.DateTime": //日期类型
DateTime dateV;
DateTime.TryParse(drValue, out dateV);
newCell.SetCellValue(dateV);
IDataFormat format = sheet.Workbook.CreateDataFormat();
ICellStyle dateStyle = sheet.Workbook.CreateCellStyle();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean": //布尔型
bool boolV;
bool.TryParse(drValue, out boolV);
newCell.SetCellValue(boolV);
break;
case "System.Int16": //整型
case "System.Int32":
case "System.Int64":
case "System.Byte":
int intV;
int.TryParse(drValue, out intV);
newCell.SetCellValue(intV);
break;
case "System.Decimal": //浮点型
case "System.Double":
double doubV;
double.TryParse(drValue, out doubV);
newCell.SetCellValue(doubV);
break;
case "System.DBNull": //空值处理
newCell.SetCellValue("");
break;
default:
newCell.SetCellValue("");
break;
}
}
}
