public double? Calculate(IWorkbook book)
{
var func = _calc.Build(this.StringExpressionPart);
var paramDict = this.Variables.ToDictionary(k => k.Key, v => GetCellValue(v.Value, book));
this.Result = func.Invoke(paramDict);
return this.Result;
}
private ICellStyle GetBaseStyle(IWorkbook workbook)
{
var style = workbook.CreateCellStyle();
style.BorderBottom = style.BorderLeft = style.BorderRight = style.BorderTop = BorderStyle.Thin;
style.FillPattern = FillPattern.SolidForeground;
return style;
}
private void Init()
{
#region//初始化信息
try
{
using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read))
{
if (filePath.Trim().ToLower().EndsWith(".xls"))
{
hssfworkbook = new HSSFWorkbook(file);
}
else if (filePath.Trim().ToLower().EndsWith(".xlsx"))
{
hssfworkbook = new XSSFWorkbook(file);
}
else if (filePath.Trim().ToLower().EndsWith(".csv"))
{
hssfworkbook = new XSSFWorkbook(file);
}
}
}
catch (Exception e)
{
throw e;
}
#endregion
}
static void SetValueAndFormat(IWorkbook workbook, ICell cell, double value, short formatId)
{
cell.SetCellValue(value);
ICellStyle cellStyle = workbook.CreateCellStyle();
cellStyle.DataFormat = formatId;
cell.CellStyle = cellStyle;
}
public static IWorkbook WriteOutAndReadBack(IWorkbook wb)
{
IWorkbook result;
try
{
using (MemoryStream baos = new MemoryStream(8192))
{
wb.Write(baos);
using (Stream is1 = new MemoryStream(baos.ToArray()))
{
if (wb is HSSFWorkbook)
{
result = new HSSFWorkbook(is1);
}
else if (wb is XSSFWorkbook)
{
result = new XSSFWorkbook(is1);
}
else
{
throw new RuntimeException("Unexpected workbook type ("
+ wb.GetType().Name + ")");
}
}
}
}
catch (IOException e)
{
throw new RuntimeException(e);
}
return result;
}
internal static ICell CreateCell(IWorkbook workbook, IRow row, int column, decimal? content, bool isCentered)
{
ICellStyle style = workbook.CreateCellStyle();
ICell cell = row.CreateCell(column);
if (content == null)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(Convert.ToDouble(content.Value));
}
if (isCentered)
{
style.Alignment = HorizontalAlignment.Center;
}
style.BorderBottom = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
cell.CellStyle = style;
return cell;
}
public byte[] WriteStream()
{
var ms = new MemoryStream();
_workbook.Write(ms);
_workbook = null;
return ms.ToArray();
}
internal WorkSheet(IWorkbook bookHandler, ISheet sheetHandler)
{
this.bookHandler = bookHandler;
this.sheetHandler = sheetHandler;
this.Cells = new CellCollection() { Sheet = this };
}
private void buildWorkbook(IWorkbook wb)
{
ISheet sh = wb.CreateSheet();
IRow row1 = sh.CreateRow(0);
IRow row2 = sh.CreateRow(1);
row3 = sh.CreateRow(2);
row1.CreateCell(0, CellType.Numeric);
row1.CreateCell(1, CellType.Numeric);
row2.CreateCell(0, CellType.Numeric);
row2.CreateCell(1, CellType.Numeric);
row3.CreateCell(0);
row3.CreateCell(1);
CellReference a1 = new CellReference("A1");
CellReference a2 = new CellReference("A2");
CellReference b1 = new CellReference("B1");
CellReference b2 = new CellReference("B2");
sh.GetRow(a1.Row).GetCell(a1.Col).SetCellValue(35);
sh.GetRow(a2.Row).GetCell(a2.Col).SetCellValue(0);
sh.GetRow(b1.Row).GetCell(b1.Col).CellFormula = (/*setter*/"A1/A2");
sh.GetRow(b2.Row).GetCell(b2.Col).CellFormula = (/*setter*/"NA()");
Evaluator = wb.GetCreationHelper().CreateFormulaEvaluator();
}
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
this.fs = new FileStream(this.fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (this.fileName.IndexOf(".xlsx") > 0)
{
this.workbook = new XSSFWorkbook();
}
else
{
if (this.fileName.IndexOf(".xls") > 0)
{
this.workbook = new HSSFWorkbook();
}
}
int result;
try
{
if (this.workbook != null)
{
ISheet sheet = this.workbook.CreateSheet(sheetName);
int num;
if (isColumnWritten)
{
IRow row = sheet.CreateRow(0);
for (int i = 0; i < data.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(data.Columns[i].ColumnName);
}
num = 1;
}
else
{
num = 0;
}
this.rowCount = data.Rows.Count;
this.cellCount = data.Columns.Count;
for (int j = 0; j < data.Rows.Count; j++)
{
IRow row = sheet.CreateRow(num);
for (int i = 0; i < data.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(data.Rows[j][i].ToString());
}
num++;
}
this.workbook.Write(this.fs);
result = num;
}
else
{
result = -1;
}
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
result = -1;
}
return result;
}
/// <summary>
/// To insert comment in active sheet
/// </summary>
/// <param name="workbook">Instance of IWorkbook</param>
/// <param name="cellAddress">Address of cell</param>
/// <param name="comment">Comment string</param>
/// <param name="clearOldComments">True/False. True to clear old comments</param>
internal static void AddComment(IWorkbook workbook, string cellAddress, string comment, bool clearOldComments)
{
SpreadsheetGear.IRange Cell;
SpreadsheetGear.IComment CellComment;
try
{
// Get a reference to a cell.
Cell = workbook.ActiveWorksheet.Cells[cellAddress];
// If the cell has no comment, add one.
if (Cell.Comment != null & clearOldComments)
{
Cell.ClearComments();
}
CellComment = Cell.AddComment(comment);
// Turn off the default bold font.
CellComment.Shape.TextFrame.Characters.Font.Bold = false;
//CellComment.Visible = true;
}
catch (Exception ex)
{
// new ApplicationException(ex.ToString());
}
}
private void Confirm(IWorkbook wb)
{
ISheet sheet = wb.CreateSheet("new sheet");
cell11 = sheet.CreateRow(0).CreateCell(0);
cell11.SetCellType(CellType.Formula);
Confirm("PROPER(\"hi there\")", "Hi There");
Confirm("PROPER(\"what's up\")", "What'S Up");
Confirm("PROPER(\"I DON'T TH!NK SO!\")", "I Don'T Th!Nk So!");
Confirm("PROPER(\"dr\u00dcb\u00f6'\u00e4 \u00e9lo\u015f|\u00eb\u00e8 \")", "Dr\u00fcb\u00f6'\u00c4 \u00c9lo\u015f|\u00cb\u00e8 ");
Confirm("PROPER(\"hi123 the123re\")", "Hi123 The123Re");
Confirm("PROPER(\"-\")", "-");
Confirm("PROPER(\"!\u00a7$\")", "!\u00a7$");
Confirm("PROPER(\"/&%\")", "/&%");
// also test longer string
StringBuilder builder = new StringBuilder("A");
StringBuilder expected = new StringBuilder("A");
for (int i = 1; i < 254; i++)
{
builder.Append((char)(65 + (i % 26)));
expected.Append((char)(97 + (i % 26)));
}
Confirm("PROPER(\"" + builder.ToString() + "\")", expected.ToString());
}
public void AssertDataValidation(IWorkbook wb)
{
MemoryStream baos = new MemoryStream(22000);
try
{
wb.Write(baos);
baos.Close();
}
catch (IOException e)
{
throw new RuntimeException(e);
}
byte[] generatedContent = baos.ToArray();
#if !HIDE_UNREACHABLE_CODE
bool isSame;
if (false)
{
// TODO - add proof spreadsheet and compare
Stream proofStream = HSSFTestDataSamples.OpenSampleFileStream("TestDataValidation.xls");
isSame = CompareStreams(proofStream, generatedContent);
}
isSame = true;
if (isSame)
{
return;
}
//File tempDir = new File(System.GetProperty("java.io.tmpdir"));
string tempDir = Path.GetTempFileName();
//File generatedFile = new File(tempDir, "GeneratedTestDataValidation.xls");
try
{
FileStream fileOut = new FileStream(tempDir, FileMode.Create);
fileOut.Write(generatedContent, 0, generatedContent.Length);
fileOut.Close();
}
catch (IOException e)
{
throw new RuntimeException(e);
}
Console.WriteLine("This Test case has failed because the generated file differs from proof copy '"
); // TODO+ proofFile.AbsolutePath + "'.");
Console.WriteLine("The cause is usually a change to this Test, or some common spreadsheet generation code. "
+ "The developer has to decide whether the Changes were wanted or unwanted.");
Console.WriteLine("If the Changes to the generated version were unwanted, "
+ "make the fix elsewhere (do not modify this Test or the proof spreadsheet to Get the Test working).");
Console.WriteLine("If the Changes were wanted, make sure to open the newly generated file in Excel "
+ "and verify it manually. The new proof file should be submitted After it is verified to be correct.");
Console.WriteLine("");
Console.WriteLine("One other possible (but less likely) cause of a failed Test is a problem in the "
+ "comparison logic used here. Perhaps some extra file regions need to be ignored.");
Console.WriteLine("The generated file has been saved to '" + tempDir + "' for manual inspection.");
Assert.Fail("Generated file differs from proof copy. See sysout comments for details on how to fix.");
#endif
}
static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
{
sheet.SetColumnWidth(0, 6000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 3600);
sheet.SetColumnWidth(3, 3600);
sheet.SetColumnWidth(4, 2400);
sheet.SetColumnWidth(5, 2400);
sheet.SetColumnWidth(6, 2400);
sheet.SetColumnWidth(7, 2400);
sheet.SetColumnWidth(8, 2400);
IRow row = sheet.CreateRow(0);
ICellStyle style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
WriteHeaderCell(row, 0, "Raw Long Bits A", style);
WriteHeaderCell(row, 1, "Raw Long Bits B", style);
WriteHeaderCell(row, 2, "Value A", style);
WriteHeaderCell(row, 3, "Value B", style);
WriteHeaderCell(row, 4, "Exp Cmp", style);
WriteHeaderCell(row, 5, "LT", style);
WriteHeaderCell(row, 6, "EQ", style);
WriteHeaderCell(row, 7, "GT", style);
WriteHeaderCell(row, 8, "Check", style);
}
private ICellStyle BorderAndFontSetting(IWorkbook wb, Tk5FieldInfoEx metaInfo, Model model)
{
ICellStyle cellStyle = wb.CreateCellStyle();
if (fUseBorder)
{
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
}
if (model == Model.Content)
{
IFont fontContent = ExcelUtil.FontSetting(wb, fContent);
cellStyle.SetFont(fontContent);
if (metaInfo.Extension != null)
{
ExcelUtil.AlignSetting(metaInfo.Extension.Align, cellStyle);
}
else
{
ExcelUtil.AlignSetting(fContent.Align, cellStyle);
}
}
else
{
ExcelUtil.AlignSetting(fHeader.Align, cellStyle);
IFont fontHeader = ExcelUtil.FontSetting(wb, fHeader);
cellStyle.SetFont(fontHeader);
}
return cellStyle;
}
public CellStyleCache(IWorkbook xlWorkbook)
{
this.xlWorkbook = xlWorkbook;
this.cellStyles = new Dictionary<string, CellStyleWrapper>();
this.cellStyle = xlWorkbook.CreateCellStyle();
this.defaultCellStyle = xlWorkbook.CreateCellStyle();
}
private void FillData(IWorkbook p_wb)
{
ISheet sheet = p_wb.CreateSheet("sheet123");
sheet.RowSumsBelow = (/*setter*/false);
int i;
for (i = 0; i < ROWS_NUMBER; i++)
{
IRow row = sheet.CreateRow(i);
ICell cell = row.CreateCell(0);
cell.SetCellValue(i + 1);
}
i = 1;
while (i < ROWS_NUMBER)
{
int end = i + (GROUP_SIZE - 2);
int start = i; // natural order
// int start = end - 1; // reverse order
while (start < end)
{ // natural order
// while (start >= i) { // reverse order
sheet.GroupRow(start, end);
//o_groupsNumber++;
bool collapsed = IsCollapsed();
//System.out.Println("Set group " + start + "->" + end + " to " + collapsed);
sheet.SetRowGroupCollapsed(start, collapsed);
start++; // natural order
// start--; // reverse order
}
i += GROUP_SIZE;
}
}
public CheckExpression(string expression, IWorkbook workbook)
{
this.StringExpression = expression;
this.Workbook = workbook;
this.Cells = new List<Cell>();
this.ErrorMessages = new List<string>();
}
/// <summary>
/// Initializes a new instance of the <see cref="BetListExcelBuilder" /> class.
/// </summary>
public BetListExcelBuilder()
{
_workbook = new HSSFWorkbook();
_isDisposed = false;
OddCellStyle = _workbook.CreateCellStyle();
EvenCellStyle = _workbook.CreateCellStyle();
OddCellStyleCenterAligned = _workbook.CreateCellStyle();
EvenCellStyleCenterAligned = _workbook.CreateCellStyle();
_nextCellIndex = -1;
RTFHelper = new RTFHelper()
{
NegFont = _workbook.CreateFont(),
NormalFont = _workbook.CreateFont(),
PosFont = _workbook.CreateFont(),
NegFontCrossed = _workbook.CreateFont(),
NormalFontCrossed = _workbook.CreateFont(),
PosFontCrossed = _workbook.CreateFont(),
RTFRenderer = new RtfTextRender()
};
InitDefaultRTFHelper();
InitDefaultCellStyles();
}
/// <summary>
/// Create Series that references data in Excel
/// </summary>
public SpreadsheetGearSeries(string filename, string sheetName,
string dateColumn, string valueColumn)
: base()
{
workbook = SpreadsheetGearExcel.GetWorkbookReference(filename);
Init(workbook, sheetName, dateColumn, valueColumn,false,"","",this.units);
}
public bool Init()
{
if (!File.Exists(m_StrategyName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
Row.CreateCell(0).SetCellValue("-500");
Row.CreateCell(1).SetCellValue("-450");
Row.CreateCell(2).SetCellValue("-400");
Row.CreateCell(3).SetCellValue("-350");
Row.CreateCell(4).SetCellValue("-300");
Row.CreateCell(5).SetCellValue("-250");
Row.CreateCell(6).SetCellValue("-200");
Row.CreateCell(7).SetCellValue("-150");
Row.CreateCell(8).SetCellValue("-100");
Row.CreateCell(9).SetCellValue("-50");
Row.CreateCell(10).SetCellValue("0");
Row.CreateCell(11).SetCellValue("50");
Row.CreateCell(12).SetCellValue("100");
Row.CreateCell(13).SetCellValue("150");
Row.CreateCell(14).SetCellValue("200");
Row.CreateCell(15).SetCellValue("250");
Row.CreateCell(16).SetCellValue("300");
Row.CreateCell(17).SetCellValue("350");
Row.CreateCell(18).SetCellValue("400");
Row.CreateCell(19).SetCellValue("450");
Row.CreateCell(20).SetCellValue("500");
return true;
}
return false;
}
public void TestMissingWorkbookMissingOverride()
{
mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME);
ISheet lSheet = mainWorkbook.GetSheetAt(0);
ICell lA1Cell = lSheet.GetRow(0).GetCell(0);
ICell lB1Cell = lSheet.GetRow(1).GetCell(0);
ICell lC1Cell = lSheet.GetRow(2).GetCell(0);
Assert.AreEqual(CellType.Formula, lA1Cell.CellType);
Assert.AreEqual(CellType.Formula, lB1Cell.CellType);
Assert.AreEqual(CellType.Formula, lC1Cell.CellType);
// Check cached values
Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(true, lC1Cell.BooleanCellValue);
// Evaluate
IFormulaEvaluator evaluator = mainWorkbook.GetCreationHelper().CreateFormulaEvaluator();
evaluator.IgnoreMissingWorkbooks = (true);
Assert.AreEqual(CellType.Numeric, evaluator.EvaluateFormulaCell(lA1Cell));
Assert.AreEqual(CellType.String, evaluator.EvaluateFormulaCell(lB1Cell));
Assert.AreEqual(CellType.Boolean, evaluator.EvaluateFormulaCell(lC1Cell));
Assert.AreEqual(10.0d, lA1Cell.NumericCellValue, 0.00001d);
Assert.AreEqual("POI rocks!", lB1Cell.StringCellValue);
Assert.AreEqual(true, lC1Cell.BooleanCellValue);
}
public SheetWriter(IWorkbook wb)
{
ISheet sheet = wb.CreateSheet("Sheet1");
WriteHeaderRow(wb, sheet);
_sheet = sheet;
_rowIndex = 1;
}
public static int LoadImage(string path, IWorkbook wb)
{
FileStream file = new FileStream(path, FileMode.Open, FileAccess.Read);
byte[] buffer = new byte[file.Length];
file.Read(buffer, 0, (int)file.Length);
return wb.AddPicture(buffer, PictureType.JPEG);
}
/// <summary>
/// Create Series that references data in Excel
/// </summary>
public SpreadsheetGearSeries(string filename, string sheetName,
string dateColumn, string valueColumn, bool waterYearFormat,string siteColumn="",
string siteFilter="",string units="")
: base()
{
workbook = SpreadsheetGearExcel.GetWorkbookReference(filename);
Init(workbook, sheetName, dateColumn, valueColumn,waterYearFormat,siteColumn,siteFilter,units);
}
public IWorkbook WriteOutAndReadBack(IWorkbook original)
{
if (!(original is XSSFWorkbook))
{
throw new ArgumentException("Expected an instance of XSSFWorkbook, but had " + original.GetType().Name);
}
return XSSFTestDataSamples.WriteOutAndReadBack((XSSFWorkbook)original);
}
public static XSSFEvaluationWorkbook Create(IWorkbook book)
{
if (book == null)
{
return null;
}
return new XSSFEvaluationWorkbook(book);
}
public void WriteFile(string filePath)
{
using (var fs = new FileStream(filePath, FileMode.Create))
{
_workbook.Write(fs);
}
_workbook = null;
}
protected virtual void SetUp()
{
mainWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(MAIN_WORKBOOK_FILENAME);
sourceWorkbook = HSSFTestDataSamples.OpenSampleWorkbook(SOURCE_WORKBOOK_FILENAME);
Assert.IsNotNull(mainWorkbook);
Assert.IsNotNull(sourceWorkbook);
}
private void CloseAndCleanUp()
{
mSheet = null;
mWorkbook = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
#region Workbook initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
IList <Brands> list = GetVehicleDetails();
ExcelImportDataOptions importDataOptions = new ExcelImportDataOptions();
importDataOptions.FirstRow = 4;
if (comboBox1.SelectedIndex == 0)
{
importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Default;
}
else if (comboBox1.SelectedIndex == 1)
{
importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Merge;
}
else if (comboBox1.SelectedIndex == 2)
{
importDataOptions.NestedDataLayoutOptions = ExcelNestedDataLayoutOptions.Repeat;
}
if (checkbox1.IsChecked.Value)
{
if (rdbExpand.IsChecked.Value)
{
importDataOptions.NestedDataGroupOptions = ExcelNestedDataGroupOptions.Expand;
}
else if (rdbCollapse.IsChecked.Value)
{
importDataOptions.NestedDataGroupOptions = ExcelNestedDataGroupOptions.Collapse;
if (levelTextBox.Text != string.Empty)
{
importDataOptions.CollapseLevel = int.Parse(levelTextBox.Text);
}
}
}
worksheet.ImportData(list, importDataOptions);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 16;
pageHeader.Font.Bold = true;
pageHeader.Color = Windows.UI.Color.FromArgb(0, 146, 208, 80);
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Bold = true;
tableHeader.Font.FontName = "Calibri";
tableHeader.Color = Windows.UI.Color.FromArgb(0, 146, 208, 80);
#endregion
#region Apply Styles
// Apply style for header
worksheet["A1:C2"].Merge();
worksheet["A1"].Text = "Automobile Brands in the US";
worksheet["A1"].CellStyle = pageHeader;
worksheet["A4:C4"].CellStyle = tableHeader;
worksheet.Columns[0].ColumnWidth = 10;
worksheet.Columns[1].ColumnWidth = 20;
worksheet.Columns[2].ColumnWidth = 25;
#endregion
#endregion
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "ImportData";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("ImportData.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been saved successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
/// <summary>
/// 解析单元格样式
/// </summary>
/// <param name="workbook">工作薄</param>
/// <param name="style">单元格样式</param>
public static ICellStyle Resolve(IWorkbook workbook, CellStyle style)
{
return(new CellStyleResolver(workbook, style).Resolve());
}
/// <summary>
/// 将文件流读取到DataTable数据表中
/// </summary>
/// <param name="fileStream">文件流</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadStreamToDataTable(Stream fileStream, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
//根据文件流创建excel数据结构,NPOI的工厂类WorkbookFactory会自动识别excel版本，创建出不同的excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fileStream);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null || row.FirstCellNum < 0)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
//同理，没有数据的单元格都默认是null
ICell cell = row.GetCell(j);
if (cell != null)
{
if (cell.CellType == CellType.Numeric)
{
//判断是否日期类型
if (DateUtil.IsCellDateFormatted(cell))
{
dataRow[j] = row.GetCell(j).DateCellValue;
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();
}
}
else
{
dataRow[j] = row.GetCell(j).ToString().Trim();
}
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// Convert the Excel document to HTML
/// </summary>
/// <returns>Return the created excel document as stream</returns>
public MemoryStream WorksheetToHTMLXlsIO(string button, string option)
{
if (button == "Input Template")
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//An existing workbook is opened
FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-template.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
//Save the created Excel document to MemoryStream
workbook.SaveAs(stream);
return(stream);
}
}
}
else
{
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine
using (ExcelEngine excelEngine = new ExcelEngine())
{
//Step 2 : Instantiate the excel application object
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
//An existing workbook is opened
FileStream inputStream = new FileStream(ResolveApplicationPath("northwind-template.xlsx"), FileMode.Open, FileAccess.Read);
IWorkbook workbook = application.Workbooks.Open(inputStream);
//The first worksheet object in the worksheets collection is accessed
IWorksheet worksheet = workbook.Worksheets[0];
//Save the document as a stream and retrun the stream
using (MemoryStream stream = new MemoryStream())
{
if (option == "Workbook")
{
//Save the created HTML document to MemoryStream
workbook.SaveAsHtml(stream);
}
else
{
//Save the created HTML document to MemoryStream
worksheet.SaveAsHtml(stream);
}
return(stream);
}
}
}
}
private async void btnGenerateExcel_Click(object sender, RoutedEventArgs e)
{
StorageFile storageFile;
string fileName = "BoxAndWhisker_Chart.xlsx";
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = fileName;
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("BoxAndWhisker_Chart.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile == null)
{
return;
}
#region Initializing Workbook
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the Excel application object.
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
Assembly assembly = typeof(BoxAndWhisker).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.BoxAndWhiskerTemplate.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
#endregion
#region Chart Creation
IChart chart = null;
if (this.rdBtnSheet.IsChecked != null && this.rdBtnSheet.IsChecked.Value)
{
chart = workbook.Charts.Add();
}
else
{
chart = workbook.Worksheets[0].Charts.Add();
}
#region Box and Whisker Chart Settings
chart.ChartType = ExcelChartType.BoxAndWhisker;
chart.DataRange = sheet["B1:C181"];
IChartSerie series = chart.Series[0];
series.SerieFormat.ShowInnerPoints = false;
series.SerieFormat.ShowOutlierPoints = true;
series.SerieFormat.ShowMeanMarkers = true;
series.SerieFormat.ShowMeanLine = false;
series.SerieFormat.QuartileCalculationType = ExcelQuartileCalculation.ExclusiveMedian;
series.SerieFormat.Fill.ForeColorIndex = ExcelKnownColors.Grey_25_percent;
chart.ChartTitle = "Box & Whisker Plot for Price Distribution of Books by Genre";
#endregion
chart.Legend.Position = ExcelLegendPosition.Right;
if (this.rdBtnSheet.IsChecked != null && this.rdBtnSheet.IsChecked.Value)
{
chart.Activate();
}
else
{
workbook.Worksheets[0].Activate();
IChartShape chartShape = chart as IChartShape;
chartShape.TopRow = 1;
chartShape.BottomRow = 20;
chartShape.LeftColumn = 6;
chartShape.RightColumn = 13;
}
#endregion
#region Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
#endregion
#region Launching the saved workbook
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been created successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
#endregion
}
private void btnWorksheetLock_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Opening the Existing Worksheet from a Workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Insert Data
worksheet.Range["C5"].Text = "Worksheet protected with password 'syncfusion'";
worksheet.Range["C6"].Text = "You can't edit any cells other than A1 and A2";
worksheet.Range["C5"].CellStyle.Font.Bold = true;
worksheet.Range["C5"].CellStyle.Font.Size = 12;
worksheet.Range["C6"].CellStyle.Font.Bold = true;
worksheet.Range["C6"].CellStyle.Font.Size = 12;
worksheet.Range["C8"].Text = "For Excel 2003: Click 'Tools->Protection' to Unprotect the sheet.";
worksheet.Range["C8"].CellStyle.Font.Bold = true;
worksheet.Range["C8"].CellStyle.Font.Size = 12;
worksheet.Range["C10"].Text = "For Excel 2007 and above: Click 'Review Tab->Unprotect Sheet' to Unprotect the sheet.";
worksheet.Range["C10"].CellStyle.Font.Bold = true;
worksheet.Range["C10"].CellStyle.Font.Size = 12;
worksheet.Range["A1:A2"].Text = "You can edit this cell";
worksheet.Range["A1:A2"].CellStyle.Font.Bold = true;
#endregion
#region Protect Sheet
//Protecting Worksheet using Password
worksheet.Protect("syncfusion");
//Unlocking the cells which are needed to be edited
worksheet.Range["A1"].CellStyle.Locked = false;
worksheet.Range["A2"].CellStyle.Locked = false;
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("ProtectedSheet.xls");
btnWorksheetLock.Enabled = false;
btnWorksheetUnlock.Enabled = true;
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
System.Diagnostics.Process.Start("ProtectedSheet.xls");
}
#endregion
}
public ExcelHelper()
{
_workbook = new HSSFWorkbook();
}
//private RecordInfo mRecordInfo;
#endregion
#region " Public Properties "
#endregion
#region " InitExcel "
private void InitExcel()
{
mWorkbook = null;
mSheet = null;
}
/// <summary>
/// 生成excel
/// </summary>
/// <param name="columns">栏位，格式为：{
/// column_name:'栏位名称',
/// column_display_name:'表头显示名称'
/// }
/// </param>
/// <param name="data">数据，格式为：
/// {
///column_name1:'值',
///column_name2:'值',
/// .....
///column_nameN:'值',
///}
/// </param>
/// <returns></returns>
public byte[] GenerateExcel(IEnumerable <FrameDLRObject> columns, IEnumerable <FrameDLRObject> data)
{
IWorkbook workbook = null;
try
{
using (var ms = new MemoryStream())
{
ms.Seek(0, SeekOrigin.Begin);
workbook = new NPOI.XSSF.UserModel.XSSFWorkbook(ms);
}
ISheet sheet = workbook.GetSheetAt(0);
var index = 0;
//设置表头
var header = sheet.CreateRow(index);
var headerstyle = workbook.CreateCellStyle();
headerstyle.Alignment = HorizontalAlignment.Center;
headerstyle.BorderBottom = BorderStyle.Thin;
headerstyle.BorderTop = BorderStyle.Thin;
headerstyle.BorderLeft = BorderStyle.Thin;
headerstyle.BorderRight = BorderStyle.Thin;
headerstyle.FillBackgroundColor = IndexedColors.BlueGrey.Index;
headerstyle.FillPattern = FillPattern.SolidForeground;
var cellindex = 0;
foreach (dynamic item in columns)
{
var cell = header.CreateCell(cellindex);
cell.CellStyle = headerstyle;
cell.SetCellValue(item.column_display_name);
cellindex++;
}
cellindex = 0;
var datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Center;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
foreach (var item in data)
{
index++;
var row = sheet.CreateRow(index);
foreach (dynamic c in columns)
{
var cell = row.CreateCell(cellindex);
cell.CellStyle = datastyle;
var v = item.GetValue(c.column_name);
if (v == null)
{
cell.SetCellValue("");
}
else if (v is DateTime)
{
cell.SetCellValue(((DateTime)v).ToString("yyyy-MM-dd HH:mm:ss"));
}
else
{
cell.SetCellValue(v);
}
}
cellindex++;
}
byte[] buffer = new byte[1024];
using (MemoryStream output = new MemoryStream())
{
workbook.Write(output);
buffer = output.ToArray();
}
return(buffer);
}
finally
{
if (workbook != null)
{
workbook.Close();
}
}
}
public ViewToSync(IWorkbook workbook, ISystemView view) : base(workbook, view)
{
}
private void button1_Click(object sender, EventArgs e)
{
SplashScreenManager.ShowForm(this, typeof(WaitForm1), true, true, false);
SplashScreenManager.Default.SetWaitFormDescription("Recopilando informaciòn...");
if (MyStuff.UsaWCF == true)
{
DS_Detalle = objWCF.Reporte_Detalle_Formulacion("000000",
Convert.ToString(this.Cbo_AñoProceso.SelectedValue),
Convert.ToString(this.Cbo_Version.SelectedValue),
Convert.ToString(this.Txt_CodFuenteFinanciamiento.Value),
Convert.ToString(this.Txt_CodCentroCosto.Value),
Convert.ToString(this.Txt_CodProyecto.Value)
);
}
else
{
Service.Reporte SR = new Service.Reporte();
DS_Detalle = SR.Reporte_Detalle_Formulacion("000000",
Convert.ToString(this.Cbo_AñoProceso.SelectedValue),
Convert.ToString(this.Cbo_Version.SelectedValue),
Convert.ToString(this.Txt_CodFuenteFinanciamiento.Value),
Convert.ToString(this.Txt_CodCentroCosto.Value),
Convert.ToString(this.Txt_CodProyecto.Value)
);
}
workbook = spreadsheetControl.Document;
string sRutaInterna = Path.Combine(AppDomain.CurrentDomain.BaseDirectory, @"Excel\FormulacionDetalle.xlsx");
workbook.LoadDocument(sRutaInterna, DocumentFormat.Xlsx);
Worksheet worksheet_Detalle = workbook.Worksheets[0];
DV_Detalle = DS_Detalle.Tables[0].DefaultView;
worksheet_Detalle.DataBindings.BindToDataSource(DV_Detalle, 6, 0);
worksheet_Detalle.Cells[2, 1].Value = Convert.ToString(this.Cbo_AñoProceso.SelectedValue);
worksheet_Detalle.Cells[3, 1].Value = Convert.ToString(this.Cbo_Version.SelectedValue);
Range boundrange_Detalle = worksheet_Detalle.DataBindings[0].Range;
int intFilaInicial = boundrange_Detalle.TopRowIndex + 1;
int intFila = boundrange_Detalle.BottomRowIndex + 1;
string strRango = "";
string strCelda = "";
string strLetra = "";
for (var c = 15; c < 32; c++)
{
strLetra = traeLetraColumna(c);
strRango = strLetra.TrimEnd() + Convert.ToString(intFilaInicial) + ":" + strLetra.TrimEnd() + Convert.ToString(intFila);
strCelda = strLetra.TrimEnd() + Convert.ToString(intFila + 2);
worksheet_Detalle.Cells[strCelda].Formula = "= SUM(" + strRango.TrimEnd() + ")";
worksheet_Detalle.Cells[strCelda].Font.Bold = true;
}
////Creamos una nueva hoja
//Worksheet pivotWorksheet = workbook.Worksheets.Add();
//Worksheet worksheetPivot = workbook.Worksheets[1];
//worksheetPivot.Name = "Pivot";
////Creamos un pivot table
//string strLetraColumnaInicial = traeLetraColumna(boundrange_Detalle.LeftColumnIndex + 1);
//string strLetraColumnaFinal = traeLetraColumna(boundrange_Detalle.RightColumnIndex + 1);
//strRango = strLetraColumnaInicial.TrimEnd() + Convert.ToString(intFilaInicial) + ":" + strLetraColumnaFinal.TrimEnd() + Convert.ToString(intFila);
//PivotTable pivottable = pivotWorksheet.PivotTables.Add(worksheet_Detalle[strRango], pivotWorksheet["A7"]);
//PivotTable pt = workbook.Worksheets[1].PivotTables[0] as PivotTable;
//pt.Cache.Refresh();
//SplashScreenManager.CloseForm();
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
{
continue;
}
Entity_Hiragana data = (Entity_Hiragana)AssetDatabase.LoadAssetAtPath(exportPath, typeof(Entity_Hiragana));
if (data == null)
{
data = ScriptableObject.CreateInstance <Entity_Hiragana> ();
AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.sheets.Clear();
using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
IWorkbook book = null;
if (Path.GetExtension(filePath) == ".xls")
{
book = new HSSFWorkbook(stream);
}
else
{
book = new XSSFWorkbook(stream);
}
foreach (string sheetName in sheetNames)
{
ISheet sheet = book.GetSheet(sheetName);
if (sheet == null)
{
Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
Entity_Hiragana.Sheet s = new Entity_Hiragana.Sheet();
s.name = sheetName;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = null;
Entity_Hiragana.Param p = new Entity_Hiragana.Param();
cell = row.GetCell(0); p.Hira_kake2 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(1); p.Hira_kake3 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(2); p.Hira_kake4 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(3); p.Hira_kake5 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(4); p.Hira_kake6 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(5); p.Hira_yominikui3 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(6); p.Hira_yominikui4 = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(7); p.Hira_yominikui5 = (cell == null ? "" : cell.StringCellValue);
s.list.Add(p);
}
data.sheets.Add(s);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
/// <summary>
/// 把列表导入Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="collection"></param>
/// <param name="columns"></param>
/// <param name="title"></param>
public static void AppendToExcel <T>(IWorkbook workbook, ISheet sheet, List <T> collection, List <ExcelColumn> columns, int startRow, ExcelTitle title = null)
{
AppendToExcelProcess <T>(workbook, sheet, collection, columns, startRow, title);
}
private void btnConvert_Click(object sender, EventArgs e)
{
string fileName = "";
#region Workbook Initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Get the path of the Input file
string inputPath = GetFullTemplatePath("NorthwindTemplate.xls");
// Existing workbook is opened
IWorkbook workbook = application.Workbooks.Open(inputPath);
// The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Save Workbook or Worksheet
if (rdbWkSheet.Checked)
{
// Save the sheet as HTML
fileName = "WorksheetToHTML.html";
worksheet.SaveAsHtml(fileName);
}
else
{
//Save the book as HTML
fileName = "WorkbookToHTML.html";
workbook.SaveAsHtml(fileName, HtmlSaveOptions.Default);
}
#endregion
#region Workbook Close and Dispose
//Close the workbook and excelEngine
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the HTML?", "Conversion successful",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the HTMl file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo(fileName)
{
UseShellExecute = true
};
process.Start();
#else
Process.Start(fileName);
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
private async void btnInputTemplate_Click(object sender, RoutedEventArgs e)
{
#region Workbook initialization
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2016;
Assembly assembly = typeof(BoxAndWhisker).GetTypeInfo().Assembly;
string resourcePath = "Syncfusion.SampleBrowser.UWP.XlsIO.XlsIO.Tutorials.Samples.Assets.Resources.Templates.BoxAndWhiskerTemplate.xlsx";
Stream fileStream = assembly.GetManifestResourceStream(resourcePath);
IWorkbook workbook = await application.Workbooks.OpenAsync(fileStream);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Save the Workbook
StorageFile storageFile;
if (!(Windows.Foundation.Metadata.ApiInformation.IsTypePresent("Windows.Phone.UI.Input.HardwareButtons")))
{
FileSavePicker savePicker = new FileSavePicker();
savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
savePicker.SuggestedFileName = "InputTemplate";
savePicker.FileTypeChoices.Add("Excel Files", new List <string>()
{
".xlsx",
});
storageFile = await savePicker.PickSaveFileAsync();
}
else
{
StorageFolder local = Windows.Storage.ApplicationData.Current.LocalFolder;
storageFile = await local.CreateFileAsync("InputTemplate.xlsx", CreationCollisionOption.ReplaceExisting);
}
if (storageFile != null)
{
//Saving the workbook
await workbook.SaveAsAsync(storageFile);
workbook.Close();
excelEngine.Dispose();
MessageDialog msgDialog = new MessageDialog("Do you want to view the Document?", "File has been saved successfully.");
UICommand yesCmd = new UICommand("Yes");
msgDialog.Commands.Add(yesCmd);
UICommand noCmd = new UICommand("No");
msgDialog.Commands.Add(noCmd);
IUICommand cmd = await msgDialog.ShowAsync();
if (cmd == yesCmd)
{
// Launch the saved file
bool success = await Windows.System.Launcher.LaunchFileAsync(storageFile);
}
}
else
{
workbook.Close();
excelEngine.Dispose();
}
#endregion
}
/// <summary>
/// 把列表导入Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="collection"></param>
/// <param name="columns"></param>
/// <param name="title"></param>
public static void ListToExcel <T>(IWorkbook workbook, ISheet sheet, List <T> collection, List <ExcelColumn> columns, ExcelTitle title = null)
{
ListToExcelProcess <T>(workbook, sheet, collection, columns, title);
}
void OnExportClicked(object sender, EventArgs e)
{
ExcelEngine excelEngine = new ExcelEngine();
IApplication application = excelEngine.Excel;
application.DefaultVersion = ExcelVersion.Excel2013;
#region Initializing Workbook
//A new workbook is created.[Equivalent to creating a new workbook in MS Excel]
//The new workbook will have 1 worksheets
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
Assembly assembly = typeof(App).GetTypeInfo().Assembly;
Stream fileStream = null;
#if COMMONSB
fileStream = assembly.GetManifestResourceStream("SampleBrowser.Samples.XlsIO.Samples.Template.CLRObjects.xml");
#else
fileStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.CLRObjects.xml");
#endif
// Import the Custom Object to worksheet
StreamReader reader = new StreamReader(fileStream);
//IEnumerable<CLRObject> customers = GetData<CLRObject>(reader.ReadToEnd());
sheet.ImportData((List <CustomerObject>)dataGrid.ItemsSource, 5, 1, false);
#region Define Styles
IStyle pageHeader = workbook.Styles.Add("PageHeaderStyle");
IStyle tableHeader = workbook.Styles.Add("TableHeaderStyle");
pageHeader.Font.RGBColor = COLOR.Color.FromArgb(255, 83, 141, 213);
pageHeader.Font.FontName = "Calibri";
pageHeader.Font.Size = 18;
pageHeader.Font.Bold = true;
pageHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
pageHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Font.Color = ExcelKnownColors.Black;
tableHeader.Font.Bold = true;
tableHeader.Font.Size = 11;
tableHeader.Font.FontName = "Calibri";
tableHeader.HorizontalAlignment = ExcelHAlign.HAlignCenter;
tableHeader.VerticalAlignment = ExcelVAlign.VAlignCenter;
tableHeader.Color = COLOR.Color.FromArgb(255, 118, 147, 60);
tableHeader.Borders[ExcelBordersIndex.EdgeLeft].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeRight].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thin;
tableHeader.Borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thin;
#endregion
#region Apply Styles
// Apply style for header
sheet["A1:D1"].Merge();
sheet["A1"].Text = "Yearly Sales Report";
sheet["A1"].CellStyle = pageHeader;
sheet["A1"].RowHeight = 20;
sheet["A2:D2"].Merge();
sheet["A2"].Text = "Namewise Sales Comparison Report";
sheet["A2"].CellStyle = pageHeader;
sheet["A2"].CellStyle.Font.Bold = false;
sheet["A2"].CellStyle.Font.Size = 16;
sheet["A2"].RowHeight = 20;
sheet["A3:A4"].Merge();
sheet["D3:D4"].Merge();
sheet["B3:C3"].Merge();
sheet["B3"].Text = "Sales";
sheet["A3:D4"].CellStyle = tableHeader;
sheet["A3"].Text = "Sales Person";
sheet["B4"].Text = "Jan - Jun";
sheet["C4"].Text = "Jul - Dec";
sheet["D3"].Text = "Change (%)";
sheet.Columns[0].ColumnWidth = 19;
sheet.Columns[1].ColumnWidth = 10;
sheet.Columns[2].ColumnWidth = 10;
sheet.Columns[3].ColumnWidth = 11;
#endregion
#endregion
#region Saving workbook and disposing objects
workbook.Version = ExcelVersion.Excel2013;
MemoryStream stream = new MemoryStream();
workbook.SaveAs(stream);
workbook.Close();
excelEngine.Dispose();
if (Device.RuntimePlatform == Device.WinPhone || Device.RuntimePlatform == Device.WinRT || Device.RuntimePlatform == Device.UWP)
{
Xamarin.Forms.DependencyService.Get <ISaveWindowsPhone>().Save("CLRObjects.xlsx", "application/msexcel", stream);
}
else
{
Xamarin.Forms.DependencyService.Get <ISave>().Save("CLRObjects.xlsx", "application/msexcel", stream);
}
#endregion
}
public static List <T> ExcelToList <T>(IWorkbook workbook, ISheet sheet, List <ExcelColumn> columns, int headRowIndex, int dataRowIndex)
{
InitHead <T>(columns);
List <int> lstCellNum = new List <int>();
List <T> entityList = new List <T>();
IRow headRow = sheet.GetRow(headRowIndex);
for (int i = 0; i < columns.Count; i++)
{
int cellNum = headRow.GetCellNum(columns[i].Name);
if (cellNum == -1)
{
throw new ExcelValidException("缺少列：" + columns[i].Name);
}
lstCellNum.Add(cellNum);
}
PropertyInfo[] properties = typeof(T).GetProperties();
Dictionary <string, PropertyInfo> dictProperty = new Dictionary <string, PropertyInfo>();
for (int i = 0; i < properties.Length; i++)
{
dictProperty.Add(properties[i].Name, properties[i]);
}
IRow row;
ICell cell;
object cellValue;
for (int i = dataRowIndex; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
T entity = Activator.CreateInstance <T>();
for (int j = 0; j < columns.Count; j++)
{
cell = row.GetCell(lstCellNum[j]);
cellValue = GetCellValue(cell);
if (columns[j].AllowNull == false && string.IsNullOrWhiteSpace(cellValue.ToString()))
{
throw new ExcelValidException(columns[j].Name + "不能为空");
}
if (columns[j] is ExcelColumn <T> )
{
var genericColumn = columns[j] as ExcelColumn <T>;
if (genericColumn.OnDataBind != null)
{
cellValue = genericColumn.OnDataBind(cellValue, entity, cell);
}
}
else
{
if (columns[j].OnDataBind != null)
{
cellValue = columns[j].OnDataBind(cellValue, entity, cell);
}
}
if (dictProperty[columns[j].Field].PropertyType.IsValueType && string.IsNullOrWhiteSpace(cellValue.ToString()))
{
cellValue = Activator.CreateInstance(dictProperty[columns[j].Field].PropertyType);
}
else
{
try
{
cellValue = Convert.ChangeType(cellValue, dictProperty[columns[j].Field].PropertyType);
}
catch
{
if (dictProperty[columns[j].Field].PropertyType == typeof(DateTime))
{
cellValue = CommonUtility.ConvertToDateTime(cellValue.ToString());
}
}
}
dictProperty[columns[j].Field].SetValue(entity, cellValue, null);
}
entityList.Add(entity);
}
return(entityList);
}
/// <summary>
/// 将excel文件内容读取到DataTable数据表中
/// </summary>
/// <param name="fileName">文件完整路径名</param>
/// <param name="sheetName">指定读取excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名：true=是，false=否</param>
/// <returns>DataTable数据表</returns>
public static DataTable ReadExcelToDataTable(string fileName, string sheetName = null, bool isFirstRowColumn = true)
{
//定义要返回的datatable对象
DataTable data = new DataTable();
//excel工作表
ISheet sheet = null;
//数据开始行(排除标题行)
int startRow = 0;
try
{
if (!File.Exists(fileName))
{
return(null);
}
//根据指定路径读取文件
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read);
//根据文件流创建excel数据结构
IWorkbook workbook = WorkbookFactory.Create(fs);
//IWorkbook workbook = new HSSFWorkbook(fs);
//如果有指定工作表名称
if (!string.IsNullOrEmpty(sheetName))
{
sheet = workbook.GetSheet(sheetName);
//如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
if (sheet == null)
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
//如果没有指定的sheetName，则尝试获取第一个sheet
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
IRow firstRow = sheet.GetRow(0);
//一行最后一个cell的编号 即总的列数
int cellCount = firstRow.LastCellNum;
//如果第一行是标题列名
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
startRow = sheet.FirstRowNum + 1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
throw ex;
}
}
public static List <T> ExcelToList <T>(IWorkbook workbook, ISheet sheet, List <ExcelColumn> columns, int headRowIndex = 0)
{
return(ExcelToList <T>(workbook, sheet, columns, headRowIndex, headRowIndex + 1));
}
/// <summary>
/// 初始化单元格样式解析器
/// </summary>
/// <param name="workbook">工作薄</param>
/// <param name="style">单元格样式</param>
private CellStyleResolver(IWorkbook workbook, CellStyle style)
{
_workbook = workbook;
_style = style;
}
/// <summary>
///填充Excel
/// </summary>
/// <typeparam name="T"></typeparam>
/// <param name="workbook"></param>
/// <param name="sheet"></param>
/// <param name="collection"></param>
/// <param name="columns"></param>
/// <param name="startRow"></param>
private static void ListToExcelDataRow <T>(IWorkbook workbook, ISheet sheet, List <T> collection, List <ExcelColumn> columns, int startRow)
{
ICellStyle defaultCellStyle = GetCellStyle(workbook);
ICell cell;
IRow row;
List <PropertyInfo> lstProperty = new List <PropertyInfo>();
Type type = typeof(T);
for (int i = 0; i < columns.Count; i++)
{
if (string.IsNullOrEmpty(columns[i].Field))
{
lstProperty.Add(null);
}
else
{
lstProperty.Add(type.GetProperty(columns[i].Field));
}
}
for (int i = 0; i < collection.Count; i++)
{
row = sheet.CreateRow(i + startRow);
for (int j = 0; j < lstProperty.Count; j++)
{
cell = row.CreateCell(j);
cell.CellStyle = columns[j].DataCellStyle == null ? defaultCellStyle : columns[j].DataCellStyle;
if (!string.IsNullOrWhiteSpace(columns[j].CellFormulaString))
{
cell.SetCellFormula(columns[j].CellFormulaString);
}
if (columns[j].OnDataCellCreate != null)
{
columns[j].OnDataCellCreate(cell, sheet, workbook);
}
object value = null;
if (lstProperty[j] != null)
{
value = lstProperty[j].GetValue(collection[i], null);
}
if (columns[j] is ExcelColumn <T> )
{
var genericColumn = columns[j] as ExcelColumn <T>;
if (genericColumn.OnDataBind != null)
{
value = genericColumn.OnDataBind(value, collection[i], cell);
}
}
else
{
if (columns[j].OnDataBind != null)
{
value = columns[j].OnDataBind(value, collection[i], cell);
}
}
if (value == null)
{
continue;
}
TypeCode typeCode = Type.GetTypeCode(value.GetType());
if (typeCode == TypeCode.Decimal && columns[j].DataCellStyle == null)
{
if (cell.CellStyle.Index == defaultCellStyle.Index)
{
cell.CellStyle = defaultCellStyle;
}
}
if (typeCode == TypeCode.DateTime)
{
#region 过滤默认时间 1900-01-01
try
{
if (Convert.ToDateTime(value).Date <= Convert.ToDateTime("1900-01-01").Date)
{
cell.SetCellValue("");
}
else
{
cell.SetCellValue(Convert.ToDateTime(value).ToString("yyyy-MM-dd HH:mm:ss"));
}
}
catch
{
cell.SetCellValue("格式错误");
}
#endregion
}
else if (typeCode == TypeCode.Boolean)
{
#region bool类型：是/否
try
{
if (Convert.ToBoolean(value))
{
cell.SetCellValue("是");
}
else
{
cell.SetCellValue("否");
}
}
catch
{
cell.SetCellValue("未知");
}
#endregion
}
else if (typeCode == TypeCode.Byte || typeCode == TypeCode.Decimal || typeCode == TypeCode.Double || typeCode == TypeCode.Int16 || typeCode == TypeCode.Int32 || typeCode == TypeCode.Int64 || typeCode == TypeCode.SByte || typeCode == TypeCode.Single || typeCode == TypeCode.UInt16 || typeCode == TypeCode.UInt32 || typeCode == TypeCode.UInt64)
{
cell.SetCellValue(Convert.ToDouble(value));
}
else
{
cell.SetCellValue(value.ToString());
}
}
}
}
//Export and saves the chart in Excel Sheet
#region XLsIO
private void buttonXLsIO_Click(object sender, EventArgs e)
{
try
{
//Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Instantiate the excel application object.
IApplication application = excelEngine.Excel;
//Set the default version as Excel 2007;
application.DefaultVersion = ExcelVersion.Excel2007;
exportFileName = fileName + ".xlsx";
//A new workbook with a worksheet is created.
IWorkbook chartBook = application.Workbooks.Create(1);
IWorksheet sheet = chartBook.Worksheets[0];
//Fill the worksheet by chart data.
for (int i = 1; i <= 4; i++)
{
sheet.Range[i, 1].Number = this.chartControl1.Series[0].Points[i - 1].X;
sheet.Range[i, 2].Number = this.chartControl1.Series[0].Points[i - 1].YValues[0];
}
for (int i = 1; i <= 4; i++)
{
sheet.Range[i + 5, 1].Number = this.chartControl1.Series[1].Points[i - 1].X;
sheet.Range[i + 5, 2].Number = this.chartControl1.Series[1].Points[i - 1].YValues[0];
}
//Create a chart worksheet.
IChart chart = chartBook.Charts.Add("Essential Chart");
//Specifies the title of the Chart.
chart.ChartTitle = "Essential Chart";
//Initializes a new series instance and adds it to the series collection of the chart.
IChartSerie series1 = chart.Series.Add();
//Specify the chart type of the series.
series1.SerieType = ExcelChartType.Column_Clustered;
//Specify the name of the series. This will be displayed as the text of the legend.
series1.Name = "Sample Series";
//Specify the value ranges for the series.
series1.Values = sheet.Range["B1:B5"];
//Specify the Category labels for the series.
series1.CategoryLabels = sheet.Range["A1:A5"];
IChartSerie series2 = chart.Series.Add();
//Specify the chart type of the series.
series2.SerieType = ExcelChartType.Column_Clustered;
//Specify the name of the series. This will be displayed as the text of the legend.
series2.Name = "Sample Series";
//Specify the value ranges for the series.
series2.Values = sheet.Range["B6:B10"];
//Specify the Category labels for the series.
series2.CategoryLabels = sheet.Range["A6:A10"];
//Makes the chart as active sheet.
chart.Activate();
//Save the Chart book.
chartBook.SaveAs(exportFileName);
chartBook.Close();
ExcelUtils.Close();
OpenFile("XLsIO", exportFileName);
System.Diagnostics.Process.Start(exportFileName);
}
catch (Exception ex)
{
this.toolStripStatusLabel1.Text = "Chart Export failed.";
Console.WriteLine(ex.ToString());
}
}
/// <summary>
/// 根据循环遍历Sheet获取数据
/// </summary>
/// <param name="wb"></param>
/// <param name="sheetName"></param>
private void AddVisiableSheetToDataSet(IWorkbook wb, string sheetName)
{
ISheet sh = wb.GetSheet(sheetName);
DateTime productionDate = DateTime.Now.Date.AddDays(10);
//List<ProductiveTaskListModel> listModels = new List<ProductiveTaskListModel>();
//创建列
DataTable dt = ConvertHelper.CreateDataTableFromModel <ProductiveTaskListModel>();
string previousBatch = string.Empty, previousProductionModel = string.Empty, previousHasSmallMaterial = string.Empty;
decimal previousQuantity = 0;
if (sh != null && sh.PhysicalNumberOfRows > 1)//包括表头大于1条记录
{
//获取第一行日期
string title = sh.GetRow(0).GetCell(0).StringCellValue;
string productionDateString = title.Substring(0, title.IndexOf("日")).Replace("年", "-").Replace("月", "-").Replace("日", "-");
try
{
productionDate = DateTime.Parse(productionDateString);
}
catch
{
new Exception("标题日期格式错误");
}
//具体数据获取sh.FirstRowNum 第一行为表头 从第二行开始获取;LastRowNum多加一行
for (int i = 2; i <= sh.LastRowNum + 1; i++)
{
IRow rowdata = sh.GetRow(i);
//ProductiveTaskListModel model = new ProductiveTaskListModel();
DataRow dr = dt.NewRow();
//空行跳过
if (rowdata == null || rowdata.Cells.Count == 0)
{
continue;
}
else
{
//批号有数据则，将当行获取的数据写入
if (GetCellValue(rowdata.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString().Length > 0)
{
previousProductionModel = GetCellValue(rowdata.GetCell(1, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
previousBatch = GetCellValue(rowdata.GetCell(2, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
previousQuantity = Convert.ToDecimal(GetCellValue(rowdata.GetCell(3, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString());
previousHasSmallMaterial = GetCellValue(rowdata.GetCell(4, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FitemName"] = previousProductionModel;
dr["FBatchNo"] = previousBatch;
dr["FQuantity"] = previousQuantity;
dr["FHasSmallMaterial"] = previousHasSmallMaterial;
}
else //没有批号，则取上一个值
{
dr["FitemName"] = previousProductionModel;
dr["FBatchNo"] = previousBatch;
dr["FQuantity"] = previousQuantity;
dr["FHasSmallMaterial"] = previousHasSmallMaterial;
}
dr["FProductionDate"] = productionDate;
dr["FType"] = sheetName;
dr["ID"] = GetCellValue(rowdata.GetCell(0, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FPackage"] = GetCellValue(rowdata.GetCell(5, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FBucketName"] = GetCellValue(rowdata.GetCell(6, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FOrgID"] = GetCellValue(rowdata.GetCell(7, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FLabel"] = GetCellValue(rowdata.GetCell(8, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FNote"] = GetCellValue(rowdata.GetCell(9, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["FResidue"] = Convert.ToDecimal(GetCellValue(rowdata.GetCell(13, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString());
dr["FBillNo"] = GetCellValue(rowdata.GetCell(14, MissingCellPolicy.CREATE_NULL_AS_BLANK)).ToString();
dr["CreateDate"] = DateTime.Now.Date;
dt.Rows.Add(dr);
}
}
}
// 将DataTable dt 导入到数据库 覆盖任务
new ProductiveTaskListDAL().ImportDataTableSync(dt);
}
public DataTable ConvertExcelToDataTable(string fileName, bool firstSheet)
{
IWorkbook wb = null;
DataTable dataTable = new DataTable();
if (!File.Exists(fileName))
{
return(null);
}
try
{
FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);//FileShare 及时文件打开也可以读取里面的内容
if (fileName.IndexOf("xlsx") > 0)
{
wb = new XSSFWorkbook(fs);
fs.Close();
}
else if (fileName.IndexOf("xls") > 0)
{
wb = new HSSFWorkbook(fs);
fs.Close();
}
else
{
return(null);
}
if (firstSheet)
{
// 增加列
ISheet sh = wb.GetSheetAt(0);
if (sh == null)
{
return(null);
}
IRow header = sh.GetRow(0);
if (header == null)
{
return(null);
}
for (int i = 0; i < header.Cells.Count; i++)
{
DataColumn dc = new DataColumn(header.GetCell(i).StringCellValue);
dataTable.Columns.Add(dc);
}
dataTable.Columns.Add(new DataColumn("Seq"));
int colCount = dataTable.Columns.Count;
// 增加行
for (int i = 1; i <= sh.LastRowNum; i++)
{
IRow rowdata = sh.GetRow(i);
DataRow dr = dataTable.NewRow();
//空行跳过
if (rowdata == null || rowdata.Cells.Count == 0)
{
continue;
}
else
{
for (int j = 0; j < colCount - 1; j++)
{
dr[header.GetCell(j).StringCellValue] = j <= rowdata.Cells.Count()? GetCellValue(rowdata.GetCell(j)):"";
}
dr[colCount - 1] = i + 1;
dataTable.Rows.Add(dr);
}
}
}
else
{ // 此处没有实现
for (int i = 0; i < wb.NumberOfSheets; i++)
{
if (!wb.IsSheetHidden(i))//对所有不是隐藏的表执行转换
{
AddVisiableSheetToDataSet(wb, wb.GetSheetAt(i).SheetName);
}
}
}
return(dataTable);
}
catch (Exception e)
{
throw new Exception(e.Message.ToString());
}
}
private void btnProtectWorkbook_Click(object sender, System.EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Opening the Existing Worksheet from a Workbook
IWorkbook workbook = application.Workbooks.Create(1);
//The first worksheet object in the worksheets collection is accessed.
IWorksheet worksheet = workbook.Worksheets[0];
#endregion
#region Protect Workbook
worksheet.Range["C5"].Text = "Workbook is protected with password 'syncfusion'";
worksheet.Range["C6"].Text = "You can't make changes to structure and window of the workbook.";
worksheet.Range["C5"].CellStyle.Font.Bold = true;
worksheet.Range["C5"].CellStyle.Font.Size = 12;
worksheet.Range["C6"].CellStyle.Font.Bold = true;
worksheet.Range["C6"].CellStyle.Font.Size = 12;
worksheet.Range["C8"].Text = "For Excel 2003: Click 'Tools->Protection' to Unprotect the workbook.";
worksheet.Range["C8"].CellStyle.Font.Bold = true;
worksheet.Range["C8"].CellStyle.Font.Size = 12;
worksheet.Range["C10"].Text = "For Excel 2007 and above: Click 'Review Tab->Protect Workbook' to Unprotect the workbook.";
worksheet.Range["C10"].CellStyle.Font.Bold = true;
worksheet.Range["C10"].CellStyle.Font.Size = 12;
workbook.Protect(true, true, "syncfusion");
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("ProtectedWorkbook.xls");
btnProtectWorkbook.Enabled = false;
btnUnprotectWorkbook.Enabled = true;
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("ProtectedWorkbook.xls")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("ProtectedWorkbook.xls");
#endif
}
#endregion
}
private static IWorkbook AgregarDatos <T>(IWorkbook wb, IEnumerable <T> datos, TipoExportacion tipoexportacion, string grupo)
{
//Recuperar la hoja de exportación - Se asume que siempre trabajamos con la primera hoja
ISheet sheet = wb.GetSheetAt(0);
IRow fila;
ICell celda;
FabricaEstilos fabricaEstilos = new FabricaEstilos(estilosHistoricos);
//Obtener la siguiente fila disponible, sino es la primera dejar dos espacios al medio
var ultimaFila = sheet.LastRowNum;
ultimaFila = ultimaFila != 0 ? ultimaFila + 2 : 0;
//Inicializamos la lista de columnas a exportar
var columnasExportar = LeerAtributos(typeof(T));
//En caso haya columnas para exportar continuamos
if (columnasExportar.Any())
{
//Inicializamos el ancho de las columnas solo si es la primera exportación de datos
if (ultimaFila == 0)
{
foreach (var item in columnasExportar)
{
if (item.Grupo.Contains(grupo))
{
sheet.SetColumnWidth(item.Orden, item.Tamanio * 256);
}
}
}
//Llena la cabecera
fila = sheet.CreateRow(ultimaFila);
foreach (var columna in columnasExportar.OrderBy(x => x.Orden))
{
if (columna.Grupo.Contains(grupo))
{
celda = fila.CreateCell(columna.Orden);
celda.SetCellValue(columna.Cabecera);
if (tipoexportacion == TipoExportacion.ExcelCabeceras)
{
celda.CellStyle = (HSSFCellStyle)fabricaEstilos.obtenerCellStyle(wb, TipoCellStyle.Cabecera);
//Actualizamos los estilos históricos
estilosHistoricos = fabricaEstilos.Estilos;
}
}
}
//Agregar las filas de datos
var secuencia = ultimaFila + 1;
foreach (var registro in datos)
{
fila = sheet.CreateRow(secuencia++);
foreach (var columna in columnasExportar.OrderBy(x => x.Orden))
{
if (columna.Grupo.Contains(grupo))
{
EscribirCelda(wb, fila, columna, registro);
}
}
}
}
return(wb);
}
private void btnUnprotect_Click(object sender, EventArgs e)
{
#region Workbook Initialize
//New instance of XlsIO is created.[Equivalent to launching MS Excel with no workbooks open].
//The instantiation process consists of two steps.
//Step 1 : Instantiate the spreadsheet creation engine.
ExcelEngine excelEngine = new ExcelEngine();
//Step 2 : Instantiate the excel application object.
IApplication application = excelEngine.Excel;
// Opening a Existing(Protected) Worksheet from a Workbook
IWorkbook workbook = application.Workbooks.Open(@"ProtectedWorkbook.xls");
#endregion
#region Unprotect the workbook
//Unprotecting( unlocking) Workbook using the Password
workbook.Unprotect("syncfusion");
#endregion
#region Modify the Datas
//The first worksheet object in the worksheets collection is accessed.
IWorksheet sheet = workbook.Worksheets[0];
sheet.Range["C5"].Text = "Workbook is Unprotected with password 'syncfusion' and changes are done";
sheet.Range["C6"].Text = "You can now edit the structure and window of this workbook.";
sheet.Range["C5"].CellStyle.Font.Bold = true;
sheet.Range["C5"].CellStyle.Font.Size = 12;
sheet.Range["C8"].Text = "For Excel 2003: Click 'Tools->Protection' to view the Protection settings.";
sheet.Range["C8"].CellStyle.Font.Bold = true;
sheet.Range["C8"].CellStyle.Font.Size = 12;
sheet.Range["C10"].Text = "For Excel 2007 and above: Click 'Review Tab->Protect Workbook' to view the Protection settings.";
sheet.Range["C10"].CellStyle.Font.Bold = true;
sheet.Range["C10"].CellStyle.Font.Size = 12;
#endregion
#region Save the Workbook
//Saving the workbook to disk.
workbook.SaveAs("UnProtectedWorkbook.xls");
#endregion
#region Workbook Close and Dispose
//Close the workbook.
workbook.Close();
//No exception will be thrown if there are unsaved workbooks.
excelEngine.ThrowNotSavedOnDestroy = false;
excelEngine.Dispose();
#endregion
#region View the Workbook
//Message box confirmation to view the created spreadsheet.
if (MessageBox.Show("Do you want to view the workbook?", "Workbook has been created",
MessageBoxButtons.YesNo, MessageBoxIcon.Information)
== DialogResult.Yes)
{
//Launching the Excel file using the default Application.[MS Excel Or Free ExcelViewer]
#if NETCORE
System.Diagnostics.Process process = new System.Diagnostics.Process();
process.StartInfo = new System.Diagnostics.ProcessStartInfo("UnProtectedWorkbook.xls")
{
UseShellExecute = true
};
process.Start();
#else
Process.Start("UnProtectedWorkbook.xls");
#endif
//Exit
this.Close();
}
else
{
// Exit
this.Close();
}
#endregion
}
/// <summary>
///
/// </summary>
/// <param name="path">生成的文件路径</param>
/// <param name="chips">源数据</param>
public static bool Analysize(string path, Chip[] chips)
{
bool result = false;
FileStream fs_create = new FileStream(path, FileMode.Create, FileAccess.ReadWrite);
try
{
//1.获取有多少个chip
var count = from i in chips select i[1]; //选取所有的chipID
var count_distinct = count.Distinct().ToArray(); //对chipID去重,结果是数组。
//2.通过文件流来创建workbook,注意最后要关闭。
IWorkbook workbook = Factory.GetWorkbook();
IWorksheet worksheet = (IWorksheet)workbook.Sheets[0];
for (int i = 0; i < count_distinct.Length; i++)
{
if (i == 0)
{
worksheet.Name = count_distinct[0];
}
else
{
IWorksheet next_worksheet = (IWorksheet)worksheet.CopyAfter(worksheet);
next_worksheet.Name = count_distinct[i];
}
IWorksheet currentworksheet = workbook.Worksheets[count_distinct[i]];
//分析数据
//1.搜索chipID为count_distinct[i]的所有chip对象。
var chip_group = from j in chips where j[1] == count_distinct[i] select j;
//2.对同一个chipID的所有chip对象进行分析。
var TemperatureMeasureds = (from j in chip_group select j[8]).ToArray();
//3.取最大温度量测值和最小温度量测值做比较
double[] temperatures = new double[TemperatureMeasureds.Length];
for (int k = 0; k < TemperatureMeasureds.Length; k++)
{
temperatures[k] = Convert.ToDouble(TemperatureMeasureds[k]);
}
double maxvalue = Enumerable.Max(temperatures);
double minvalue = Enumerable.Min(temperatures);
//4.搜索最大温度值的芯片与最小温度值的芯片：
var Max_chip = (from j in chip_group where Convert.ToDouble(j[8]) == maxvalue select j).ToArray()[0];
var Min_chip = (from j in chip_group where Convert.ToDouble(j[8]) == minvalue select j).ToArray()[0];
//得出需要的数据：
//Gain Resistance 14 L
//Imod_90mA 38 L
//Ith_Imod 17 L
//Pout_90mA 42 两个，一个高一个低 H L
//Reflection3rd Distance 23 L
//SMSR_90mA 57 L
//Vgain_90mA 34 L
//WVL_90mA 50 H L
IWorksheet current_worksheet = workbook.Worksheets[i];
current_worksheet.Cells["A1"].Value = "GAINR_L";
current_worksheet.Cells["B1"].Value = "Gain Resistance (Ohm)";
current_worksheet.Cells["C1"].Value = Min_chip[14];
current_worksheet.Cells["A2"].Value = "IMOD_90MA_L";
current_worksheet.Cells["B2"].Value = "Imod_90mA (mA)";
current_worksheet.Cells["C2"].Value = Min_chip[38];
current_worksheet.Cells["A3"].Value = "ITH_L";
current_worksheet.Cells["B3"].Value = "Ith_Imod (mA)";
current_worksheet.Cells["C3"].Value = Min_chip[17];
current_worksheet.Cells["A4"].Value = "POUT_90MA_H";
current_worksheet.Cells["B4"].Value = "Pout_90mA (mW)";
current_worksheet.Cells["C4"].Value = Max_chip[42];
current_worksheet.Cells["A5"].Value = "POUT_90MA_L";
current_worksheet.Cells["B5"].Value = "Pout_90mA (mW)";
current_worksheet.Cells["C5"].Value = Min_chip[42];
current_worksheet.Cells["A6"].Value = "REFCECTLON_D3_L";
current_worksheet.Cells["B6"].Value = "Reflection3rd Distance (K/A^3)";
current_worksheet.Cells["C6"].Value = Min_chip[23];
current_worksheet.Cells["A7"].Value = "SMSR_90MA_L";
current_worksheet.Cells["B7"].Value = "SMSR_90mA (dB)";
current_worksheet.Cells["C7"].Value = Min_chip[57];
current_worksheet.Cells["A8"].Value = "VGAIN_90MA_L";
current_worksheet.Cells["B8"].Value = Chip.properties[34];
current_worksheet.Cells["C8"].Value = Min_chip[34];
current_worksheet.Cells["A9"].Value = "WL_90MA_H";
current_worksheet.Cells["B9"].Value = Chip.properties[50];
current_worksheet.Cells["C9"].Value = Max_chip[50];
current_worksheet.Cells["A10"].Value = "WL_90MA_L";
current_worksheet.Cells["B10"].Value = Chip.properties[50];
current_worksheet.Cells["C10"].Value = Min_chip[50];
}
workbook.SaveToStream(fs_create, FileFormat.Excel8);
result = true;
}
catch (Exception e)
{
ExDictionary dic = new ExDictionary();
dic["msg"] = "异常：" + e.Message + "\r\n异常出现的地方：" + e.StackTrace;
dic["color"] = Color.Red;
dic["nextline"] = true;
Notification.GetInstance().PostNotification("log", dic);
result = false;
}
finally
{
fs_create.Close();
}
return(result);
}
public static DataSet Load(FileInfo file, bool isFirtRowHeader)
{
IWorkbook hssfworkbook = Open(file.FullName);
return(GetDataSet(isFirtRowHeader, hssfworkbook));
}
