public int getRowCount(string sheetName) { int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(0); } else { sheet = workbook.GetSheetAt(index); int number = sheet.LastRowNum + 1; return(number); } }
public int getRowNumber(string sheetName, int colNum, string value) { try { fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite); workbook = new XSSFWorkbook(fs); int d = 0; int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(0); } sheet = workbook.GetSheetAt(index); for (int rw = 0; rw < sheet.LastRowNum; rw++) { if (sheet.GetRow(rw) != null) { row = sheet.GetRow(rw); } } return(row.RowNum); } catch (Exception) { return(row.RowNum); } }
public static List <string> GetCellDataList(string sheetName, string colName) { string path = @"D:\SeleniumCsharp\AmazonTestFramework\AmazonTest\Data\AmazonExcelData.xlsx"; Console.WriteLine(Path.GetFullPath(path)); XSSFWorkbook wb; List <string> data = new List <string>(); FileStream fs = new FileStream(path, FileMode.Open, FileAccess.Read); wb = new XSSFWorkbook(fs); int index = wb.GetSheetIndex(sheetName); ISheet sh = wb.GetSheetAt(index); int totalRow = sh.LastRowNum; for (int i = 0; i <= totalRow; i++) { IRow row = sh.GetRow(i); ICell cell1 = row.GetCell(0); string cell1Value = cell1.StringCellValue; if (cell1.StringCellValue.Equals(colName)) { int totalColumn = row.LastCellNum; for (int j = 1; j < totalColumn; j++) { ICell cell = row.GetCell(j); data.Add(cell.StringCellValue); } break; } } wb.Close(); fs.Close(); return(data); }
/// <summary> /// 缓存某个表 /// </summary> /// <param name="dt">数据表</param> /// <param name="sheetName">表格名称</param> private void CacheData(DataTable dt, string sheetName) { if (dt == null) { return; } //新建sheet var sheet = _workbook.CreateSheet(sheetName);//名为ref的工作表 var index = _workbook.GetSheetIndex(sheet); var row = sheet.CreateRow(0); ICell cell; //写入表头 var colIndex = 0; foreach (DataColumn dc in dt.Columns) { cell = row.CreateCell(colIndex++); cell.SetCellValue(dc.ColumnName); //设置单元格为字符串类型,便于取值 cell.SetCellType(CellType.String); } //写入数据行 var rowIndex = 1; //循环赋值 foreach (DataRow dr in dt.Rows) { row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); colIndex = 0; foreach (DataColumn dc in dt.Columns) { cell = row.CreateCell(colIndex++); cell.SetCellValue(dr[dc.ColumnName].ToString()); //设置单元格为字符串类型,便于取值 cell.SetCellType(CellType.String); } rowIndex++; } //隐藏缓存数据的列 //调试模式下,显示该Excel _workbook.SetSheetHidden(index, _config.Prop.Debug ? false : true); }
public string setCellData(string sheetName, string colName, int rowNum, string data) { try { fs = new FileStream(path, FileMode.Open, FileAccess.Read); workbook = new XSSFWorkbook(fs); if (rowNum <= 0) { return(""); } int colNum = -1; int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(""); } sheet = workbook.GetSheetAt(index); row = sheet.GetRow(0); for (int i = 0; i < row.LastCellNum; i++) { if (row.GetCell(i).StringCellValue.Equals(colName)) { colNum = i; } } if (colNum == -1) { return(""); } row = sheet.GetRow(rowNum - 1); if (row == null) { row = sheet.CreateRow(rowNum - 1); } cell = row.GetCell(colNum); if (cell == null) { cell = row.CreateCell(colNum); } ICellStyle cs = workbook.CreateCellStyle(); cs.WrapText = true; cell.CellStyle = cs; cell.SetCellValue(data); FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite); workbook.Write(f); f.Close(); fs.Close(); return(data); } catch (Exception) { return(""); } }
public void SetFirstVisibleTab_57373() { XSSFWorkbook wb = new XSSFWorkbook(); try { /*Sheet sheet1 =*/ wb.CreateSheet(); ISheet sheet2 = wb.CreateSheet(); int idx2 = wb.GetSheetIndex(sheet2); ISheet sheet3 = wb.CreateSheet(); int idx3 = wb.GetSheetIndex(sheet3); // add many sheets so "first visible" is relevant for (int i = 0; i < 30; i++) { wb.CreateSheet(); } wb.FirstVisibleTab = (/*setter*/ idx2); wb.SetActiveSheet(idx3); //wb.Write(new FileOutputStream(new File("C:\\temp\\test.xlsx"))); Assert.AreEqual(idx2, wb.FirstVisibleTab); Assert.AreEqual(idx3, wb.ActiveSheetIndex); IWorkbook wbBack = XSSFTestDataSamples.WriteOutAndReadBack(wb); sheet2 = wbBack.GetSheetAt(idx2); sheet3 = wbBack.GetSheetAt(idx3); Assert.AreEqual(idx2, wb.FirstVisibleTab); Assert.AreEqual(idx3, wb.ActiveSheetIndex); wbBack.Close(); } finally { wb.Close(); } }
public string setCellData(string sheetName, int colNum, int rowNum, string data) { fs = new FileStream(path, FileMode.Open, FileAccess.ReadWrite); workbook = new XSSFWorkbook(fs); if (rowNum <= 0) { return(""); } int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(""); } sheet = workbook.GetSheetAt(index); row = sheet.GetRow(rowNum - 1); if (row == null) { row = sheet.CreateRow(rowNum - 1); } cell = row.GetCell(colNum - 1); if (cell == null) { cell = row.CreateCell(colNum - 1); } //for (int rownum = 1; rownum <= 1000; rownum++) //{ // row = sheet.CreateRow(rownum); // cell = row.CreateCell(colNum-1); // ICellStyle cs = workbook.CreateCellStyle(); // cs.WrapText = true; // cell.CellStyle = cs; // cell.SetCellValue(data); //} ICellStyle cs = workbook.CreateCellStyle(); cs.WrapText = true; cell.CellStyle = cs; cell.SetCellValue(data); FileStream f = new FileStream(path, FileMode.Create, FileAccess.ReadWrite); workbook.Write(f); f.Close(); fs.Close(); return(data); }
//以下未优化 /// <summary> /// 将DataTable写入模板 /// </summary> /// <param name="dataTable">汇总</param> /// <param name="rowIndex">开始行</param> /// <param name="colIndex">结束行</param> /// <param name="shtName">要汇总的工作表名</param> /// <param name="path">输出路径</param> public static void DataTableToTemplate(DataTable dataTable, int rowIndex, int colIndex, string shtName, string path, params int[] nums) { IWorkbook wb; using (FileStream fs = new FileStream("Template.xlsx", FileMode.Open, FileAccess.ReadWrite, FileShare.ReadWrite)) { wb = new XSSFWorkbook(fs); } ISheet sht = wb.GetSheetAt(wb.GetSheetIndex(shtName)); ICellStyle style = wb.CreateCellStyle(); style.BorderTop = BorderStyle.Thin; style.BorderLeft = BorderStyle.Thin; style.BorderRight = BorderStyle.Thin; style.BorderBottom = BorderStyle.Thin; for (int r = 0; r < dataTable.Rows.Count; r++) { IRow row = sht.GetRow(rowIndex); int col = colIndex; for (int c = 0; c < dataTable.Columns.Count; c++) { ICell cell = row.CreateCell(col); cell.CellStyle = style; if (nums.Contains(c)) { double.TryParse(dataTable.Rows[r][c].ToString(), out double d); cell.SetCellValue(d); } else { Type type = dataTable.Columns[c].DataType; SetCellTypeValue(type, dataTable.Rows[r][c].ToString(), ref cell); } col++; } rowIndex++; } MemoryStream stream = new MemoryStream(); wb.Write(stream); var buf = stream.ToArray(); //保存为Excel文件 using (FileStream fs1 = new FileStream(path, FileMode.Create, FileAccess.Write)) { fs1.Write(buf, 0, buf.Length); fs1.Flush(); } }
/** * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook. * * @param cell the cell to update * @param frwb the formula rendering workbbok that returns new sheet name */ private void UpdateFormula(XSSFCell cell, IFormulaRenderingWorkbook frwb) { CT_CellFormula f = cell.GetCTCell().f; if (f != null) { String formula = f.Value; if (formula != null && formula.Length > 0) { int sheetIndex = _wb.GetSheetIndex(cell.Sheet); Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex); String updatedFormula = FormulaRenderer.ToFormulaString(frwb, ptgs); if (!formula.Equals(updatedFormula)) { f.Value = (updatedFormula); } } } }
public bool addColumn(string sheetName, string colName) { try { fs = new FileStream(path, FileMode.OpenOrCreate, FileAccess.ReadWrite); workbook = new XSSFWorkbook(fs); int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(false); } ICellStyle cs = workbook.CreateCellStyle(); sheet = workbook.GetSheetAt(index); row = sheet.GetRow(0); if (row == null) { row = sheet.CreateRow(0); } cell = row.GetCell(0); if (cell == null) { cell = row.CreateCell(0); } else { cell = row.CreateCell(row.LastCellNum); } cell.SetCellValue(colName); cell.CellStyle = cs; FileStream f = new FileStream(path, FileMode.Create); workbook.Write(f); f.Close(); fs.Close(); return(true); } catch (Exception) { return(false); } }
/** * Parse cell formula and re-assemble it back using the specified FormulaRenderingWorkbook. * * @param cell the cell to update * @param frwb the formula rendering workbbok that returns new sheet name */ private void UpdateFormula(XSSFCell cell, String oldName, String newName) { CT_CellFormula f = cell.GetCTCell().f; if (f != null) { String formula = f.Value; if (formula != null && formula.Length > 0) { int sheetIndex = _wb.GetSheetIndex(cell.Sheet); Ptg[] ptgs = FormulaParser.Parse(formula, _fpwb, FormulaType.Cell, sheetIndex, -1); foreach (Ptg ptg in ptgs) { UpdatePtg(ptg, oldName, newName); } String updatedFormula = FormulaRenderer.ToFormulaString(_fpwb, ptgs); if (!formula.Equals(updatedFormula)) { f.Value = (updatedFormula); } } } }
public int GetSheetIndex(string name) { return(XssfWorkbook.GetSheetIndex(name)); }
public override void PassDataToExcel(ISmartTool tool) { TemplateFileName = "ARC Diagram.xlsx"; var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}"; try { using ( var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { Book = new XSSFWorkbook(file); } Sheet = Book.GetSheetAt(0); Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{tool.Top.Name}_{tool.Top.SerialNumber}"); var arcData = new ArcData(); if (!arcData.Tools.ContainsKey(tool.Top.SerialNumber)) { MessageBox.Show("No such ARC in library", "Information", MessageBoxButton.OK, MessageBoxImage.Asterisk); return; } var arcTool = arcData.Tools[tool.Top.SerialNumber]; var inspectionLength = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Top.Length)); var arrayLength = Convert.ToSingle(arcTool.L, CultureInfo.InvariantCulture); var difference = Math.Abs(inspectionLength - arrayLength); if (difference > 0.025f) { MessageBox.Show($"Collar length {inspectionLength} doesn't match. Should be about {arrayLength}. Difference is {difference}. Prepare fishing diagram manually.", "Information", MessageBoxButton.OK, MessageBoxImage.Asterisk); return; } //Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0) //Поэтому от номера строки и столбца нужно отнимать 1 var cellNum = 2; //ARC SerialNumber SetCellValue(7, cellNum, tool.Top.SerialNumber); //Bottom Sub SerialNumber SetCellValue(11, cellNum, tool.Bottom.SerialNumber); cellNum = 5; //OD8 from ARC inspection SetCellValue(13, cellNum, tool.Top.ConnectionOne.Od); //OD7 SetCellValue(19, cellNum, arcTool.Od7); //OD6 SetCellValue(22, cellNum, arcTool.Od6); //OD5 SetCellValue(25, cellNum, arcTool.Od5); //OD4 SetCellValue(36, cellNum, arcTool.Od4); //OD3 SetCellValue(48, cellNum, arcTool.Od3); //OD2 SetCellValue(60, cellNum, arcTool.Od2); //OD1 SetCellValue(64, cellNum, arcTool.Od1); //OD Saver Sub SetCellValue(68, cellNum, tool.Bottom.ConnectionOne.Od); //ID Saver Sub SetCellValue(75, 7, tool.Bottom.ConnectionTwo.Id); cellNum = 9; //Top connection type SetCellValue(7, cellNum, tool.Top.ConnectionOne.ConnectionType); //Saver sub connection type SetCellValue(75, cellNum, tool.Bottom.ConnectionTwo.ConnectionType); cellNum = 10; var saverSubLength = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Bottom.Length)); //L10 SetCellValue(18, cellNum, (Convert.ToSingle(arcTool.L10, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L9 SetCellValue(21, cellNum, (Convert.ToSingle(arcTool.L9, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L8 SetCellValue(23, cellNum, (Convert.ToSingle(arcTool.L8, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L7 SetCellValue(25, cellNum, (Convert.ToSingle(arcTool.L7, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L6 SetCellValue(35, cellNum, (Convert.ToSingle(arcTool.L6, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L5 SetCellValue(38, cellNum, (Convert.ToSingle(arcTool.L5, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L4 SetCellValue(47, cellNum, (Convert.ToSingle(arcTool.L4, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L3 SetCellValue(50, cellNum, (Convert.ToSingle(arcTool.L3, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L2 SetCellValue(59, cellNum, (Convert.ToSingle(arcTool.L2, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L1 SetCellValue(62, cellNum, (Convert.ToSingle(arcTool.L1, CultureInfo.InvariantCulture) + saverSubLength).ToString("0.000")); //L total SetCellValue(40, 13, (inspectionLength + saverSubLength).ToString("0.000")); string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{ tool.Top.Name}_{tool.Top.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx"; //Сохранение изменённого файла using ( var file = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write)) { Book.Write(file); } } catch (Exception e) { MessageBox.Show(e.Message, "I have a bad feeling about this", MessageBoxButton.OK, MessageBoxImage.Error); return; } }
//解析excel public static List <Manage_Type> RecursionAnalyseExecel() { //1.读取 FileStream fs = File.OpenRead(@"C:\\work\222.xlsx"); //1.1获取execel文档 IWorkbook workBook = new XSSFWorkbook(fs); ISheet sheet = null; IRow row = null; ICell cell = null; //实例文档表数量 var countSheet = workBook.NumberOfSheets; var countRow = 0; // sheet.LastRowNum; var countCell = 0; // row.LastCellNum; var thePointedSheet = 0; Manage_Type model = new Manage_Type(); List <Manage_Type> listModel = new List <Manage_Type>(); var rootId = "1d9e56bd-3064-4dc8-aeef-5c4479903b05"; string[] ApproverAccount = { "Corporate Manuals", "Global & Site Specific Procedures", "Global & Site Specific Regulations", "Operation Instructions", "Technical Specifications", "SOP" }; var arrTemp = new Manage_Type[10]; arrTemp[0] = new Manage_Type() { Id = rootId, ApproverAccount = ApproverAccount[0] }; //数据表对象 sheet = workBook.GetSheetAt(0); for (int i = 0; i < countSheet; i++) { //获取当前sheet sheet = workBook.GetSheetAt(i); //设置制定页 if (thePointedSheet != 0) { sheet = workBook.GetSheetAt(thePointedSheet); countSheet = 0; } countRow = sheet.LastRowNum; for (int j = 1; j <= countRow; j++) { row = sheet.GetRow(j); countCell = row.Cells.Count; bool isNewRow = true; //判断是否为根目录 isNewRow = IsRootPath(row); //添加列 for (int m = 1; m <= countCell; m++) { cell = row.GetCell(m); if (cell == null || string.IsNullOrEmpty(cell.StringCellValue)) { continue; } model = new Manage_Type { ApproverAccount = ApproverAccount[workBook.GetSheetIndex(sheet)], TypeName = cell.StringCellValue, ParentId = arrTemp[m - 1].Id }; if (isNewRow) { arrTemp[m] = model; } listModel.Add(model); } } } return(listModel); }
/// <summary> /// 将excel中的多个SHEET的数据导入到DataSet中 /// </summary> /// <param name="Content">Stream</param> /// <param name="sheetNames">多个sheet名称</param> /// <returns>返回的DataSet</returns> public DataSet GetExcelToSet07(Stream Content, string[] sheetNames) //2007 Excel { XSSFWorkbook workbook = new XSSFWorkbook(Content); DataSet dataset = new DataSet(); foreach (string sheetname in sheetNames) { int index = workbook.GetSheetIndex(sheetname); if (index == -1) { return(null); } XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(sheetname); DataTable table = new DataTable(sheetname); //获取sheet的首行 XSSFRow headerRow = (XSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue); table.Columns.Add(column); } //最后一列的标号 即总的行数 int rowCount = sheet.LastRowNum + 1; for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); if (row == null || row.Cells.Count == 0) { continue; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { //dataRow[j] = row.GetCell(j).ToString(); if (row.GetCell(j).CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))//日期类型 { dataRow[j] = row.GetCell(j).DateCellValue.ToString("yyyy年MM月"); } else//其他数字类型 { if (!string.IsNullOrEmpty(row.GetCell(j).CellStyle.GetDataFormatString()) && row.GetCell(j).CellStyle.GetDataFormatString().Contains("%")) { dataRow[j] = row.GetCell(j).NumericCellValue * 100; } else { dataRow[j] = row.GetCell(j).NumericCellValue; } } } else if (row.GetCell(j).CellType == CellType.Blank)//空数据类型 { dataRow[j] = ""; } else if (row.GetCell(j).CellType == CellType.Formula)//公式类型 { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); dataRow[j] = eva.Evaluate(row.GetCell(j)).StringValue; } else //其他类型都按字符串类型来处理 { dataRow[j] = row.GetCell(j).StringCellValue; } } } table.Rows.Add(dataRow); } dataset.Tables.Add(table); } return(dataset); }
/// <summary> /// 读取excel2007 /// 当string.IsNullOrEmpty(sheetName)为TRUE时,则按sheetIndex读取 /// 否则,按sheetName读取 /// </summary> /// <param name="Content"></param> /// <returns></returns> public DataTable GetExcelToTable07(Stream Content, string sheetName, int sheetIndex = 0) //2007 Excel { XSSFWorkbook workbook = new XSSFWorkbook(Content); //获取excel的第一个sheet XSSFSheet sheet; if (string.IsNullOrEmpty(sheetName)) { sheet = (XSSFSheet)workbook.GetSheetAt(sheetIndex); } else { int index = workbook.GetSheetIndex(sheetName); if (index == -1) { return(null); } sheet = (XSSFSheet)workbook.GetSheet(sheetName); } sheet.ForceFormulaRecalculation = true; DataTable table = new DataTable(); /*try * {*/ //获取sheet的首行 XSSFRow headerRow = (XSSFRow)sheet.GetRow(0); //一行最后一个方格的编号 即总的列数 int cellCount = headerRow.LastCellNum; ICell cell = null; for (int i = headerRow.FirstCellNum; i < cellCount; i++) { cell = headerRow.GetCell(i); DataColumn column; if (headerRow.GetCell(i).CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { column = new DataColumn(cell.DateCellValue.ToString("yyyy年MM月")); } else//其他数字类型 { column = new DataColumn(cell.NumericCellValue.ToString()); } } else if (cell.CellType == CellType.Blank)//空数据类型 { column = new DataColumn(""); } else //其他类型都按字符串类型来处理 { column = new DataColumn(cell.StringCellValue); } cell = null; table.Columns.Add(column); } //最后一列的标号 即总的行数 int rowCount = sheet.LastRowNum + 1; for (int i = (sheet.FirstRowNum + 1); i < rowCount; i++) { XSSFRow row = (XSSFRow)sheet.GetRow(i); if (row == null || row.Cells.Count == 0) { continue; } DataRow dataRow = table.NewRow(); for (int j = row.FirstCellNum; j < cellCount; j++) { if (row.GetCell(j) != null) { cell = row.GetCell(j); if (cell.CellType == CellType.Numeric) { //NPOI中数字和日期都是NUMERIC类型的,这里对其进行判断是否是日期类型 if (HSSFDateUtil.IsCellDateFormatted(cell))//日期类型 { if (cell.CellStyle.GetDataFormatString().Contains("日") || cell.CellStyle.GetDataFormatString().Contains("d")) { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月dd日"); } else { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月"); } } else//其他数字类型 { if (!string.IsNullOrEmpty(cell.CellStyle.GetDataFormatString()) && cell.CellStyle.GetDataFormatString().Contains("%")) { dataRow[j] = cell.NumericCellValue * 100; } else if (cell.CellStyle.DataFormat.GetHashCode() == 3735609) { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月"); } else if (cell.CellStyle.DataFormat.GetHashCode() == 2031647) { dataRow[j] = cell.DateCellValue.ToString("yyyy年MM月dd日"); } else { dataRow[j] = cell.NumericCellValue; } // 处理科学计数 if (dataRow[j].ToString().Contains("E")) { Decimal dData = 0.0M; dData = Convert.ToDecimal(Decimal.Parse(dataRow[j].ToString(), System.Globalization.NumberStyles.Float)); dataRow[j] = dData; //FileLog.WriteLog("科学计数:" + dataRow[j].ToString()); } } } else if (cell.CellType == CellType.Blank)//空数据类型 { dataRow[j] = ""; } else if (cell.CellType == CellType.Formula)//公式类型 { XSSFFormulaEvaluator eva = new XSSFFormulaEvaluator(workbook); CellValue cv = eva.Evaluate(cell); switch (cv.CellType) { case CellType.Numeric: dataRow[j] = cv.NumberValue; break; case CellType.String: dataRow[j] = cv.StringValue; break; case CellType.Error: dataRow[j] = cv.ErrorValue; break; } //FileLog.WriteLog("公式:" + dataRow[j]); } else //其他类型都按字符串类型来处理 { cell.SetCellType(CellType.String); dataRow[j] = cell.StringCellValue; } } cell = null; } table.Rows.Add(dataRow); } workbook = null; sheet = null; /*} * catch (Exception ex) * { * FileLog.WriteLog("Exception: " + ex); * table = null; * }*/ return(table); }
public static void GenerateDmgWorkbook(List <AtkData> atkDatas, string name) { string sheetName = $"{name}_{DateTime.Today.Year}-{DateTime.Today.Month}-{DateTime.Today.Day}"; XSSFWorkbook dmgWorkbook = IOUtils.OpenOrCreateDmgWorkbook(); if (dmgWorkbook.GetSheetIndex(sheetName) != -1) { ConsoleLog.Error("Excel生成", "今天已进行过统计,删除旧表"); dmgWorkbook.RemoveSheetAt(dmgWorkbook.GetSheetIndex(sheetName)); } ISheet dmgSheet = dmgWorkbook.CreateSheet(sheetName); //新建一个工作表 dmgSheet.CreateRow(0); //写入第一行数据 IRow firstRow = dmgSheet.GetRow(0); int rowCount = 0; firstRow.CreateCell(rowCount++).SetCellValue("QQ"); firstRow.CreateCell(rowCount++).SetCellValue("昵称"); firstRow.CreateCell(rowCount++).SetCellValue("总伤害"); firstRow.CreateCell(rowCount++).SetCellValue("平均伤害"); firstRow.CreateCell(rowCount++).SetCellValue("出刀次数"); firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)"); firstRow.CreateCell(rowCount++).SetCellValue("对一王总伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对一王平均伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对一王出刀次数"); firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)"); firstRow.CreateCell(rowCount++).SetCellValue("对二王总伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对二王平均伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对二王出刀次数"); firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)"); firstRow.CreateCell(rowCount++).SetCellValue("对三王总伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对三王平均伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对三王出刀次数"); firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)"); firstRow.CreateCell(rowCount++).SetCellValue("对四王总伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对四王平均伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对四王出刀次数"); firstRow.CreateCell(rowCount++).SetCellValue("RSD(%)"); firstRow.CreateCell(rowCount++).SetCellValue("对五王总伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对五王平均伤害"); firstRow.CreateCell(rowCount++).SetCellValue("对五王出刀次数"); firstRow.CreateCell(rowCount).SetCellValue("RSD(%)"); //写入伤害数据 int rowNum = 1; foreach (AtkData atkData in atkDatas) { int colNum = 0; dmgSheet.CreateRow(rowNum); //创建行 IRow sheetRow = dmgSheet.GetRow(rowNum); // 获得行索引 //总伤害数据 sheetRow.CreateCell(colNum++).SetCellValue(atkData.Uid.ToString()); sheetRow.CreateCell(colNum++).SetCellValue(atkData.Name); sheetRow.CreateCell(colNum++).SetCellValue(atkData.TotalDmg); sheetRow.CreateCell(colNum++).SetCellValue(double.IsNaN(atkData.TotalAvgDmg) ? 0 : atkData.TotalAvgDmg); sheetRow.CreateCell(colNum++).SetCellValue(atkData.TotalTimes); sheetRow.CreateCell(colNum++).SetCellValue(atkData.Deviation); //boss伤害数据 foreach (BossDmg bossDmg in atkData.BossDmgInfos) { sheetRow.CreateCell(colNum++).SetCellValue(bossDmg.Dmg); sheetRow.CreateCell(colNum++).SetCellValue(double.IsNaN(bossDmg.AvgDmg) ? 0 : bossDmg.AvgDmg); sheetRow.CreateCell(colNum++).SetCellValue(bossDmg.Count); sheetRow.CreateCell(colNum++).SetCellValue(bossDmg.Deviation); } rowNum++; } //设置自动宽度 for (int i = 0; i < 20; i++) { dmgSheet.AutoSizeColumn(i); } //写入数据到文件 IOUtils.WriteToWorkbookFile(dmgWorkbook); }
public override void UpdateConditionalFormatting(FormulaShifter Shifter) { XSSFSheet xsheet = (XSSFSheet)sheet; XSSFWorkbook wb = xsheet.Workbook as XSSFWorkbook; int sheetIndex = wb.GetSheetIndex(sheet); XSSFEvaluationWorkbook fpb = XSSFEvaluationWorkbook.Create(wb); CT_Worksheet ctWorksheet = xsheet.GetCTWorksheet(); List <CT_ConditionalFormatting> conditionalFormattingArray = ctWorksheet.conditionalFormatting; // iterate backwards due to possible calls to ctWorksheet.removeConditionalFormatting(j) for (int j = conditionalFormattingArray.Count - 1; j >= 0; j--) { CT_ConditionalFormatting cf = conditionalFormattingArray[j]; List <CellRangeAddress> cellRanges = new List <CellRangeAddress>(); String[] regions = cf.sqref.ToString().Split(new char[] { ' ' }); for (int i = 0; i < regions.Length; i++) { cellRanges.Add(CellRangeAddress.ValueOf(regions[i])); } bool Changed = false; List <CellRangeAddress> temp = new List <CellRangeAddress>(); for (int i = 0; i < cellRanges.Count; i++) { CellRangeAddress craOld = cellRanges[i]; CellRangeAddress craNew = ShiftRange(Shifter, craOld, sheetIndex); if (craNew == null) { Changed = true; continue; } temp.Add(craNew); if (craNew != craOld) { Changed = true; } } if (Changed) { int nRanges = temp.Count; if (nRanges == 0) { conditionalFormattingArray.RemoveAt(j); continue; } string refs = string.Empty; foreach (CellRangeAddress a in temp) { if (refs.Length == 0) { refs = a.FormatAsString(); } else { refs += " " + a.FormatAsString(); } } cf.sqref = refs; } foreach (CT_CfRule cfRule in cf.cfRule) { List <String> formulas = cfRule.formula; for (int i = 0; i < formulas.Count; i++) { String formula = formulas[i]; Ptg[] ptgs = FormulaParser.Parse(formula, fpb, FormulaType.Cell, sheetIndex, -1); if (Shifter.AdjustFormula(ptgs, sheetIndex)) { String ShiftedFmla = FormulaRenderer.ToFormulaString(fpb, ptgs); formulas[i] = ShiftedFmla; } } } } }
public override void RemoveSheet(string name) { int index = workbook.GetSheetIndex(name); workbook.RemoveAt(index); }
public virtual void PassDataToExcel(ISmartTool tool) { switch (tool.Type) { case SmartToolType.Telescope: TemplateFileName = "TeleScope Diagram.xlsx"; break; case SmartToolType.Gdis: TemplateFileName = "GDIS Diagram.xlsx"; break; } var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}"; try { using ( var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { Book = new XSSFWorkbook(file); } Sheet = Book.GetSheetAt(0); Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{tool.Top.Name}_{tool.Top.SerialNumber}"); //Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0) //Поэтому от номера строки и столбца нужно отнимать 1 var cellNum = 2; //MDC SerialNumber SetCellValue(7, cellNum, tool.Middle.SerialNumber); //Top Sub SerialNumber SetCellValue(9, cellNum, tool.Top.SerialNumber); //Bottom Sub SerialNumber SetCellValue(11, cellNum, tool.Bottom.SerialNumber); cellNum = 4; //Top Sub OD SetCellValue(14, cellNum, tool.Top.ConnectionOne.Od); //MDC OD SetCellValue(20, cellNum, tool.Middle.ConnectionOne.Od); //Bottom Sub OD SetCellValue(64, cellNum, tool.Bottom.ConnectionOne.Od); //Bottom Sub ID SetCellValue(75, 7, tool.Bottom.ConnectionTwo.Id); cellNum = 9; //Top Sub Treadsize SetCellValue(7, cellNum, tool.Top.ConnectionOne.TreadSize); //Bottom Sub Treadsize SetCellValue(75, cellNum, tool.Bottom.ConnectionTwo.TreadSize); cellNum = 10; //L var lMdc = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Middle.Length)); var lBotSub = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Bottom.Length)); var lTopSub = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(tool.Top.Length)); //L MDC + Bottom Sub SetCellValue(17, cellNum, (lMdc + lBotSub).ToString("0.000")); //L MDC + Bottom Sub SetCellValue(61, cellNum, lBotSub.ToString("0.000")); //L MDC + Bottom Sub + Top Sub SetCellValue(40, 13, (lMdc + lBotSub + lTopSub).ToString("0.000")); string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{ tool.Top.Name}_{tool.Top.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx"; //Сохранение изменённого файла using ( var file = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write)) { Book.Write(file); } } catch (Exception e) { MessageBox.Show(e.Message, "I have a bad feeling about this", MessageBoxButton.OK, MessageBoxImage.Error); return; } }
public override void PassDataToExcel(IParsedData data) { var crossoverData = (CrossoverSubParsedData)data; var crossoverType = crossoverData.Type; switch (crossoverType) { case CrossoverType.Type1: TemplateFileName = "Crossover Sub Type 1 Diagram.xlsx"; break; case CrossoverType.Type2: TemplateFileName = "Crossover Sub Type 2 Diagram.xlsx"; break; case CrossoverType.Type3: TemplateFileName = "Crossover Sub Type 3 Diagram.xlsx"; break; case CrossoverType.Type4: TemplateFileName = "Crossover Sub Type 4 Diagram.xlsx"; break; case CrossoverType.NotDefined: MessageBox.Show("Crossover Sub Type Not Defined", "Information message", MessageBoxButton.OK, MessageBoxImage.Asterisk); return; } if (string.IsNullOrEmpty(TemplateFileName)) { return; } var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}"; try { using ( var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { _xlsBook = new XSSFWorkbook(file); } _xlsBook.SetForceFormulaRecalculation(true); Sheet = _xlsBook.GetSheetAt(0); Sheet = _xlsBook.GetSheetAt(0); _xlsBook.SetSheetName(_xlsBook.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}"); //Запись заголовка FillHeader(crossoverData); //cellNum - Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные var cellNum = 5; //SerialNumber SetCellValue(14, cellNum, crossoverData.SerialNumber); //TOP SetCellValue(16, cellNum, crossoverData.ConnectionOne.TreadSize); //BOT SetCellValue(17, cellNum, crossoverData.ConnectionTwo.TreadSize); //L var inches = InchesValueRetriever.GetInchesValue(crossoverData.Length); SetCellValue(19, cellNum, inches.Equals(0) ? string.Empty : LengthConverter.InchesToMeters(inches).ToString("0.000")); switch (crossoverType) { case CrossoverType.Type1: //OD SetCellValue(20, cellNum, crossoverData.ConnectionOne.Od); //ID SetCellValue(21, cellNum, crossoverData.ConnectionTwo.Id); break; case CrossoverType.Type2: //ID1 SetCellValue(20, cellNum, crossoverData.ConnectionOne.Id); //ID2 SetCellValue(21, cellNum, crossoverData.ConnectionTwo.Id); break; case CrossoverType.Type3: case CrossoverType.Type4: //FishingNeck SetCellValue(20, cellNum, crossoverData.FishingNeck); //Od1 SetCellValue(21, cellNum, crossoverData.ConnectionOne.Od); //ID2 SetCellValue(23, cellNum, crossoverData.ConnectionTwo.Id); //Od2 SetCellValue(24, cellNum, crossoverData.ConnectionTwo.Od); break; } string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{ crossoverData.Name}_{crossoverData.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-s")}.xlsx"; //Сохранение изменённого файла using ( var file = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { _xlsBook.Write(file); } } catch (Exception e) { MessageBox.Show($"Something is wrong: {e.Message}", "Viva La Resistance!!!", MessageBoxButton.OK, MessageBoxImage.Error); return; } }
public override void PassDataToExcel(IParsedData data) { var stabilizerData = (IStabilizerParsedData)data; //... (прдолжение) чтобы использовать поля из StabilizerParsedData, необходимо вот так вот //как здесь выполнить приведение к призводному типу. Просто небольшой нюанс, если вы будете делать свои версии класса ParsedData if (string.IsNullOrEmpty(TemplateFileName)) { return; } var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}"; try { using ( var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { _xlsBook = new XSSFWorkbook(file); } _xlsBook.SetForceFormulaRecalculation(true); Sheet = _xlsBook.GetSheetAt(0); Sheet = _xlsBook.GetSheetAt(0); _xlsBook.SetSheetName(_xlsBook.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}"); //Запись заголовка FillHeader(stabilizerData); //cellNum - Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные var cellNum = 6; //SerialNumber SetCellValue(14, cellNum, stabilizerData.SerialNumber); //TOP SetCellValue(17, cellNum, stabilizerData.ConnectionOne.TreadSize); //BOT SetCellValue(18, cellNum, stabilizerData.ConnectionTwo.TreadSize); //L var inches = InchesValueRetriever.GetInchesValue(stabilizerData.Length); SetCellValue(22, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000")); //L1 inches = InchesValueRetriever.GetInchesValue(stabilizerData.FishingNeckTongSpace); SetCellValue(23, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000")); //OD SetCellValue(29, cellNum, stabilizerData.ConnectionOne.Od); //ID SetCellValue(30, cellNum, stabilizerData.ConnectionTwo.Od); //MaxOD SetCellValue(31, cellNum, stabilizerData.StabilizerOd); //BladeLength inches = InchesValueRetriever.GetInchesValue(stabilizerData.LobeLength); SetCellValue(32, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000")); //BladeWidth SetCellValue(34, cellNum, stabilizerData.LobeWidth); string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{ stabilizerData.Name}_{stabilizerData.SerialNumber}_FishingDiagram_{ DateTime.Now.ToString("yy-MM-dd-HH-mm-s")}.xlsx"; //Сохранение изменённого файла using ( var file = new FileStream(fileName, FileMode.Create, FileAccess.Write)) { _xlsBook.Write(file); } } catch (Exception e) { MessageBox.Show($"Something is wrong: {e.Message}", "Viva La Resistance!!!", MessageBoxButton.OK, MessageBoxImage.Error); return; } }
public override void PassDataToExcel(IParsedData data) { if (string.IsNullOrEmpty(TemplateFileName)) { return; } var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}"; try { using ( var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { Book = new XSSFWorkbook(file); } Sheet = Book.GetSheetAt(0); Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}"); //Заполнение заголовка (метод прописан в ExcelProcessor.cs) FillHeader(data); //Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0) //Поэтому от номера строки и столбца нужно отнимать 1 var cellNum = 5; //SerialNumber SetCellValue(14, cellNum, data.SerialNumber); //соответствует 22 строке в шаблоне и т.д. //L var inches = InchesValueRetriever.GetInchesValue(data.Length); SetCellValue(19, cellNum, LengthConverter.InchesToMeters(inches).ToString("0.000")); //Od1 SetCellValue(20, cellNum, data.ConnectionOne.Od); //ID SetCellValue(21, cellNum, data.ConnectionTwo.Od); //Top SetCellValue(16, cellNum, data.ConnectionOne.TreadSize); //Bottom SetCellValue(17, cellNum, data.ConnectionTwo.TreadSize); //соответствует 27 строке в шаблоне //var totalFishingDiagram = // TotalFishingDiagramFile.GetTotalFishingDiagramFileStream( // $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\out\Field_Pad_Well_FishingDiagram_{ // SessionStartTime}.xlsx"); //var totalBook = new XSSFWorkbook(totalFishingDiagram); string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{ data.Name}_{data.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx"; //Сохранение изменённого файла using ( var file = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write)) { Book.Write(file); } } catch (Exception e) { MessageBox.Show($"I have a bad feeling about this: {e.Message}", "Viva La Resistance!!!", MessageBoxButton.OK, MessageBoxImage.Error); return; } }
public int GetExternalSheetIndex(String sheetName) { int sheetIndex = _uBook.GetSheetIndex(sheetName); return(ConvertToExternalSheetIndex(sheetIndex)); }
public void GenerateDocument(List <string> tableNames, string templatePath, string outputDocPath) { const string sheetName4TableListTempalte = "#TableListTemplate"; const string sheetName4TableSchemaTempalte = "#TableSchemaTemplate"; const string tagName4TableNo = "#table.no"; const string tagName4TableName = "#table.name"; const string tagName4TableDesc = "#table.description"; const string tagName4TableView = "#table.view"; const string tagName4TableType = "#table.type"; const string tagName4ColumnNo = "#column.no"; const string tagName4ColumnName = "#column.name"; const string tagName4ColumnPK = "#column.pk"; const string tagName4ColumnFK = "#column.fk"; const string tagName4ColumnFKReference = "#column.fkreference"; const string tagName4ColumnNullable = "#column.nullable"; const string tagName4ColumnIdentity = "#column.identity"; const string tagName4ColumnDataType = "#column.datatype"; const string tagName4ColumnDefault = "#column.default"; const string tagName4ColumnDesc = "#column.description"; // open template file XSSFWorkbook workbook; using (FileStream fs = new FileStream(templatePath, FileMode.Open, FileAccess.Read)) { workbook = new XSSFWorkbook(fs); } // TABLE LIST // clone tempalte table list sheet & rename sheet var tableSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableListTempalte)); workbook.SetSheetName(workbook.NumberOfSheets - 1, "Table List"); var tableIndex = 1; foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName))) { var noLocation = tableSheet.FindCellLocation(tagName4TableNo); if (noLocation.HasValue) { // copy the table info row from tempalte tableSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1); var newRowIndex = noLocation.Value.Y; tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableNo, tableIndex.ToString()); tableSheet.SetFirstMatchCellHyperlinkInRow(newRowIndex, tagName4TableName, table.TableName, tableIndex.ToString()); tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableDesc, table.Description); tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableView, table.IsViewTable ? "V" : ""); tableSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4TableType, table.TableType.ToTitleCase()); tableIndex++; } } // remove the last template row tableSheet.RemoveFirstMatchRow(tagName4TableNo); // TABLE SCHEMA SHEETS tableIndex = 1; foreach (var table in DbTables.Where(t => tableNames.Contains(t.TableName))) { // clone tempalte schema sheet & rename sheet var schemaSheet = workbook.CloneSheet(workbook.GetSheetIndex(sheetName4TableSchemaTempalte)); workbook.SetSheetName(workbook.NumberOfSheets - 1, tableIndex.ToString() /*table.TableName*/); // table name & description schemaSheet.SetFirstMatchCellContent(tagName4TableName, table.TableName); schemaSheet.SetFirstMatchCellContent(tagName4TableDesc, table.Description); schemaSheet.SetFirstMatchCellContent(tagName4TableView, table.IsViewTable ? "V" : ""); schemaSheet.SetFirstMatchCellContent(tagName4TableType, table.TableType.ToTitleCase()); // prepare each column info foreach (var column in table.Columns) { var noLocation = schemaSheet.FindCellLocation(tagName4ColumnNo); if (noLocation.HasValue) { // copy the column info row from tempalte schemaSheet.CopyRow(noLocation.Value.Y, noLocation.Value.Y + 1); var newRowIndex = noLocation.Value.Y; schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNo, column.No.ToString()); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnName, column.ColumnName); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnPK, column.IsPrimaryKey ? "V" : ""); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFK, column.IsForeignKey ? "V" : ""); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnFKReference, column.FkReferencedInfo); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnNullable, column.IsNullable ? "V" : ""); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnIdentity, column.IsIdentity ? "V" : ""); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDataType, column.FullDataType); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDefault, column.Default); schemaSheet.SetFirstMatchCellContentInRow(newRowIndex, tagName4ColumnDesc, column.Description); } } // remove the last template row schemaSheet.RemoveFirstMatchRow(tagName4ColumnNo); tableIndex++; } // remove template sheet workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableListTempalte)); workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName4TableSchemaTempalte)); // save as another excel file using (FileStream stream = new FileStream(outputDocPath, FileMode.Create, FileAccess.Write)) { workbook.Write(stream); } // set null workbook = null; }
/// <summary> ///從datatable中導出到excel /// </summary> /// <param name="dtSource"> datatable數據源</param> /// <param name="strHeaderText">表名</param> /// <param name="fs">文件流</param> /// <param name="readfs">內存流</param> /// <param name="sheetnum"> sheet索引</param> static void ExportDTI(DataTable dtSource, string strHeaderText, FileStream fs, MemoryStream readfs, Dictionary <string, string> dir, int sheetnum) { IWorkbook workbook = new XSSFWorkbook(); if (readfs.Length > 0 && sheetnum > 0) { workbook = WorkbookFactory.Create(readfs); } ISheet sheet = null; ICellStyle dateStyle = workbook.CreateCellStyle(); IDataFormat format = workbook.CreateDataFormat(); dateStyle.DataFormat = format.GetFormat(" yyyy-mm-dd "); //取得列寬 int[] arrColWidth = new int[dtSource.Columns.Count]; foreach (DataColumn item in dtSource.Columns) { arrColWidth[item.Ordinal] = Encoding.GetEncoding(936).GetBytes(item.ColumnName.ToString()).Length; } for (int i = 0; i < dtSource.Rows.Count; i++) { for (int j = 0; j < dtSource.Columns.Count; j++) { int intTemp = Encoding.GetEncoding(936).GetBytes(dtSource.Rows[i][j].ToString()).Length; if (intTemp > arrColWidth[j]) { arrColWidth[j] = intTemp; } } } int rowIndex = 0; foreach (DataRow row in dtSource.Rows) { #region 新建表,填充表頭,填充列首,樣式 if (rowIndex == 0) { #region 表頭及樣式 { string sheetName = strHeaderText + (sheetnum == 0 ? "" : sheetnum.ToString()); if (workbook.GetSheetIndex(sheetName) >= 0) { workbook.RemoveSheetAt(workbook.GetSheetIndex(sheetName)); } sheet = workbook.CreateSheet(sheetName); sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, dtSource.Columns.Count - 1)); IRow headerRow = sheet.CreateRow(0); headerRow.HeightInPoints = 25; headerRow.CreateCell(0).SetCellValue(strHeaderText); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 20; font.Boldweight = 700; headStyle.SetFont(font); headerRow.GetCell(0).CellStyle = headStyle; } #endregion #region 列首及樣式 { IRow headerRow = sheet.CreateRow(1); ICellStyle headStyle = workbook.CreateCellStyle(); headStyle.Alignment = HorizontalAlignment.Center; IFont font = workbook.CreateFont(); font.FontHeightInPoints = 10; font.Boldweight = 700; headStyle.SetFont(font); foreach (DataColumn column in dtSource.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(dir[column.ColumnName]); headerRow.GetCell(column.Ordinal).CellStyle = headStyle; //設置列寬 sheet.SetColumnWidth(column.Ordinal, (arrColWidth[column.Ordinal] + 1) * 256 * 2); } } #endregion rowIndex = 2; } #endregion #region 填充內容 IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in dtSource.Columns) { ICell newCell = dataRow.CreateCell(column.Ordinal); string drValue = row[column].ToString(); switch (column.DataType.ToString()) { case " System.String ": //字符串類型 double result; if (isNumeric(drValue, out result)) { double.TryParse(drValue, out result); newCell.SetCellValue(result); break; } else { newCell.SetCellValue(drValue); break; } case " System.DateTime ": //日期類型 DateTime dateV; DateTime.TryParse(drValue, out dateV); newCell.SetCellValue(dateV); newCell.CellStyle = dateStyle; //格式化顯示 break; case " System.Boolean ": //布爾型 bool boolV = false; bool.TryParse(drValue, out boolV); newCell.SetCellValue(boolV); break; case " System.Int16 ": //整型 case " System.Int32 ": case " System.Int64 ": case " System.Byte ": int intV = 0; int.TryParse(drValue, out intV); newCell.SetCellValue(intV); break; case " System.Decimal ": //浮點型 case " System.Double ": double doubV = 0; double.TryParse(drValue, out doubV); newCell.SetCellValue(doubV); break; case " System.DBNull ": //空值處理 newCell.SetCellValue(""); break; default: newCell.SetCellValue(drValue.ToString()); break; } } #endregion rowIndex++; } workbook.Write(fs); fs.Close(); }
// 上传数据模板 // POST api/inputapi/upload public Object upload() { System.Web.HttpFileCollection _file = System.Web.HttpContext.Current.Request.Files; int count = 0; if (_file.Count > 0) { for (int i = 0; i < _file.Count; i++) { //文件大小 //long size = _file[i].ContentLength; //文件类型 //string type = _file[i].ContentType; //文件名 string _filename = _file[i].FileName; //文件格式 string _tp = System.IO.Path.GetExtension(_filename); if (_tp.ToLower() == ".xlsx") { BaseService bs = new BaseService(); string userid = UtilFunction.GetCookie("uid"); string username = UtilFunction.GetCookie("uname"); string _directory2 = "UploadFiles/" + username; string path = _directory + _directory2; string key = null; List <Object> parames = new List <Object>(); Dictionary <string, List <Object> > map = new Dictionary <string, List <Object> >(); //获取文件流 //System.IO.Stream stream = _file[i].InputStream; if (!Directory.Exists(path)) { Directory.CreateDirectory(path); } path += "/" + _filename; if (File.Exists(path)) // 如果当前用户下已有同名文件,则执行update操作 { key = "updateFileInfo"; parames.Add(DateTime.Now); parames.Add(userid); parames.Add(username); parames.Add(_filename); parames.Add(userid); } else // insert { key = "insertFileInfo"; parames.Add(_filename); parames.Add(_directory2); parames.Add(DateTime.Now); parames.Add(userid); parames.Add(username); } map.Add(key, parames); // 上传文件 _file[i].SaveAs(path); XSSFWorkbook workbook = new XSSFWorkbook(_file[i].InputStream); int index = workbook.GetSheetIndex("填写模板"); if (index > -1) { // 上传记录入库 count = bs.InsUpdDelDataTableToParam(map); } else { File.Delete(path); return(new { error = "《" + _filename + "》文件中必须有一个sheet名叫“填写模板”!" }); } } else { return(new { error = "《" + _filename + "》只允许上传后缀为.xlsx的文件!" }); } } } if (count > 0) { return(new { msg = "数据模板上传成功!" }); } else { return(new { error = "数据模板上传失败!" }); } }
public override void PassDataToExcel(IParsedData data) { if (string.IsNullOrEmpty(TemplateFileName)) { return; } var filePath = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\misc\{TemplateFileName}"; try { using ( var file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { Book = new XSSFWorkbook(file); } Sheet = Book.GetSheetAt(0); Book.SetSheetName(Book.GetSheetIndex(Sheet), $"{data.Name}_{data.SerialNumber}"); var nmdcData = new NmdcFData(); if (!nmdcData.Tools.ContainsKey(data.SerialNumber)) { MessageBox.Show("No such NMDC-F in library", "Information", MessageBoxButton.OK, MessageBoxImage.Asterisk); return; } var nmdcTool = nmdcData.Tools[data.SerialNumber]; var inspectionLength = LengthConverter.InchesToMeters(InchesValueRetriever.GetInchesValue(data.Length)); var arrayLength = Convert.ToSingle(nmdcTool.L, CultureInfo.InvariantCulture); var difference = Math.Abs(inspectionLength - arrayLength); if (difference > 0.025f) { MessageBox.Show($"Collar length {inspectionLength} doesn't match. Should be about {arrayLength}. Difference is {difference}. Prepare fishing diagram manually.", "Information", MessageBoxButton.OK, MessageBoxImage.Asterisk); return; } //Заполнение заголовка (сам метод внизу) FillHeader(data); //Номер ячейки (в контексте таблицы - столбца), в которую вставляются данные (нумерация ячеек в коде начинается с 0) //Поэтому от номера строки и столбца нужно отнимать 1 var cellNum = 4; //L SetCellValue(12, cellNum, inspectionLength.ToString("0.000")); //L12 SetCellValue(15, cellNum, nmdcTool.L12); //L11 SetCellValue(16, cellNum, nmdcTool.L11); //L10 SetCellValue(20, cellNum, nmdcTool.L10); //L9 SetCellValue(21, cellNum, nmdcTool.L9); //L8 SetCellValue(24, cellNum, nmdcTool.L8); //L7 SetCellValue(25, cellNum, nmdcTool.L7); //L6 SetCellValue(29, cellNum, nmdcTool.L6); //L5 SetCellValue(30, cellNum, nmdcTool.L5); //L4 SetCellValue(33, cellNum, nmdcTool.L4); //L3 SetCellValue(34, cellNum, nmdcTool.L3); //L2 SetCellValue(38, cellNum, nmdcTool.L2); //L1 SetCellValue(39, cellNum, nmdcTool.L1); cellNum = 8; //SerialNumber SetCellValue(12, cellNum, data.SerialNumber); //соответствует 22 строке в шаблоне и т.д. //OD SetCellValue(13, cellNum, data.ConnectionOne.Od); //ID SetCellValue(14, cellNum, data.ConnectionTwo.Id); //Od6 SetCellValue(19, cellNum, nmdcTool.Od6); //Od5 SetCellValue(20, cellNum, nmdcTool.Od5); //Od4 SetCellValue(21, cellNum, nmdcTool.Od4); //Od3 SetCellValue(22, cellNum, nmdcTool.Od3); //Od2 SetCellValue(23, cellNum, nmdcTool.Od2); //Od1 SetCellValue(24, cellNum, nmdcTool.Od1); string fileName = $@"{Path.GetDirectoryName(Assembly.GetExecutingAssembly().Location)}\work\{ data.Name}_{data.SerialNumber}_FishingDiagram_{DateTime.Now.ToString("yy-MM-dd-HH-mm-ss")}.xlsx"; //Сохранение изменённого файла using ( var file = new FileStream(fileName, FileMode.CreateNew, FileAccess.Write)) { Book.Write(file); } } catch (Exception e) { MessageBox.Show(e.Message, "I have a bad feeling about this", MessageBoxButton.OK, MessageBoxImage.Error); return; } }