log xls stream bre tor last 一个 odata
- public static voidCreateExcelFile(stringFileName, List luu)
- {
- if(FileName.Split('.')[FileName.Split('.').Length -1] =="xlsx")//如果是2007版以后
- {
- //create
- objectNothing = System.Reflection.Missing.Value;
- varapp =new Excel.Application();
- app.Visible =false;
- Excel.Workbook workBook = app.Workbooks.Add(Nothing);
- Excel.Worksheet worksheet = (Excel.Worksheet)workBook.Sheets[1];
- worksheet.Name ="Sheet1";
- //headline
- inti =1;
- foreach(UUser uuin luu)
- {
- worksheet.Cells[1, i] = uu.name;
- i++;
- }
- worksheet.SaveAs(FileName, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
- workBook.Close(false, Type.Missing, Type.Missing);
- app.Quit();
- }
- else
- {
- HSSFWorkbook wk =newHSSFWorkbook();//创建一个名称为mySheet的表ISheet tb = wk.CreateSheet("Sheet1");
- //创建一行,此行为第二行IRow row = tb.CreateRow(1);
- for(inti =0; i < luu.Count; i++)
- {
- ICell cell = row.CreateCell(i);//在第二行中创建单元格cell.SetCellValue(luu[i].name);//循环往第二行的单元格中添加数据 }
- using(FileStream fs = File.OpenWrite(FileName))
- {
- wk.Write(fs); //向打开的这个xls文件中写入mySheet表并保存。
- }
- }
- }
- }
using Excel = Microsoft.Office.Interop.Excel;
using NExcel;using NPOI.HSSF.UserModel;using NPOI.SS.UserModel;using NPOI.XSSF.UserModel;
需要这些引用 dll 网上都可以下载
- /// 将excel中的数据导入到DataTable中
- /// </summary>
- /// <param name="sheetName">excel工作薄sheet的名称</param>
- /// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
- /// <returns>返回的DataTable</returns>
- public staticDataTable ExcelToDataTable(stringname,stringsheetName,bool isFirstRowColumn)//name 是excel的地址
- {
- List<string> ls =newList<string>();
- stringsss =null;
- IWorkbook workbook =null;
- stringfileName = name;
- ISheet sheet =null;
- DataTable data =new DataTable();
- intstartRow =0;
- try
- {
- FileStream fs =new FileStream(fileName, FileMode.Open, FileAccess.Read);
- if(fileName.IndexOf(".xlsx") >0)// 2007版本workbook =new XSSFWorkbook(fs);
- else if(fileName.IndexOf(".xls") >0)// 2003版本workbook =new HSSFWorkbook(fs);
- if(sheetName !=null)
- {
- //sheet = workbook.GetSheetAt(0);sheet = workbook.GetSheet(sheetName);
- }
- else
- {
- sheet = workbook.GetSheetAt(0);
- }
- if(sheet !=null)
- {
- IRow firstRow = sheet.GetRow(0);
- intcellCount = firstRow.Cells.Count;//一行最后一个cell的编号 即总的列数
- if (isFirstRowColumn)
- {
- for(inti = firstRow.FirstCellNum; i < cellCount; ++i)
- {
- DataColumn column =newDataColumn(firstRow.GetCell(i).StringCellValue.Replace("\n","").Replace(" ","").Replace("\t","").Replace("\r",""));
- data.Columns.Add(column);
- }
- startRow = sheet.FirstRowNum +1;
- }
- else
- {
- startRow = sheet.FirstRowNum;
- }
- //最后一列的标号
- introwCount = sheet.LastRowNum;
- for(inti = startRow; i <= rowCount; ++i)
- {
- IRow row = sheet.GetRow(i);
- if(row ==null)continue;//没有数据的行默认是null
- DataRow dataRow = data.NewRow();
- for(intj = row.FirstCellNum; j < cellCount; ++j)
- {
- if(row.GetCell(j) !=null)//同理,没有数据的单元格都默认是null
- {
- switch (row.GetCell(j).CellType)
- {
- caseNPOI.SS.UserModel.CellType.Blank://空数据类型处理dataRow[j] ="";
- break;
- caseNPOI.SS.UserModel.CellType.String://字符串类型dataRow[j] ="'"+ row.GetCell(j).StringCellValue;
- break;
- caseNPOI.SS.UserModel.CellType.Numeric://数字类型
- if (HSSFDateUtil.IsCellDateFormatted(row.GetCell(j)))
- {
- dataRow[j] ="'"+row.GetCell(j).DateCellValue.ToShortDateString().Split(' ')[0];
- }
- else
- {
- if(row.GetCell(j).NumericCellValue.ToString().IndexOf(".") <0)
- dataRow[j] = row.GetCell(j).NumericCellValue;
- else
- {
- dataRow[j] =Convert.ToDouble( getstr( row.GetCell(j).NumericCellValue.ToString()));
- }
- }
- break;
- case NPOI.SS.UserModel.CellType.Formula:
- if(fileName.IndexOf(".xlsx") <0)
- {
- HSSFFormulaEvaluator e =new HSSFFormulaEvaluator(workbook);
- dataRow[j] = e.Evaluate(row.GetCell(j)).StringValue;
- }
- else
- {
- XSSFFormulaEvaluator e =new XSSFFormulaEvaluator(workbook);
- dataRow[j] = e.Evaluate(row.GetCell(j)).StringValue;
- }
- break;
- default:
- row.GetCell(j).SetCellType(NPOI.SS.UserModel.CellType.String);
- dataRow[j] = row.GetCell(j).StringCellValue;
- break;
- }
- }
- }
- data.Rows.Add(dataRow);
- }
- }
- fs.Close();
- return data;
- }
- catch (Exception ex)
- {
- // MessageBox.Show("err:"+ex.Message);
- return new DataTable();
- }
- }
- public static voidDataTableToExcel(DataTable dt, Excel.Worksheet excelSheet,ProgressBar s,List luu)
- {
- s.Maximum = dt.Rows.Count * luu.Count;
- s.Value =0;
- s.Visible =true;
- introwCount = dt.Rows.Count ;
- intcolCount =luu.Count;
- object[,] dataArray =new object[rowCount+1, colCount];
- for(intk =0; k < luu.Count; k++)
- {
- stringstr= luu[k].name;
- dataArray[0, k] = str;
- }
- for(inti =0; i < rowCount; i++)
- {
- for(intj =0; j < luu.Count; j++)
- {
- dataArray[i +1, j] =dt.Rows[i][luu[j].source];
- Application.DoEvents();
- s.Value = s.Value +1;
- }
- }
- excelSheet.Range["A1", excelSheet.Cells[rowCount+1, colCount]].Value2 = dataArray;
- s.Visible =false;
- s.Value =0;
- }
这个 uuser 有两个字段 Name 和 Source
name 是写入到新 excel 的列名
source 是 datatable 里的列名
这个是一个 excel 转换的流程
如果只是读取和写入直接去掉 list<uuser> 即可
C# 创建 写入 读取 excel
来源: http://www.bubuko.com/infodetail-2021525.html