/// <summary> /// 将DATATABLE中的数据写入到xls中 /// </summary> /// <param name="dta">表类型 弱类型</param> /// <returns></returns> /// public string Creating_Excel_Information(DataTable dta) { HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿 workbook2003.CreateSheet("Sheet1"); //新建1个Sheet工作表 HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1"); //获取名称为Sheet1的工作表 //对工作表先添加行,下标从0开始 //MessageBox.Show(dta.Rows.Count.ToString()); //MessageBox.Show(dta.Rows[0][0].ToString()); for (int i = 0; i < dta.Rows.Count; i++) { IRow row = SheetOne.CreateRow(i); //HSSFRow SheetRow = (HSSFRow)SheetOne.GetRow(i); //HSSFCell[] SheetCell = new HSSFCell[3]; //SheetCell[0] = (HSSFCell)SheetRow.CreateCell(0); //SheetCell[1] = (HSSFCell)SheetRow.CreateCell(1); //SheetCell[2] = (HSSFCell)SheetRow.CreateCell(2); //SheetCell[0].SetCellValue(dta.Rows[i][0].ToString()); //SheetCell[1].SetCellValue(dta.Rows[i][1].ToString()); //SheetCell[2].SetCellValue(dta.Rows[i][2].ToString()); //MessageBox.Show(dta.Rows[i][2].ToString()); row.CreateCell(0).SetCellValue(dta.Rows[i][0].ToString()); row.CreateCell(1).SetCellValue(dta.Rows[i][1].ToString()); row.CreateCell(2).SetCellValue(dta.Rows[i][2].ToString()); row.CreateCell(3).SetCellValue(dta.Rows[i][3].ToString()); row.CreateCell(4).SetCellValue(dta.Rows[i][4].ToString()); } string saveAsPath = xlsSavePath + dta.Rows[0][1].ToString() + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(":", "_") + "_new.xls"; //string saveAsPath = xlsSavePath + dta.Rows[0][1].ToString() + "_" + DateTime.Now.ToString().Replace("/", "_").Replace(":", "_") + "_new.xls"; FileStream file2003 = new FileStream(saveAsPath, FileMode.Create); workbook2003.Write(file2003); file2003.Close(); workbook2003.Close(); return(saveAsPath); }
/// <summary> /// Excel添加一行 /// </summary> private void addExcel() { FileStream fs = new FileStream(savePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite); POIFSFileSystem ps = new POIFSFileSystem(fs); HSSFWorkbook wk = new HSSFWorkbook(ps); ISheet tb = wk.GetSheet("Sheet01"); int number = tb.LastRowNum; FileStream fs1 = new FileStream(savePath, FileMode.OpenOrCreate, FileAccess.Write, FileShare.ReadWrite); IRow row1 = tb.CreateRow(number + 1); row1.CreateCell(0).SetCellValue(BingRen.StepSpeed); row1.CreateCell(1).SetCellValue(BingRen.RightSpeedM); row1.CreateCell(2).SetCellValue(BingRen.RightSpeedD); row1.CreateCell(3).SetCellValue(BingRen.LeftSpeedM); row1.CreateCell(4).SetCellValue(BingRen.LeftSpeedD); row1.CreateCell(5).SetCellValue(BingRen.CyleSpeedM); row1.CreateCell(6).SetCellValue(BingRen.CyleSpeedD); row1.CreateCell(7).SetCellValue(BingRen.RightStepLengthM); row1.CreateCell(8).SetCellValue(BingRen.RightStepLengthD); row1.CreateCell(9).SetCellValue(BingRen.LeftStepLengthM); row1.CreateCell(10).SetCellValue(BingRen.LeftStepLengthD); row1.CreateCell(11).SetCellValue(BingRen.StepLengthBalance); row1.CreateCell(12).SetCellValue(BingRen.RightStepHeightM); row1.CreateCell(13).SetCellValue(BingRen.RightStepHeightD); row1.CreateCell(14).SetCellValue(BingRen.LeftStepHeightM); row1.CreateCell(15).SetCellValue(BingRen.LeftStepHeightD); row1.CreateCell(16).SetCellValue(BingRen.StepHeightBalance); row1.CreateCell(17).SetCellValue(BingRen.StepWidthM); row1.CreateCell(18).SetCellValue(BingRen.StepWidthD); row1.CreateCell(19).SetCellValue(BingRen.StepDistanceM); row1.CreateCell(20).SetCellValue(BingRen.StepDistanceD); row1.CreateCell(21).SetCellValue(BingRen.ZM); row1.CreateCell(22).SetCellValue(BingRen.ZD); row1.CreateCell(23).SetCellValue(BingRen.LeftFootAberrance); row1.CreateCell(24).SetCellValue(BingRen.RightFootAberrance); fs1.Flush(); wk.Write(fs1); wk.Close(); fs1.Close(); }
public void TestNamedCell_1() { // setup for this Testcase String sheetName = "Test Named Cell"; String cellName = "A name for a named cell"; String cellValue = "TEST Value"; HSSFWorkbook wb = new HSSFWorkbook(); NPOI.SS.UserModel.Sheet sheet = wb.CreateSheet(sheetName); sheet.CreateRow(0).CreateCell(0).SetCellValue(new HSSFRichTextString(cellValue)); // Create named range for a single cell using areareference NPOI.SS.UserModel.Name namedCell = wb.CreateName(); namedCell.NameName = (cellName); String reference = "'" + sheetName + "'" + "!A1:A1"; namedCell.RefersToFormula = (reference); // retrieve the newly Created named range int namedCellIdx = wb.GetNameIndex(cellName); NPOI.SS.UserModel.Name aNamedCell = wb.GetNameAt(namedCellIdx); Assert.IsNotNull(aNamedCell); // retrieve the cell at the named range and Test its contents AreaReference aref = new AreaReference(aNamedCell.RefersToFormula); Assert.IsTrue(aref.IsSingleCell, "Should be exactly 1 cell in the named cell :'" + cellName + "'"); CellReference cref = aref.FirstCell; Assert.IsNotNull(cref); NPOI.SS.UserModel.Sheet s = wb.GetSheet(cref.SheetName); Assert.IsNotNull(s); Row r = sheet.GetRow(cref.Row); Cell c = r.GetCell(cref.Col); String contents = c.RichStringCellValue.String; Assert.AreEqual(contents, cellValue, "Contents of cell retrieved by its named reference"); }
/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的数据</param> /// <param name="coluids">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> public static void UpdateExcel(string outputFile, string sheetname, string[][] updateData, int[] coluids, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); readfile.Close(); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int j = 0; j < coluids.Length; j++) { for (int i = 0; i < updateData[j].Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluids[j]) == null) { sheet1.GetRow(i + rowid).CreateCell(coluids[j]); } sheet1.GetRow(i + rowid).GetCell(coluids[j]).SetCellValue(updateData[j][i]); } catch (Exception ex) { } } } try { FileStream writefile = new FileStream(outputFile, FileMode.Create); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { } }
private async void btnLoad_Click(object sender, EventArgs e) { this.btnLoad.Enabled = false; Stream fs = new FileStream("G:\\JIRA.xls", FileMode.OpenOrCreate, FileAccess.ReadWrite); IWorkbook workbook = new HSSFWorkbook(fs); ISheet sheet = workbook.GetSheet("All Case"); if (sheet != null) { IRow firstRow = sheet.GetRow(0); int cellCount = firstRow.LastCellNum; // 一行最后一个cell的编号 即总的列数 int rowCount = sheet.LastRowNum; // 最后一列的标号 int startRow = 1; for (int i = startRow; i <= rowCount; ++i) { IRow row = sheet.GetRow(i); if (row == null) { continue; //没有数据的行默认是null } string casenum = row.GetCell(1).ToString(); List <string> cases = new List <string>(); cases.Add(casenum); var getCaselist = SalesforceProxy.GetCaseList(cases); var caseList = await getCaselist; foreach (var caseInfo in caseList) { if (caseInfo != null) { System.Console.WriteLine(casenum + "," + caseInfo.Origin); } } } } this.btnLoad.Enabled = true; }
/// <summary> /// 更新Excel表格 /// </summary> /// <param name="outputFile">需更新的excel表格路径</param> /// <param name="sheetname">sheet名</param> /// <param name="updateData">需更新的数据</param> /// <param name="coluid">需更新的列号</param> /// <param name="rowid">需更新的开始行号</param> public static void UpdateExcel(string outputFile, string sheetname, double[] updateData, int coluid, int rowid) { FileStream readfile = new FileStream(outputFile, FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(readfile); ISheet sheet1 = hssfworkbook.GetSheet(sheetname); for (int i = 0; i < updateData.Length; i++) { try { if (sheet1.GetRow(i + rowid) == null) { sheet1.CreateRow(i + rowid); } if (sheet1.GetRow(i + rowid).GetCell(coluid) == null) { sheet1.GetRow(i + rowid).CreateCell(coluid); } sheet1.GetRow(i + rowid).GetCell(coluid).SetCellValue(updateData[i]); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); throw; } } try { readfile.Close(); FileStream writefile = new FileStream(outputFile, FileMode.Create, FileAccess.Write); hssfworkbook.Write(writefile); writefile.Close(); } catch (Exception ex) { //wl.WriteLogs(ex.ToString()); } }
public MemoryStream ExportDataTableToExcel(DataTable sourceTable, string sheetName) { const int maxSheetCount = 65536; HSSFWorkbook workbook = new HSSFWorkbook(); MemoryStream ms = new MemoryStream(); int Count = sourceTable.Rows.Count / maxSheetCount; for (int i = 0; i <= Count; i++) { ISheet sheet = workbook.CreateSheet(sheetName + i.ToString()); } for (int i = 0; i <= Count; i++) { ISheet sheet = workbook.GetSheet(sheetName + i.ToString()); IRow headerRow = sheet.CreateRow(0); // handling header. foreach (DataColumn column in sourceTable.Columns) { headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName); } // handling value. int rowIndex = 1; for (int j = maxSheetCount * i; j < maxSheetCount * (i + 1); j++) { if (j >= sourceTable.Rows.Count || rowIndex >= maxSheetCount) { break; } DataRow row = sourceTable.Rows[j]; IRow dataRow = sheet.CreateRow(rowIndex); foreach (DataColumn column in sourceTable.Columns) { dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString()); } rowIndex++; } } workbook.Write(ms); return(ms); }
private void Form2_Load(object sender, EventArgs e) { openFileDialog1.InitialDirectory = documentPath; if (openFileDialog1.ShowDialog() == System.Windows.Forms.DialogResult.OK) { excelPath = openFileDialog1.FileName; IWorkbook workbook; try { using (FileStream file = new FileStream(excelPath, FileMode.Open, FileAccess.Read)) { workbook = new HSSFWorkbook(file); } ISheet sheet1 = workbook.GetSheet("КТ"); ttt = new Dictionary <int, string>(); int i = 9; while (1 == 1) { if (sheet1.GetRow(i) == null) { i++; } if (string.IsNullOrEmpty(sheet1.GetRow(i).GetCell(0).StringCellValue) && (string.IsNullOrEmpty(sheet1.GetRow(i + 1).GetCell(0).StringCellValue))) { break; } ttt.Add(i, (sheet1.GetRow(i).GetCell(0).StringCellValue) + " " + (sheet1.GetRow(i).GetCell(12).NumericCellValue) + " семестр"); i++; } listBox1.DataSource = ttt.Values.ToList <string>(); listBox1.Refresh(); } catch (Exception) { MessageBox.Show("Выберите соответствующий тип файла!"); this.Close(); } } }
public static async Task ParseXLS(string workBookPath) { try { ISheet sheet; var fileExt = Path.GetExtension(workBookPath); var fileFullPath = Path.GetFullPath(workBookPath); if (fileExt != null && fileExt.ToLower() == ".xls") { HSSFWorkbook hssfwb; using (var file = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read)) hssfwb = new HSSFWorkbook(file); sheet = hssfwb.GetSheet(hssfwb.GetSheetName(0)); await MainCyrcle(sheet); using (var file = new FileStream(fileFullPath, FileMode.Create)) hssfwb.Write(file); } else if (fileExt != null && fileExt.ToLower() == ".xlsx") { XSSFWorkbook xssfwb; using (var file = new FileStream(fileFullPath, FileMode.Open, FileAccess.Read)) xssfwb = new XSSFWorkbook(file); sheet = xssfwb.GetSheet(xssfwb.GetSheetName(0)); await MainCyrcle(sheet); using (var file = new FileStream(fileFullPath, FileMode.Create)) xssfwb.Write(file); } Informer.RaiseOnResultReceived($"{workBookPath} successfully saved"); } catch (Exception ex) { Informer.RaiseOnResultReceived(ex); } }
public int DataTableToExcel(DataTable dt, string sheetName, bool isColumnWritten, bool Append = true) { try { HSSFWorkbook Workbook = NPOIOpenExcel(fileName);//打开工作薄 ISheet sheet = null; if (sheetName != null) { sheet = Workbook.GetSheet(sheetName); } if (sheet == null) { sheet = Workbook.GetSheetAt(0); } if (sheet == null) { return(0); } int nStartRow = sheet.LastRowNum + 1; for (int i = 0; i < dt.Rows.Count; i++) { DataRow dr = dt.Rows[i]; IRow row = sheet.CreateRow(nStartRow); for (int j = 0; j < dt.Columns.Count; j++) //写一行的数据 { row.CreateCell(j).SetCellValue(dr[j].ToString()); } } WriteToFile(Workbook, fileName); } catch (Exception e) { Messenger.Default.Send <string>(e.Message, "ShowError"); return(0); } return(1); }
protected void btnImport_Click(object sender, EventArgs e) { bool isSuccess = false; if (this.importFiles.PostedFile.ContentLength <= 0) { return; } Stream stream = this.importFiles.FileContent; HSSFWorkbook hssfworkbook = new HSSFWorkbook(stream); ISheet sheet = hssfworkbook.GetSheet("Sheet1"); string strSql = ""; ArrayList list = new ArrayList(); DataTable dt = RenderFromExcel(sheet, 0); TBaseApparatusInfoVo tav = new TBaseApparatusInfoVo(); //string[] strArr; for (int i = 0; i < dt.Rows.Count; i++) { tav.ID = GetSerialNumber("Apparatus_Id"); //ID tav.IS_DEL = "0"; //是否删除 0=否,1=是 tav.APPARATUS_CODE = dt.Rows[i + 2][0].ToString().Trim(); //仪器编号 tav.NAME = dt.Rows[i + 2][1].ToString().Trim(); //仪器名称 tav.MODEL = dt.Rows[i + 2][3].ToString().Trim(); //型号规格 tav.MEASURING_RANGE = dt.Rows[i + 2][4].ToString().Trim(); //测量范围 tav.EXPANDED_UNCETAINTY = dt.Rows[i + 2][5].ToString().Trim(); //不确定度 tav.FITTINGS_PROVIDER = dt.Rows[i + 2][6].ToString().Trim(); //供应商 tav.SERIAL_NO = dt.Rows[i + 2][7].ToString().Trim(); //出厂编号 tav.BUY_TIME = dt.Rows[i + 2][8].ToString().Trim(); //购买时间 tav.REMARK2 = dt.Rows[i + 2][9].ToString().Trim(); //金额 tav.POSITION = dt.Rows[i + 2][10].ToString().Trim(); //放置地点(新地点) tav.REMARK3 = dt.Rows[i + 2][11].ToString().Trim(); //放置地点(旧地点) new TBaseApparatusInfoLogic().Create(tav); } labMsg.Text = "导入成功!!!!!!!!!!!!!!!!!!"; }
public static List <Data> GetData() { var answer = new List <Data>(); HSSFWorkbook book; using (FileStream file = new FileStream(@"DataFile.xls", FileMode.Open, FileAccess.Read)) { book = new HSSFWorkbook(file); } ISheet page = book.GetSheet("Page1"); for (int row = 1; row <= page.LastRowNum; row++) { var item = page.GetRow(row); if (item != null) //null is when the row only contains empty cells { try { String Name = item.Cells[0].ToString(); String Url = item.Cells[3].ToString(); DateTime StartData = StringToDate(item.Cells[1].ToString()); DateTime EndData = StringToDate(item.Cells[2].ToString()); answer.Add(new Data(Name, StartData, EndData, Url)); } catch (Exception e) { // im lazy to parse if some data is not correct or dont exist // first of all, i dont know what i shall do // put a message on it place like "Ещё не выбрано" or it ""? // mush easer to skip this line Console.Error.WriteLine("Cant parse xls row to Data"); Console.Error.WriteLine(e.Message); } } } return(answer); }
/// <summary> /// 从属性表生成excel文件 /// </summary> /// <param name="dta"></param> /// <param name="FilePath"></param> /// <returns></returns> public string Creating_Excel_From_DataTable(DataTable dta, string FilePath) { HSSFWorkbook workbook2003 = new HSSFWorkbook(); //新建xls工作簿 workbook2003.CreateSheet("Sheet1"); //新建1个Sheet工作表 HSSFSheet SheetOne = (HSSFSheet)workbook2003.GetSheet("Sheet1"); //获取名称为Sheet1的工作表 for (int i = 0; i < dta.Rows.Count; i++) { IRow row = SheetOne.CreateRow(i); row.CreateCell(0).SetCellValue(dta.Rows[i][0].ToString()); row.CreateCell(1).SetCellValue(dta.Rows[i][1].ToString()); row.CreateCell(2).SetCellValue(dta.Rows[i][2].ToString()); row.CreateCell(3).SetCellValue(dta.Rows[i][3].ToString()); row.CreateCell(4).SetCellValue(dta.Rows[i][4].ToString()); } string saveAsPath = FilePath.Replace(".rfa", "") + ".xls"; FileStream file2003 = new FileStream(saveAsPath, FileMode.Create); workbook2003.Write(file2003); file2003.Close(); workbook2003.Close(); //eeeeee = saveAsPath; //string xlsFilePath = @"C:\ProgramData\Autodesk\Revit\Addins\2016\xlsFilePath.txt"; FileStream fs = new FileStream(xlsFilePathLog, FileMode.Append, FileAccess.Write); StreamWriter sw = new StreamWriter(fs); if (saveAsPath != null) { sw.Write(saveAsPath + "\n"); } sw.Close(); fs.Close(); return(saveAsPath); }
public ResultadoOperacion CleanHyperLinks(int col) { var ro = GetExcel(); if (!ro.Ok) { return(ro); } using (var fs = File.OpenRead(Excel)) { hssfWorkbook = new HSSFWorkbook(fs); } var sheet = hssfWorkbook.GetSheet(Hoja); var row = 1; while (true) { var xlRow = sheet.GetRow(row); if (xlRow == null) { break; } var cell = xlRow.GetCell(col); row++; if (cell == null) { continue; } cell.Hyperlink = null; } using (var fs = File.OpenWrite(Excel)) { hssfWorkbook.Write(fs); } return(new ResultadoOperacion() { Ok = true }); }
/// <summary> /// Method for getting the data by Row /// </summary> /// <params>SheetName,RowNumber,String FilePath=null </params> /// <return>String array</returns> public string[] GetRowDataFromExcel(string sheetName, int rowNum, string filePath = null) { try { if (filePath == null) { filePath = getPath(); } log.Info(filePath); List <string> al = new List <string>(); string data; FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.ReadWrite); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); file.Close(); ISheet sheet = hssfworkbook.GetSheet(sheetName); IRow r = sheet.GetRow(rowNum); for (int i = 0; i < r.LastCellNum; i++) { data = r.GetCell(i).ToString(); al.Add(data); } return(al.ToArray()); } catch (FileNotFoundException) { throw new FileNotFoundException(); } catch (NullReferenceException) { throw new NullReferenceException(); } catch (Exception e) { throw new Exception(e.Message); } }
/// <summary> /// Excel文件导成Datatable /// </summary> /// <param name="ms">Excel文件流</param> /// <param name="sheetname">Excel表名</param> /// <returns></returns> public static DataTable EecelToDataTable(Stream ms, string sheetname) { DataTable dt = new DataTable(); HSSFWorkbook workbook = new HSSFWorkbook(ms); var sheet = workbook.GetSheet(sheetname); if (null == sheet) { return(null); } //添加列 var row = sheet.GetRow(0); var val = ""; for (int i = 0; i < row.LastCellNum; i++) { val = row.GetCell(i).StringCellValue; if (string.IsNullOrEmpty(val) || dt.Columns.Contains(val)) { dt.Columns.Add(string.Format("第{0}列{1}", i + 1, val)); } else { dt.Columns.Add(val); } } //数据 for (int ri = 1; ri < sheet.LastRowNum; ri++) { var dr = dt.NewRow(); row = sheet.GetRow(ri); for (int i = 0; i < dt.Columns.Count && i < row.LastCellNum; i++) { dr[i] = row.GetCell(i).StringCellValue; } dt.Rows.Add(dr); } return(dt); }
public void GetGroups() { using (FileStream fs = new FileStream(fileName, FileMode.Open, FileAccess.Read)) { HSSFWorkbook book = new HSSFWorkbook(fs); int count = book.NumberOfSheets; Year year = new Year(); Context.Years.Add(year); if (year.Groups == null) { year.Groups = new List <Models.Group>(); } for (int a = 0; a < count; a++) { string nameOfGroup = book.GetSheetName(a); Regex regex = new Regex("^\\d"); if (regex.IsMatch(nameOfGroup)) { Models.Group group = new Models.Group() { NameGroup = nameOfGroup }; year.Groups.Add(group); } } GetStudents(year, book); ISheet sheet = book.GetSheet(year.Groups[0].NameGroup); string yearStr = sheet.GetRow(1).GetCell(1).StringCellValue; Regex regexYear = new Regex("\\d{4}\\/\\d{4}"); year.Name = regexYear.Match(yearStr).Value; FilterSubjects(); book.Close(); } Context.SaveChanges(); }
/// <summary> /// 根据表名获取表 /// </summary> /// <param name="filePath"></param> /// <param name="sheetName"></param> /// <returns></returns> public static DataTable ExcelToDataTable(string filePath, string sheetName) { string outMsg = ""; var dt = new DataTable(); string fileType = Path.GetExtension(filePath).ToLower(); try { ISheet sheet = null; FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read); if (fileType == ".xlsx") { //2007版 XSSFWorkbook workbook = new XSSFWorkbook(fs); sheet = workbook.GetSheet(sheetName); if (sheet != null) { dt = GetSheetDataTable(sheet, out outMsg); } } else if (fileType == ".xls") { //2003版 HSSFWorkbook workbook = new HSSFWorkbook(fs); sheet = workbook.GetSheet(sheetName); if (sheet != null) { dt = GetSheetDataTable(sheet, out outMsg); } } } catch (Exception e) { Console.WriteLine(e.Message); } return(dt); }
public void xpto() { // Open Template FileStream fs = new FileStream("c:\\lixo\\Extrato.xlt", FileMode.Open, FileAccess.Read); // Load the template into a NPOI workbook HSSFWorkbook templateWorkbook = new HSSFWorkbook(fs, true); // Load the sheet you are going to use as a template into NPOI NPOI.SS.UserModel.ISheet asheet = templateWorkbook.GetSheet("SaldoProjetos"); // Insert data into template asheet.GetRow(10).GetCell(3).SetCellValue(100); // Inserting a string value into Excel // Save the NPOI workbook into a memory stream to be sent to the browser, could have saved to disk. MemoryStream ms = new MemoryStream(); templateWorkbook.Write(ms); // Send the memory stream to the browser // ExportDataTableToExcel(ms, "c:\\lixo\\EventExpenseReport.xls"); }
private static void PreencherInformacoesDespesas(ICollection <Despesa> despesas) { ISheet sheetCatalogo = _documento.GetSheet("Despesas"); int numeroProximaLinha = 3; foreach (Despesa despesa in despesas) { sheetCatalogo.GetCell(numeroProximaLinha, 1).SetCellValue(despesa.Data.ToShortDateString()); sheetCatalogo.GetCell(numeroProximaLinha, 2).SetCellValue(despesa.PegarTipo()); sheetCatalogo.GetCell(numeroProximaLinha, 3).SetCellValue(despesa.Fornecedor.Nome); sheetCatalogo.GetCell(numeroProximaLinha, 4).SetCellValue(despesa.Item.Descricao); sheetCatalogo.GetCell(numeroProximaLinha, 5).SetCellValue(despesa.Quantidade); sheetCatalogo.GetCell(numeroProximaLinha, 6).SetCellValue(despesa.ValorUnitario); sheetCatalogo.GetCell(numeroProximaLinha, 7).SetCellValue(despesa.ValorTotal); if (despesa.Animal != null) { sheetCatalogo.GetCell(numeroProximaLinha, 8).SetCellValue(despesa.Animal.Especie); } numeroProximaLinha++; } }
protected void btnImport_Click(object sender, EventArgs e) { string strSampleId = this.strSampleId.Value; //获取基本信息 DataTable dt = new TMisMonitorSampleInfoLogic().getSamplingAllocationSheetInfoBySampleId(strSampleId, "03", "0"); //修改打印状态 new TMisMonitorSampleInfoLogic().updateSamplingAllocationSheetInfoStatus(strSampleId, "03", "1"); FileStream file = new FileStream(HttpContext.Current.Server.MapPath("template/SamplingAllocationSheet.xls"), FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); ISheet sheet = hssfworkbook.GetSheet("Sheet1"); sheet.GetRow(2).GetCell(0).SetCellValue("交接日期:" + DateTime.Now.ToString("yyyy-MM-dd")); for (int i = 0; i < dt.Rows.Count; i++) { sheet.GetRow(i + 4).GetCell(0).SetCellValue((dt.Rows[i]["SAMPLE_CODE"].ToString())); sheet.GetRow(i + 4).GetCell(2).SetCellValue(dt.Rows[i]["SAMPLE_NAME"].ToString()); sheet.GetRow(i + 4).GetCell(3).SetCellValue(dt.Rows[i]["SAMPLE_COUNT"].ToString()); sheet.GetRow(i + 4).GetCell(5).SetCellValue(dt.Rows[i]["ITEM_NAME"].ToString()); sheet.GetRow(i + 4).GetCell(6).SetCellValue(dt.Rows[i]["IS_OK"].ToString()); sheet.GetRow(i + 4).GetCell(7).SetCellValue(dt.Rows[i]["SAMPLING_MANAGER_NAME"].ToString()); sheet.GetRow(i + 4).GetCell(8).SetCellValue(dt.Rows[i]["SAMPLE_ACCESS_NAME"].ToString()); sheet.GetRow(i + 4).GetCell(9).SetCellValue(dt.Rows[i]["SAMPLE_ACCESS_DATE"].ToString() == "" ? "" : DateTime.Parse(dt.Rows[i]["SAMPLE_ACCESS_DATE"].ToString()).ToString("yyyy-MM-dd")); } using (MemoryStream stream = new MemoryStream()) { hssfworkbook.Write(stream); HttpContext curContext = HttpContext.Current; // 设置编码和附件格式 curContext.Response.ContentType = "application/vnd.ms-excel"; curContext.Response.ContentEncoding = Encoding.UTF8; curContext.Response.Charset = ""; curContext.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("样品交接记录表.xls", Encoding.UTF8)); curContext.Response.BinaryWrite(stream.GetBuffer()); curContext.Response.End(); } }
public static HSSFDataValidation CreateListConstraint(this HSSFWorkbook book, int columnIndex, IEnumerable <string> values) { var sheetName = "_constraintSheet_"; ISheet sheet = book.GetSheet(sheetName) ?? book.CreateSheet(sheetName); var firstRow = sheet.GetRow(0); var conColumnIndex = firstRow == null ? 0 : firstRow.PhysicalNumberOfCells; var rowIndex = 0; var lastValue = ""; foreach (var value in values) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(value); rowIndex++; lastValue = value; } //如果只有一个可选值,则增加一个相同的选项 if (values.Count() == 1) { var row = sheet.GetRow(rowIndex) ?? sheet.CreateRow(rowIndex); row.CreateCell(conColumnIndex).SetCellValue(lastValue); rowIndex++; } IName range = book.CreateName(); range.RefersToFormula = String.Format("{2}!${0}$1:${0}${1}", (Char)('A' + conColumnIndex), rowIndex.ToString(), sheetName); string rangeName = "dicRange" + columnIndex; range.NameName = rangeName; var cellRegions = new CellRangeAddressList(1, 65535, columnIndex, columnIndex); var constraint = DVConstraint.CreateFormulaListConstraint(rangeName); book.SetSheetHidden(book.GetSheetIndex(sheet), true); return(new HSSFDataValidation(cellRegions, constraint)); }
public void Reader() { DataTable table = new DataTable("book"); //创建表数据容器 table.Columns.Add(new DataColumn("name1", typeof(string))); //初始化列 table.Columns.Add(new DataColumn("name2", typeof(string))); table.Columns.Add(new DataColumn("id", typeof(string))); DataRow dr = null; //声明DataRow行 FileStream stream = new FileStream(filename, FileMode.Open); HSSFWorkbook wb = new HSSFWorkbook(stream); // 读取Excel数据 ISheet sheet = wb.GetSheet("sheet1"); //指定Sheet for (int i = 1; i < sheet.LastRowNum; i++) //遍历Excel的行数,由于该表第一行为标题(0行)需要从1开始 { IRow row = sheet.GetRow(i); //获取Execl行数据 dr = table.NewRow(); //创建DataRow for (int j = 0; j < row.Cells.Count; j++) //遍历Excel的列数 { string value = row.GetCell(j).ToString(); //获取指定行指定列的数据 dr[j] = value; //给DataRow赋值 } table.Rows.Add(dr); // 将DataRow数据添加到DataTable数据表中 } /** * 遍历DataTalbe */ for (int i = 0; i < table.Rows.Count; i++) { for (int j = 0; j < table.Columns.Count; j++) { string strName1 = table.Rows[i][j].ToString(); Console.WriteLine(strName1); } } Console.ReadKey(); }
/// <summary> /// WEBAPI 下载Excel表格文件 /// </summary> /// <param name="request"></param> /// <param name="dt"></param> /// <param name="filename"></param> /// <returns></returns> //public static HttpResponseMessage DownLoadExcel(this HttpRequestMessage request, DataTable dt, String filename) //{ // var response = request.CreateResponse(HttpStatusCode.OK); // HSSFWorkbook book = new HSSFWorkbook(); // ISheet sheet = book.CreateSheet(dt.TableName); // IRow row = sheet.CreateRow(0); // for (int i = 0; i < dt.Columns.Count; i++) // { // row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName); // } // for (int i = 0; i < dt.Rows.Count; i++) // { // IRow row2 = sheet.CreateRow(i + 1); // for (int j = 0; j < dt.Columns.Count; j++) // { // row2.CreateCell(j).SetCellValue(Convert.ToString(dt.Rows[i][j])); // } // } // // 写入到客户端 // System.IO.MemoryStream stream = new System.IO.MemoryStream(8192); // book.Write(stream); // stream.Seek(0, SeekOrigin.Begin); // response.Content = new StreamContent(stream); // response.Content.Headers.ContentType = new MediaTypeHeaderValue("application/octet-stream"); // response.Content.Headers.ContentDisposition = new ContentDispositionHeaderValue("attachment") // { // FileName = filename // }; // book = null; // return response; //} /// <summary> /// 从 Xls文件导入数据到 DataTable /// </summary> /// <param name="filePath"></param> /// <param name="SheetName"></param> /// <returns></returns> public static DataTable Read(string filePath, String SheetName) { HSSFWorkbook hssfworkbook; using (FileStream file = new FileStream(filePath, FileMode.Open, FileAccess.Read)) { hssfworkbook = new HSSFWorkbook(file); } ISheet sheet = hssfworkbook.GetSheet(SheetName); if (sheet == null) { return(new DataTable()); } System.Collections.IEnumerator rows = sheet.GetRowEnumerator(); DataTable dt = new DataTable(SheetName); for (int j = 0; j < (sheet.GetRow(0).LastCellNum); j++) { ICell cell = sheet.GetRow(0).Cells[j]; dt.Columns.Add(cell.ToString()); } rows.MoveNext(); while (rows.MoveNext()) { HSSFRow row = (HSSFRow)rows.Current; DataRow Row = dt.NewRow(); for (int i = 0; i < row.LastCellNum; i++) { ICell cell = row.GetCell(i); Row[i] = cell == null ? null : cell.ToString(); } dt.Rows.Add(Row); } hssfworkbook.Close(); hssfworkbook = null; return(dt); }
//机票会计总账(DepCode=订单号开头2个数字21,22,23,25) private void TicketAccount(int Year, int Month, int DepCode) { //read the template via FileStream, it is suggested to use FileAccess.Read to prevent file lock. FileStream file = new FileStream(AppDomain.CurrentDomain.BaseDirectory + "Invoice\\Ticket_Account_Template.xls", FileMode.Open, FileAccess.Read); HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); HSSFSheet sheet1 = hssfworkbook.GetSheet("Sheet1"); DataTable dt; int[] arrDep = { 21, 22, 23, 25 }; int startRow = 0; if (DepCode == 0) { foreach (int iDep in arrDep) { dt = Ticket.GetTicketAccountingReport(Year, Month, iDep); FillExcelAccount(sheet1, dt, startRow); startRow += dt.Rows.Count + 1; } } else { dt = Ticket.GetTicketAccountingReport(Year, Month, DepCode); FillExcelAccount(sheet1, dt, startRow); } //Excel文件在被打开的时候自动将焦点定位在单元格 sheet1.GetRow(0).GetCell(0).SetAsActiveCell(); //Force excel to recalculate all the formula while open sheet1.ForceFormulaRecalculation = true; string FullFileName = AppDomain.CurrentDomain.BaseDirectory + "Upload\\Excel\\Ticket_Account_" + Year.ToString() + Month.ToString("00") + ".xls"; file = new FileStream(FullFileName, FileMode.Create); hssfworkbook.Write(file); file.Close(); DownloadFileAsAttachment(FullFileName); }
public void TestBug43093() { HSSFWorkbook xlw = new HSSFWorkbook(); AddNewSheetWithCellsA1toD4(xlw, 1); AddNewSheetWithCellsA1toD4(xlw, 2); AddNewSheetWithCellsA1toD4(xlw, 3); AddNewSheetWithCellsA1toD4(xlw, 4); NPOI.SS.UserModel.Sheet s2 = xlw.GetSheet("s2"); Row s2r3 = s2.GetRow(3); Cell s2E4 = s2r3.CreateCell(4); s2E4.CellFormula = ("SUM(s3!B2:C3)"); HSSFFormulaEvaluator eva = new HSSFFormulaEvaluator(xlw); double d = eva.Evaluate(s2E4).NumberValue; // internalEvaluate(...) Area3DEval.: 311+312+321+322 expected Assert.AreEqual(d, (311 + 312 + 321 + 322), 0.0000001); // System.out.println("Area3DEval ok.: 311+312+321+322=" + d); }
/// <summary> /// 由DataTable导出Excel(适应于基本的模版导出,且不超过65535条) /// </summary> /// <param name="sourceTable">要导出数据的DataTable</param> /// <param name="modelpath">模版文件实体路径</param> /// <param name="modelName">模版文件名称</param> /// <param name="fileName">指定Excel工作表名称</param> /// <param name="sheetName">作为模型的Excel</param> /// <param name="rowindex">从第几行开始写入数据(此为行索引,若为1则从第2行开始写入数据)</param> /// <returns>Excel工作表</returns> public static void ExportDataTableToExcelModel(DataTable sourceTable, string modelpath, string modelName, string fileName, string sheetName, int rowIndex) { int colIndex = 0; FileStream file = new FileStream(modelpath + "/" + modelName, FileMode.Open, FileAccess.Read);//读入excel模板 HSSFWorkbook hssfworkbook = new HSSFWorkbook(file); HSSFSheet sheet1 = (HSSFSheet)hssfworkbook.GetSheet(sheetName); if (sourceTable.Rows.Count + rowIndex > 65535) { throw new ArgumentException("数据太多,系统尚不支持,请缩小查询范围!"); } foreach (DataRow row in sourceTable.Rows) { //双循环写入sourceTable中的数据 colIndex = 0; HSSFRow xlsrow = (HSSFRow)sheet1.CreateRow(rowIndex); foreach (DataColumn col in sourceTable.Columns) { xlsrow.CreateCell(colIndex).SetCellValue(row[col.ColumnName].ToString()); colIndex++; } rowIndex++; } sheet1.ForceFormulaRecalculation = true; //CS项目适用胡方法 //FileStream fileS = new FileStream(modelpath + fileName + ".xls", FileMode.Create);//保存 //hssfworkbook.Write(fileS); //fileS.Close(); MemoryStream ms = new MemoryStream(); hssfworkbook.Write(ms); HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode(fileName, Encoding.UTF8) + ".xls"); HttpContext.Current.Response.BinaryWrite(ms.ToArray()); HttpContext.Current.Response.End(); ms.Close(); ms = null; }
public void TestCaseInsensitiveNames() { HSSFWorkbook b = new HSSFWorkbook(); NPOI.SS.UserModel.ISheet originalSheet = b.CreateSheet("Sheet1"); NPOI.SS.UserModel.ISheet fetchedSheet = b.GetSheet("sheet1"); if (fetchedSheet == null) { throw new AssertionException("Identified bug 44892"); } Assert.AreEqual(originalSheet, fetchedSheet); try { b.CreateSheet("sHeeT1"); Assert.Fail("should have thrown exceptiuon due to duplicate sheet name"); } catch (ArgumentException e) { // expected during successful Test Assert.AreEqual("The workbook already contains a sheet of this name", e.Message); } }
public static string CreateAmazonFeedInventoryFile(List <MarketplaceInventoryUpdateItem> inventoryFeeds) { //var template = @"D:\dev\kigwa\EShopo System\EIS.Inventory\EIS.Consoles\EIS.SchedulerTaskApp\Templates\Flat.File.PriceInventory.xls"; var template = string.Format("{0}\\Flat.File.PriceInventory.xls", ConfigurationManager.AppSettings["TemplatesRoot"]); var newFile = string.Format("{0}\\AmazonInventoryFeed-{1:yyyyMMdd_HHmmss}.xls", ConfigurationManager.AppSettings["MarketplaceFeedRoot"], DateTime.Now); HSSFWorkbook workbook; using (var fs = new FileStream(template, FileMode.Open, FileAccess.Read)) { // Getting the complete workbook... workbook = new HSSFWorkbook(fs); fs.Close(); } // Getting the worksheet by its name... var sheet = workbook.GetSheet("Price Template"); for (var index = 1; index <= inventoryFeeds.Count; index++) { var row = sheet.CreateRow(index); row.CreateCell(0).SetCellValue(inventoryFeeds[index - 1].SKU); //row.CreateCell(4).SetCellValue(inventoryFeeds[index - 1].InventoryQuantity); row.CreateCell(4).SetCellValue(101); } // Writing the workbook content to the FileStream... using (var fileStream = new FileStream(newFile, FileMode.Create)) { workbook.Write(fileStream); fileStream.Flush(); fileStream.Close(); } return(newFile); }
private void отчетToolStripMenuItem_Click(object sender, EventArgs e) { if (activeTable == 4) { SaveFileDialog dialog = new SaveFileDialog(); dialog.InitialDirectory = Environment.GetFolderPath(Environment.SpecialFolder.Desktop); dialog.DefaultExt = ".xls"; dialog.Filter = "Таблицы Excel (*.xls)|*.xls|Все файлы (*.*)|*.*"; dialog.FilterIndex = 1; dialog.FileName = "Отчет"; if (dialog.ShowDialog() == DialogResult.OK) { var file = new FileStream(dialog.FileName, FileMode.Create, FileAccess.ReadWrite); var query = (from p in db.payment join u in db.users on p.id_s equals u.id_u orderby p.id_p select new { p.id_p, u.surname, u.name, u.patron, p.summa, p.pay_day }).ToList(); var template = new MemoryStream(Properties.Resources.template, true); HSSFWorkbook workbook = new HSSFWorkbook(template); var sheet1 = workbook.GetSheet("Лист1"); sheet1.CreateRow(3).CreateCell(0).SetCellValue(DateTime.Now.ToShortDateString()); int row = 6; foreach (var item in query.OrderBy(o => o.id_p)) { var rowInsert = sheet1.CreateRow(row); rowInsert.CreateCell(0).SetCellValue(item.id_p); rowInsert.CreateCell(1).SetCellValue(item.surname); rowInsert.CreateCell(2).SetCellValue(item.name); rowInsert.CreateCell(3).SetCellValue(item.patron); rowInsert.CreateCell(4).SetCellValue(item.summa); rowInsert.CreateCell(5).SetCellValue(item.pay_day); row++; } workbook.Write(file); } } }