public static DataTable readExcel(string filePath, string sheetName, int headIndex = 0)
{
DataTable table = new DataTable();
try
{
using (FileStream fs = new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
XSSFWorkbook workbook = new XSSFWorkbook(fs);
XSSFSheet sheet = (XSSFSheet)workbook.GetSheet(sheetName);
XSSFRow headRow = (XSSFRow)sheet.GetRow(headIndex);
int columnCount = headRow.Cells.Count;
//获取标题名
for (int index = 0; index < headRow.Cells.Count; index++)
{
DataColumn dc = new DataColumn(headRow.GetCell(index).ToString());
table.Columns.Add(dc);
}
for (int i = sheet.FirstRowNum + 1 + headIndex; i <= sheet.LastRowNum; i++)
{
DataRow newRow = table.NewRow();
XSSFRow row = (XSSFRow)sheet.GetRow(i);
int isEmpty = 0;
if (row != null)
{
//复制整行数据
for (int j = 0; j < columnCount - 1; j++)
{
//if (row.GetCell(j).ToString()==string.Empty)
//{
// isEmpty++;
//}
newRow[j] = row.GetCell(j);
if (newRow[j].ToString() == string.Empty || newRow[j].ToString() == "NULL")
{
newRow[j] = null;
isEmpty++;
}
}
if (isEmpty == columnCount)
{
continue;
}
//添加到数据表中
table.Rows.Add(newRow);
}
}
}
}
catch (Exception exMsg)
{
throw (exMsg);
//throw (exMsg);
}
return(table);
}
//处理excel2007
private static DataTable ExcelToDataTableFirstRowAsHeader(XSSFSheet sheet, XSSFFormulaEvaluator evaluator)
{
using (DataTable dt = new DataTable())
{
XSSFRow firstRow = sheet.GetRow(0) as XSSFRow;
int cellCount = GetCellCount(sheet);
for (int i = 0; i < cellCount; i++)
{
if (firstRow.GetCell(i) != null)
{
dt.Columns.Add(firstRow.GetCell(i).StringCellValue ?? string.Format("F{0}", i + 1), typeof(string));
}
else
{
dt.Columns.Add(string.Format("F{0}", i + 1), typeof(string));
}
}
for (int i = 1; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dr = dt.NewRow();
FillDataRowByHSSFRow(row, evaluator, ref dr);
dt.Rows.Add(dr);
}
dt.TableName = sheet.SheetName;
return(dt);
}
}
private static DataTable getDaTableBy07Excel(Stream excelFileStream, int sheetIndex = 0, int headerRowIndex = 0)
{
DataTable table = new DataTable();
XSSFWorkbook workbook = new XSSFWorkbook(excelFileStream);
XSSFSheet sheet = workbook.GetSheetAt(sheetIndex) as XSSFSheet;
XSSFRow headerRow = sheet.GetRow(headerRowIndex) as XSSFRow; //表头行的索引
int cellCount = headerRow.LastCellNum;
for (int i = headerRow.FirstCellNum; i < cellCount; i++)
{
DataColumn column = new DataColumn(headerRow.GetCell(i).StringCellValue);
table.Columns.Add(column);
}
for (int i = 0; i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
if (row == null || row.Cells == null || row.Cells.TrueForAll(p => string.IsNullOrWhiteSpace(p.ToString())))
{
break;
}
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
dataRow[j] = row.GetCell(j) != null?row.GetCell(j) + "" : "";
}
table.Rows.Add(dataRow);
}
excelFileStream.Close();
workbook = null;
sheet = null;
return(table);
}
/**
* Get all territory numbers having interested addresses from the territoty helper dumped file.
*
*/
public List <int> GetAllTerritoryNumbersHavingLocations()
{
List <int> resultList = new List <int>();
XSSFWorkbook wb = null;
string @excelFileLocation = appSettings["destination-file-path-addresses"];
using (FileStream file = new FileStream(@excelFileLocation, FileMode.Open, FileAccess.Read))
{
wb = new XSSFWorkbook(file);
}
XSSFSheet sh = (XSSFSheet)wb.GetSheetAt(0);
//Loop the records upto filled row
for (int row = 1; row < sh.LastRowNum; row++)
{
//null is when the row only contains empty cells
if (sh.GetRow(row) != null)
{
string value = sh.GetRow(row).GetCell(1).StringCellValue; //Here for sample , I just save the value in "value" field, Here you can write your custom logics...
int intValue = Int32.Parse(value);
if (!resultList.Contains(intValue))
{
resultList.Add(intValue);
}
}
}
return(resultList);
}
public ActionResult Download()
{
IWorkbook workbook = new XSSFWorkbook();
XSSFSheet u_sheet = (XSSFSheet)workbook.CreateSheet("客戶聯絡人");
// u_sheet.
List <客戶聯絡人> list = rep客戶聯絡人.All().ToList();
u_sheet.CreateRow(0).CreateCell(0).SetCellValue("職稱");
u_sheet.GetRow(0).CreateCell(1).SetCellValue("姓名");
u_sheet.GetRow(0).CreateCell(2).SetCellValue("Email");
u_sheet.GetRow(0).CreateCell(3).SetCellValue("手機");
u_sheet.GetRow(0).CreateCell(4).SetCellValue("電話");
u_sheet.GetRow(0).CreateCell(5).SetCellValue("客戶名稱");
for (int i = 0; i < list.Count; i++)
{
u_sheet.CreateRow(i + 1).CreateCell(0).SetCellValue(list[i].職稱);
u_sheet.GetRow(i + 1).CreateCell(1).SetCellValue(list[i].姓名);
u_sheet.GetRow(i + 1).CreateCell(2).SetCellValue(list[i].Email);
u_sheet.GetRow(i + 1).CreateCell(3).SetCellValue(list[i].手機);
u_sheet.GetRow(i + 1).CreateCell(4).SetCellValue(list[i].電話);
u_sheet.GetRow(i + 1).CreateCell(5).SetCellValue(list[i].客戶資料.客戶名稱);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(File(ms.ToArray(), "application/vnd.ms-excel", "客戶聯終人.xlsx"));
}
public void Load(string inputPath, string outputPath)
{
XSSFWorkbook workbook = new XSSFWorkbook(new FileStream(inputPath, FileMode.Open));
XSSFSheet sheet = workbook.GetSheet("pivot") as XSSFSheet;
var csv = new StringBuilder();
for (int row = 6; row < 1006; row++)
{
var line = "";
for (int cell = 1; cell < 19; cell++)
{
if (row == 3)
{
line += sheet.GetRow(row).GetCell(cell).StringCellValue + ',';
}
else
{
line += sheet.GetRow(row).GetCell(cell).NumericCellValue.ToString() + ',';
}
}
csv.AppendLine(line);
}
File.WriteAllText(outputPath, csv.ToString());
}
public ActionResult Download()
{
IWorkbook workbook = new XSSFWorkbook();
XSSFSheet u_sheet = (XSSFSheet)workbook.CreateSheet("銀行帳戶");
// u_sheet.
List <客戶銀行資訊> list = repo客戶銀行資訊.All().ToList();
u_sheet.CreateRow(0).CreateCell(0).SetCellValue("銀行名稱");
u_sheet.GetRow(0).CreateCell(1).SetCellValue("銀行代碼");
u_sheet.GetRow(0).CreateCell(2).SetCellValue("分行代碼");
u_sheet.GetRow(0).CreateCell(3).SetCellValue("帳戶名稱");
u_sheet.GetRow(0).CreateCell(4).SetCellValue("帳戶號碼");
u_sheet.GetRow(0).CreateCell(5).SetCellValue("客戶名稱");
// u_sheet.GetRow(0).CreateCell(6).SetCellValue("客戶分類");
for (int i = 0; i < list.Count; i++)
{
u_sheet.CreateRow(i + 1).CreateCell(0).SetCellValue(list[i].銀行名稱);
u_sheet.GetRow(i + 1).CreateCell(1).SetCellValue(list[i].銀行代碼);
u_sheet.GetRow(i + 1).CreateCell(2).SetCellValue(list[i].分行代碼.Value);
u_sheet.GetRow(i + 1).CreateCell(3).SetCellValue(list[i].帳戶名稱);
u_sheet.GetRow(i + 1).CreateCell(4).SetCellValue(list[i].帳戶號碼);
u_sheet.GetRow(i + 1).CreateCell(5).SetCellValue(list[i].客戶資料.客戶名稱);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(File(ms.ToArray(), "application/vnd.ms-excel", "銀行資料.xlsx"));
}
public ActionResult setYEStoTrue()
{
XSSFWorkbook book1 = new XSSFWorkbook(new FileStream(HostingEnvironment.MapPath("~/Content/aliaa.xlsx"), FileMode.Open));
XSSFSheet sheet1 = book1.GetSheet("sheet2") as XSSFSheet;
int x = 1;
for (int i = 1; i <= sheet1.LastRowNum; i++)
{
if (sheet1.GetRow(i).GetCell(2).ToString() == "Yes")
{
sheet1.GetRow(i).CreateCell(2).SetCellValue(1);
}
else
{
sheet1.GetRow(i).CreateCell(2).SetCellValue(0);
}
}
string filepath = HostingEnvironment.MapPath("~/Content/aliaa.xlsx");
FileStream sw = System.IO.File.Create(filepath);
book1.Write(sw);
sw.Close();
ViewBag.msg = "scucess";
ViewBag.path = filepath;
return(View());
}
public static void ShiftRows(ref XSSFSheet sheet, int startrow, int endrow, int n, bool copyRowHeight = true, bool resetOriginalRowHeight = true)
{
if (n == 0)
{
return;
}
int i;
if (n > 0)
{
for (i = endrow; i >= startrow; i--)
{
if (sheet.GetRow(i) != null)
{
CopyRow(ref sheet, i, i + n, true, true, copyRowHeight, resetOriginalRowHeight);
}
}
}
else
{
for (i = startrow; i <= endrow; i++)
{
if (sheet.GetRow(i) != null)
{
CopyRow(ref sheet, i, i + n, true, true, copyRowHeight, resetOriginalRowHeight);
}
}
}
}
private static void SetCodeColumn(XSSFSheet sheet, string columnLetter, int colindex, int elements, string sheetName, bool createCodeColumns)
{
if (createCodeColumns)
{
var row0 = sheet.GetRow(0);
var lastColIndex = row0.LastCellNum;
var headerCell = row0.CreateCell(lastColIndex, CellType.String);
headerCell.SetCellValue(sheetName + " Code");
row0.Cells.Add(headerCell);
headerCell.CellStyle = sheet.Workbook?.GetSheetAt(0)?.GetRow(0)?.GetCell(0)?.CellStyle;
for (var i = 1; i <= elements; i++)
{
var row = sheet.GetRow(i) ?? sheet.CreateRow(i);
var cell = row.CreateCell(lastColIndex, CellType.Formula);
var formula = $"INDEX('{sheetName}'!A1:D{elements},MATCH(${columnLetter}{i + 1},'{sheetName}'!D1:D{elements},0),1)";
cell.SetCellFormula(formula);
}
sheet.SetColumnHidden(row0.LastCellNum - 1, true);
}
var validationHelper = new XSSFDataValidationHelper(sheet);
var addressList = new CellRangeAddressList(0, elements - 1, colindex - 1, colindex - 1);
var constraint = validationHelper.CreateFormulaListConstraint($"'{sheetName}'!$D$2:$D$" + elements);
var dataValidation = validationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(dataValidation);
}
public static void SaveRToXls(Stream filegrp, string fileidx, string inifilepath, string xlsFile)
{
XSSFWorkbook newBook = new XSSFWorkbook();
List <object> rows = new List <object>();
List <RdataType> datalist = ReadRanger(filegrp, fileidx, inifilepath);
for (int k = 0; k < datalist.Count; k++)
{
object[,] array = datalist[k].ToArray();
XSSFSheet newSheet = (XSSFSheet)newBook.CreateSheet(datalist[k].TypeName);//新建工作簿
for (int i = 0; i < array.GetLength(1); i++)
{
XSSFRow newRow = (XSSFRow)newSheet.CreateRow(i);//创建行
for (int j = 0; j < array.GetLength(0); j++)
{
if (array[j, i].GetType() == typeof(String))
{
newSheet.GetRow(i).CreateCell(j).SetCellValue((string)array[j, i]);
}
else
{
newSheet.GetRow(i).CreateCell(j).SetCellValue((int)array[j, i]);
}
}
}
//newSheet.AutoSizeColumn(-2);
}
FileStream fs = new FileStream(xlsFile, FileMode.Create);
newBook.Write(fs);
fs.Close();
fs.Dispose();
}
/// <summary>
/// 导出datagridview数据到excel文件
/// </summary>
/// <param name="dataGridView">dataGridView控件</param>
/// <returns></returns>
public int export2Excel(DataGridView dataGridView)
{
XSSFWorkbook workbook = new XSSFWorkbook(); //工作簿
XSSFSheet sheet = new XSSFSheet(); //工作表
SaveFileDialog saveDialog; //保存文件的对话框
FileStream fs; //文件流
string filename; //保存时的文件名称
sheet = (XSSFSheet)workbook.CreateSheet(); //在工作簿中创建表
try
{
sheet.CreateRow(0); //创建第一行
for (int i = 0; i < dataGridView.Columns.Count; i++) //创建表头
{
sheet.GetRow(0).CreateCell(i).SetCellValue(dataGridView.Columns[i].HeaderText);
}
//创建单元格样式
ICellStyle cellstyle;
cellstyle = workbook.CreateCellStyle();
IDataFormat Format = workbook.CreateDataFormat();
cellstyle.DataFormat = Format.GetFormat("0.0000");
//添加其他行和列
for (int i = 0; i < dataGridView.Rows.Count; i++)
{
sheet.CreateRow(i + 1); //每遍历一行，则在sheet中创建一行
for (int j = 0; j < dataGridView.Columns.Count; j++)
{
if (j == 0)
{
sheet.GetRow(i + 1).CreateCell(j).SetCellValue(dataGridView.Rows[i].Cells[j].Value.ToString());
}
else
{
sheet.GetRow(i + 1).CreateCell(j).SetCellValue(double.Parse(dataGridView.Rows[i].Cells[j].Value.ToString()));
}
sheet.GetRow(i).GetCell(j).CellStyle = cellstyle;
}
}
saveDialog = new SaveFileDialog(); //保存文件对话框
saveDialog.DefaultExt = "xlsx"; //设置默认文件扩展名
saveDialog.Filter = "Excel 2007 文件|*.xlsx"; //文件类型
if (saveDialog.ShowDialog() == DialogResult.OK)
{
filename = saveDialog.FileName;
fs = new FileStream(filename, FileMode.Create);
workbook.Write(fs);
fs.Close();
MessageBox.Show("导出完成！", "导出结果", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
cellstyle = null;
Format = null;
workbook = null;
return(0);
}
catch
{
return(-1);
}
}
/// <summary>
/// Convert Excel sheets to DataTable list
/// </summary>
/// <param name="filename"></param>
/// <returns>list of datatable</returns>
public static List <DataTable> ExceltoDataTable(string filename)
{
XSSFWorkbook xssfwb;
List <DataTable> dts = new List <DataTable>();
using (FileStream file = new FileStream(filename, FileMode.Open, FileAccess.Read))
{
xssfwb = new XSSFWorkbook(file);
}
for (int i = 0; i < xssfwb.NumberOfSheets; i++)
{
XSSFSheet sheet = (XSSFSheet)xssfwb.GetSheetAt(i);
DataTable dt = new DataTable();
int num = 0;
while (sheet.GetRow(num) != null)
{
if (dt.Columns.Count < sheet.GetRow(num).Cells.Count)
{
for (int j = 0; j < sheet.GetRow(num).Cells.Count; j++)
{
dt.Columns.Add("", typeof(string));
}
}
XSSFRow row = (XSSFRow)sheet.GetRow(num);
DataRow dr = dt.Rows.Add();
for (int k = 0; k < row.Cells.Count; k++)
{
XSSFCell cell = (XSSFCell)row.GetCell(k);
if (cell != null)
{
switch (cell.CellType)
{
case CellType.Numeric:
dr[k] = cell.NumericCellValue;
break;
case CellType.String:
dr[k] = cell.StringCellValue;
break;
case CellType.Blank:
dr[k] = "";
break;
case CellType.Boolean:
dr[k] = cell.BooleanCellValue;
break;
}
}
}
num++;
}
dts.Add(dt);
}
return(dts);
}
/// <summary>
/// 生成对应的cs文件
/// </summary>
/// <param name="sheet"></param>
static void GenertorDao(XSSFSheet sheet)
{
CodeGenerator code = new CodeGenerator();
code.PrintLine("//***************************************************************");
code.PrintLine("//类名：", sheet.SheetName, "Ex");
code.PrintLine("//作者：", System.Environment.MachineName);
code.PrintLine("//日期：", DateTime.Now.ToString());
code.PrintLine("//作用：", sheet.SheetName, "的数据类");
code.PrintLine("//注意:", "不要在此类里面写代码!!!");
code.PrintLine("//***************************************************************");
code.PrintLine();
code.PrintLine("using System;");
code.PrintLine("using System.Collections.Generic;");
code.PrintLine();
code.PrintLine("public class ", sheet.SheetName, "{");
code.In();
#region 生成变量
XSSFRow typeRow = (XSSFRow)sheet.GetRow(0);
XSSFRow desRow = (XSSFRow)sheet.GetRow(1);
XSSFRow nameRow = (XSSFRow)sheet.GetRow(2);
for (int i = 0; i < typeRow.LastCellNum; i++)
{
string type = typeRow.GetCell(i).ToString();
string des = string.Empty;
if (desRow.GetCell(i) != null)
{
des = desRow.GetCell(i).ToString();
}
string name = nameRow.GetCell(i).ToString();
code.PrintLine("/// <summary>");
code.PrintLine("///" + des);
code.PrintLine("/// </summary>");
if (!type.Contains("List") && !type.Contains("Dictionary"))
{
code.PrintLine("public ", type, " ", name, ";");
}
else
{
code.PrintLine("public ", type, " ", name, "=new ", type, "();");
}
}
#endregion
code.Out();
code.PrintLine("}");
code.WriteFile(codePath + sheet.SheetName + ".cs");
Console.WriteLine(sheet.SheetName + ".cs 代码生成完成");
Log.Append(sheet.SheetName + ".cs 代码生成完成\n");
}
/// <summary>
/// Pone un autoFit en las columnas
/// </summary>
private void PutFitInCells()
{
int noOfColumns = _currentsheet.GetRow(_rowInicial - 1).LastCellNum;
for (var j = 0; j < noOfColumns; j++)
{
_currentsheet.AutoSizeColumn(j, false);
}
}
public void ReadWriteMultipleAuthors()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithMoreVariousData.xlsx");
XSSFSheet sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
XSSFSheet sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsTrue(sheet1.HasComments);
Assert.IsFalse(sheet2.HasComments);
Assert.AreEqual("Nick Burch",
sheet1.GetRow(4).GetCell(2).CellComment.Author);
Assert.AreEqual("Nick Burch",
sheet1.GetRow(6).GetCell(2).CellComment.Author);
Assert.AreEqual("Torchbox",
sheet1.GetRow(12).GetCell(2).CellComment.Author);
// Save, and re-load the file
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
// Check we still have comments where we should do
sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
Assert.IsNotNull(sheet1.GetRow(4).GetCell(2).CellComment);
Assert.IsNotNull(sheet1.GetRow(6).GetCell(2).CellComment);
Assert.IsNotNull(sheet1.GetRow(12).GetCell(2).CellComment);
// And check they still have the contents they should do
Assert.AreEqual("Nick Burch",
sheet1.GetRow(4).GetCell(2).CellComment.Author);
Assert.AreEqual("Nick Burch",
sheet1.GetRow(6).GetCell(2).CellComment.Author);
Assert.AreEqual("Torchbox",
sheet1.GetRow(12).GetCell(2).CellComment.Author);
// Todo - check text too, once bug fixed
}
/// <summary>
/// Xlsx文件转换(可以获取第一行标题)
/// </summary>
/// <param name="fs">excel文件流</param>
/// <param name="sheetIndex">sheet页索引，默认0</param>
/// <returns></returns>
public static DataTable XlsxTitleToDataTable(FileStream fs, int sheetIndex = 0)
{
//根据路径通过已存在的excel来创建HSSFWorkbook，即整个excel文档
XSSFWorkbook workbook = new XSSFWorkbook(fs);
//获取excel的第一个sheet
XSSFSheet sheet = workbook.GetSheetAt(sheetIndex) as XSSFSheet;
DataTable table = new DataTable();
//获取sheet的首行
XSSFRow headerRow = sheet.GetRow(0) as XSSFRow;
if (headerRow != null)
{
//一行最后一个方格的编号 即总的列数
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;
for (int i = (sheet.FirstRowNum); i <= sheet.LastRowNum; i++)
{
XSSFRow row = sheet.GetRow(i) as XSSFRow;
DataRow dataRow = table.NewRow();
for (int j = row.FirstCellNum; j < cellCount; j++)
{
if (row.GetCell(j) != null)
{
if (row.GetCell(j).CellType == CellType.Numeric)
{
dataRow[j] = row.GetCell(j).NumericCellValue;
}
else
{
dataRow[j] = row.GetCell(j).ToString();
}
}
}
table.Rows.Add(dataRow);
}
workbook = null;
sheet = null;
return(table);
}
else
{
return(null);
}
}
private void btnAddMobile_Click(object sender, EventArgs e)
{
FileStream file = new FileStream(txtFile.Text, FileMode.Open, FileAccess.ReadWrite);
XSSFWorkbook hssfworkbook = new XSSFWorkbook(file);
XSSFSheet sheet1 = hssfworkbook.GetSheet("老师") as XSSFSheet;
List <Teacher> teachers = new List <Teacher>();
var i = 1;
while (sheet1.GetRow(i) != null)
{
var teacher = new Teacher();
teacher.name = sheet1.GetRow(i).GetCell(0).StringCellValue;
teacher.mobile = sheet1.GetRow(i).GetCell(1).NumericCellValue;
if (teacher.name != "")
{
teachers.Add(teacher);
}
i++;
}
XSSFSheet sheet2 = hssfworkbook.GetSheet("Sheet1") as XSSFSheet;
i = 1;
while (sheet2.GetRow(i) != null)
{
var mobile = sheet2.GetRow(i).GetCell(6) != null?sheet2.GetRow(i).GetCell(6).NumericCellValue : 0;
var name = sheet2.GetRow(i).GetCell(5) != null?sheet2.GetRow(i).GetCell(5).StringCellValue : "";
if (name != "" && mobile == 0)
{
var findTeachers = teachers.FindAll(o => o.name == name);
if (findTeachers.Count == 1)
{
mobile = findTeachers[0].mobile;
sheet2.GetRow(i).GetCell(6).SetCellValue(mobile);
}
else if (findTeachers.Count > 1)
{
sheet2.GetRow(i).GetCell(6).SetCellValue("重名了");
}
else
{
sheet2.GetRow(i).GetCell(6).SetCellValue("没找到");
}
}
i++;
}
file = new FileStream(txtFile.Text, FileMode.Open, FileAccess.ReadWrite);
hssfworkbook.Write(file);
file.Flush();
file.Close();
}
public void TestMultisheetFormulaEval()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet1 = wb.CreateSheet("Sheet1") as XSSFSheet;
XSSFSheet sheet2 = wb.CreateSheet("Sheet2") as XSSFSheet;
XSSFSheet sheet3 = wb.CreateSheet("Sheet3") as XSSFSheet;
// sheet1 A1
XSSFCell cell = sheet1.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 A1
cell = sheet2.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet2 B1
cell = sheet2.GetRow(0).CreateCell(1) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet3 A1
cell = sheet3.CreateRow(0).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Numeric);
cell.SetCellValue(1.0);
// sheet1 A2 formulae
cell = sheet1.CreateRow(1).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1)");
// sheet1 A3 formulae
cell = sheet1.CreateRow(2).CreateCell(0) as XSSFCell;
cell.SetCellType(CellType.Formula);
cell.CellFormula = (/*setter*/ "SUM(Sheet1:Sheet3!A1:B1)");
wb.GetCreationHelper().CreateFormulaEvaluator().EvaluateAll();
cell = sheet1.GetRow(1).GetCell(0) as XSSFCell;
Assert.AreEqual(3.0, cell.NumericCellValue);
cell = sheet1.GetRow(2).GetCell(0) as XSSFCell;
Assert.AreEqual(4.0, cell.NumericCellValue);
}
finally
{
wb.Close();
}
}
public static void SetCellValueX(XSSFSheet sheet, int row, int cell, object Value)
{
if (sheet.GetRow(row) == null)
{
sheet.CreateRow(row);
}
if (sheet.GetRow(row).GetCell(cell) == null)
{
sheet.GetRow(row).CreateCell(cell);
}
sheet.GetRow(row).GetCell(cell).SetCellValue(Value == null ? "" : Value.ToString());
}
/// <param name="destSheet"> the sheet being copied/merged. </param>
/// <param name="newSheet"> the destination sheet being copied/merged into. </param>
/// <param name="copyStyle"> true copy the style. </param>
public static void CopyRowInSameSheet(XSSFSheet destSheet, int srcRowIndex, int destRowIndex, bool copyStyle)
{
IDictionary <int?, ICellStyle> styleMap = copyStyle ? new Dictionary <int?, ICellStyle>() : null;
IRow srcRow = destSheet.GetRow(srcRowIndex);
IRow destRow = destSheet.GetRow(destRowIndex);
int maxColumnNum = 0;
CopyRow(destSheet, destSheet, srcRow, destRow, styleMap);
if (srcRow.LastCellNum > maxColumnNum)
{
maxColumnNum = srcRow.LastCellNum;
}
}
public void WriteRead()
{
XSSFWorkbook workbook = XSSFTestDataSamples.OpenSampleWorkbook("WithVariousData.xlsx");
XSSFSheet sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
XSSFSheet sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsTrue(sheet1.HasComments);
Assert.IsFalse(sheet2.HasComments);
// Change on comment on sheet 1, and add another into
// sheet 2
IRow r5 = sheet1.GetRow(4);
IComment cc5 = r5.GetCell(2).CellComment;
cc5.Author = ("Apache POI");
cc5.String = (new XSSFRichTextString("Hello!"));
IRow r2s2 = sheet2.CreateRow(2);
ICell c1r2s2 = r2s2.CreateCell(1);
Assert.IsNull(c1r2s2.CellComment);
IDrawing dg = sheet2.CreateDrawingPatriarch();
IComment cc2 = dg.CreateCellComment(new XSSFClientAnchor());
cc2.Author = ("Also POI");
cc2.String = (new XSSFRichTextString("A new comment"));
c1r2s2.CellComment = (cc2);
// Save, and re-load the file
workbook = (XSSFWorkbook)XSSFTestDataSamples.WriteOutAndReadBack(workbook);
// Check we still have comments where we should do
sheet1 = (XSSFSheet)workbook.GetSheetAt(0);
sheet2 = (XSSFSheet)workbook.GetSheetAt(1);
Assert.IsNotNull(sheet1.GetRow(4).GetCell(2).CellComment);
Assert.IsNotNull(sheet1.GetRow(6).GetCell(2).CellComment);
Assert.IsNotNull(sheet2.GetRow(2).GetCell(1).CellComment);
// And check they still have the contents they should do
Assert.AreEqual("Apache POI",
sheet1.GetRow(4).GetCell(2).CellComment.Author);
Assert.AreEqual("Nick Burch",
sheet1.GetRow(6).GetCell(2).CellComment.Author);
Assert.AreEqual("Also POI",
sheet2.GetRow(2).GetCell(1).CellComment.Author);
Assert.AreEqual("Hello!",
sheet1.GetRow(4).GetCell(2).CellComment.String.String);
}
//protected void Button1_Click(object sender, EventArgs e)
//{
// using (MemoryStream ms = Export())
// {
// Response.ContentType = "application/vnd.ms-word";
// Response.ContentEncoding = Encoding.UTF8;
// Response.Charset = "";
// Response.AppendHeader("Content-Disposition", "attachment;filename=" + HttpUtility.UrlEncode("123.doc", Encoding.UTF8));
// Response.BinaryWrite(Export().GetBuffer());
// Response.End();
// }
//}
//private void ExportExcel(string fileName)
//{
// //byte[] byteArray;
// //if (Request.Browser.Browser == "IE")
// // eFilePath = HttpUtility.UrlEncode(eFilePath);
// //using (FileStream fs = new FileStream(eFilePath, FileMode.Open))
// //{
// // byteArray = new byte[fs.Length];
// // fs.Read(byteArray, 0, byteArray.Length);
// //}
// //Response.Buffer = false;
// //Response.Clear();
// //Response.ContentType = "application/vnd.openxmlformats-officedocument.wordprocessingml.document; name=" + eFilePath;
// //Response.AddHeader("content-disposition", "attachment;filename=" + fileName + "_" + DateTime.Now.ToString("yyyyMMdd_HHmmss") + ".xlsx");
// //Response.BinaryWrite(byteArray);
// //Response.End();
//}
public static MemoryStream ExportExcel(string templateFileName)
{
string filePath = templateFileName;
XSSFWorkbook workBook = null;
XSSFSheet sheet1 = null;
using (FileStream fs = File.OpenRead(filePath))
{
workBook = new XSSFWorkbook(fs);
sheet1 = (XSSFSheet)workBook.GetSheet("Sheet1");
//添加或修改WorkSheet里的数据
System.Data.DataTable dt = new System.Data.DataTable();
//dt = DbHelperMySQLnew.Query("select * from t_jb_info where id='" + id + "'").Tables[0];
//if (dt.Rows.Count > 0)
//{
// if (!string.IsNullOrEmpty(dt.Rows[0]["blrq"].ToString()))
// {
//sheet.GetRow(2).GetCell(1).SetCellValue("56565");
//sheet.GetRow(2).GetCell(2).SetCellValue("hahaha");
//sheet.GetRow(2).GetCell(3).SetCellValue(DateTime.Now.ToString());
// }
//}
// 创建新增行
for (var i = 1; i <= 10; i++)
{
IRow row1 = sheet1.CreateRow(i);
for (var j = 0; j < 10; j++)
{
//新建单元格
NPOI.SS.UserModel.ICell cell = row1.CreateCell(j);
// 单元格赋值
cell.SetCellValue("");
}
}
sheet1.GetRow(1).GetCell(0).SetCellValue("56565");
sheet1.GetRow(1).GetCell(1).SetCellValue("hahaha");
sheet1.GetRow(1).GetCell(2).SetCellValue(DateTime.Now.ToString());
sheet1.ForceFormulaRecalculation = true;
}
using (MemoryStream ms = new MemoryStream())
{
workBook.Write(ms);
return(ms);
}
}
public void Test56170()
{
IWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("56170.xlsx");
XSSFSheet sheet = (XSSFSheet)wb.GetSheetAt(0);
IWorkbook wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
ICell cell;
// add some contents to table so that the table will need expansion
IRow row = sheet.GetRow(0);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(0);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo1");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(1);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo2");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell = row.CreateCell(2);
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
cell.SetCellValue("demo3");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
row = sheet.GetRow(1);
cell = row.CreateCell(0);
cell.SetCellValue("demo1");
cell = row.CreateCell(1);
cell.SetCellValue("demo2");
cell = row.CreateCell(2);
cell.SetCellValue("demo3");
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
// expand table
XSSFTable table = sheet.GetTables()[0];
CellReference startRef = table.GetStartCellReference();
CellReference endRef = table.GetEndCellReference();
table.GetCTTable().@ref = (new CellRangeAddress(startRef.Row, 1, startRef.Col, endRef.Col).FormatAsString());
wbRead = XSSFTestDataSamples.WriteOutAndReadBack(wb);
Assert.IsNotNull(wbRead);
/*FileOutputStream stream = new FileOutputStream("c:\\temp\\output.xlsx");
* workbook.Write(stream);
* stream.Close();*/
}
public void setCellStyle(int firstRow, int lastRow, int firstColumn, int lastColumn)
{
XSSFCellStyle cellStyle = (XSSFCellStyle)workbook.CreateCellStyle();
cellStyle.CloneStyleFrom(defaultCellStyle);
for (int currentRow = firstRow; currentRow <= lastRow; currentRow++)
{
for (int currentColumn = firstColumn; currentColumn <= lastColumn; currentColumn++)
{
try
{
XSSFCell cell = (XSSFCell)sheet.GetRow(currentRow).GetCell(currentColumn);
if (cell != null)
{
cell.CellStyle = cellStyle;
}
else
{
cell = (XSSFCell)sheet.GetRow(currentRow).CreateCell(currentColumn);
cell.CellStyle = cellStyle;
}
}
catch (Exception ex)
{
logger.logException(ex);
consoleLogger.logError(ex.Message);
}
}
}
}
public void Bug51158()
{
// create a workbook
XSSFWorkbook wb1 = new XSSFWorkbook();
XSSFSheet sheet = wb1.CreateSheet("Test Sheet") as XSSFSheet;
XSSFRow row = sheet.CreateRow(2) as XSSFRow;
XSSFCell cell = row.CreateCell(3) as XSSFCell;
cell.SetCellValue("test1");
//XSSFCreationHelper helper = workbook.GetCreationHelper();
//cell.Hyperlink=(/*setter*/helper.CreateHyperlink(0));
XSSFComment comment = (sheet.CreateDrawingPatriarch() as XSSFDrawing).CreateCellComment(new XSSFClientAnchor()) as XSSFComment;
Assert.IsNotNull(comment);
comment.SetString("some comment");
// ICellStyle cs = workbook.CreateCellStyle();
// cs.ShrinkToFit=(/*setter*/false);
// row.CreateCell(0).CellStyle=(/*setter*/cs);
// write the first excel file
XSSFWorkbook wb2 = XSSFTestDataSamples.WriteOutAndReadBack(wb1) as XSSFWorkbook;
Assert.IsNotNull(wb2);
sheet = wb2.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.IsNull(row.GetCell(4));
// add a new cell to the sheet
cell = row.CreateCell(4) as XSSFCell;
cell.SetCellValue("test2");
// write the second excel file
XSSFWorkbook wb3 = XSSFTestDataSamples.WriteOutAndReadBack(wb2) as XSSFWorkbook;
Assert.IsNotNull(wb3);
sheet = wb3.GetSheetAt(0) as XSSFSheet;
row = sheet.GetRow(2) as XSSFRow;
Assert.AreEqual("test1", row.GetCell(3).StringCellValue);
Assert.AreEqual("test2", row.GetCell(4).StringCellValue);
wb3.Close();
wb2.Close();
wb1.Close();
}
public void ExcelFile()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("Лист 1");
int countColumn = 2;
for (int i = 0; i < Liste.Count; i++)
{
var currentRow = sh.CreateRow(i);
for (int j = 0; j < countColumn; j++)
{
var currentCell = currentRow.CreateCell(j);
if (j == 0)
{
currentCell.SetCellValue(Liste[i].Name);
}
if (j == 1)
{
currentCell.SetCellValue(Liste[i].Email);
}
}
}
for (int i = 0; i < sh.LastRowNum; i++)
{
for (int j = i + 1; j < sh.LastRowNum; j++)
{
var currentRow = sh.GetRow(i);
var currentRows = sh.GetRow(j);
if ((currentRow != null) && (currentRows != null))
{
if (currentRows.GetCell(1).StringCellValue == currentRow.GetCell(1).StringCellValue)
{
DeleteRow(sh, sh.GetRow(j));
}
}
}
}
if (!File.Exists("d:\\Дет.сад.xlsx"))
{
File.Delete("d:\\Дет.сад.xlsx");
}
using (var fs = new FileStream("d:\\Дет.сад.xlsx", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
Process.Start("d:\\Дет.сад.xlsx");
Liste.Clear();
}
public string Excel2MysqlMssql(XSSFSheet sheet, int cot, int dbType)
{
var str = "";
for (int i = 1; i <= cot; i++)
{
excel sFC = new excel();
sFC.name = sheet.GetRow(i).GetCell(0) == null ? "" : sheet.GetRow(i).GetCell(0).ToString();
if (sFC.name.Length == 0 || sFC.name == "" || sFC.name == null)
{
cot -= 1;
str = str.Remove(str.Length - 3, 2);
continue;
}
str += (sFC.name + " ");
sFC.desc = sheet.GetRow(i).GetCell(1) == null ? "" : sheet.GetRow(i).GetCell(1).ToString();
sFC.type = sheet.GetRow(i).GetCell(2) == null ? "" : sheet.GetRow(i).GetCell(2).ToString();
str += (sFC.type + " ");
sFC.isNull = sheet.GetRow(i).GetCell(3) == null ? "" : sheet.GetRow(i).GetCell(3).ToString();
sFC.defaultContext = sheet.GetRow(i).GetCell(4) == null ? "" : sheet.GetRow(i).GetCell(4).ToString();
if (sFC.isNull.Length != 0)
{
str += ("NOT NULL ");
}
else
{
str += (" NULL ");
}
if (sFC.defaultContext.Length != 0)
{
str += ("DEFAULT " + sFC.defaultContext + " ");
}
if (sFC.name == "ID")
{
str += "primary key ";
}
if (dbType != 2)
{
str += ("COMMENT '" + sFC.desc + "' ");
}
if (i != cot)
{
str += " ,\n";
}
}
return(str);
}
public void TestTableFormulas()
{
XSSFWorkbook wb = XSSFTestDataSamples.OpenSampleWorkbook("StructuredReferences.xlsx");
try
{
IFormulaEvaluator eval = new XSSFFormulaEvaluator(wb);
XSSFSheet tableSheet = wb.GetSheet("Table") as XSSFSheet;
XSSFSheet formulaSheet = wb.GetSheet("Formulas") as XSSFSheet;
Confirm(eval, tableSheet.GetRow(5).GetCell(0), 49);
Confirm(eval, formulaSheet.GetRow(0).GetCell(0), 209);
Confirm(eval, formulaSheet.GetRow(1).GetCell(0), "one");
// test changing a table value, to see if the caches are properly Cleared
// Issue 59814
// this test passes before the fix for 59814
tableSheet.GetRow(1).GetCell(1).SetCellValue("ONEA");
Confirm(eval, formulaSheet.GetRow(1).GetCell(0), "ONEA");
// test Adding a row to a table, issue 59814
IRow newRow = tableSheet.GetRow(7);
if (newRow == null)
{
newRow = tableSheet.CreateRow(7);
}
newRow.CreateCell(0, CellType.Formula).CellFormula = (/*setter*/ "\\_Prime.1[[#This Row],[@Number]]*\\_Prime.1[[#This Row],[@Number]]");
newRow.CreateCell(1, CellType.String).SetCellValue("thirteen");
newRow.CreateCell(2, CellType.Numeric).SetCellValue(13);
// update Table
XSSFTable table = wb.GetTable("\\_Prime.1");
AreaReference newArea = new AreaReference(table.StartCellReference, new CellReference(table.EndRowIndex + 1, table.EndColIndex));
String newAreaStr = newArea.FormatAsString();
table.GetCTTable().@ref = (/*setter*/ newAreaStr);
table.GetCTTable().autoFilter.@ref = (/*setter*/ newAreaStr);
table.UpdateHeaders();
//table.UpdateReferences();
// these fail before the fix for 59814
Confirm(eval, tableSheet.GetRow(7).GetCell(0), 13 * 13);
Confirm(eval, formulaSheet.GetRow(0).GetCell(0), 209 + 13 * 13);
}
finally
{
wb.Close();
}
}
public string sum(int row1, int row2)
{
XSSFWorkbook book = new XSSFWorkbook(new FileStream(System.Web.HttpContext.Current.Server.MapPath("~/Content/aliaa.xlsx"), FileMode.Open));
XSSFSheet sheet1 = book.GetSheetAt(0) as XSSFSheet;
double y1 = sheet1.GetRow(row1).GetCell(1).NumericCellValue;
double y2 = sheet1.GetRow(row2).GetCell(1).NumericCellValue;
double ppm_sum = y1 + y2;
double y3 = sheet1.GetRow(row1).GetCell(2).NumericCellValue;
double y4 = sheet1.GetRow(row2).GetCell(2).NumericCellValue;
double met = y3 + y4;
return("ppm sum=" + ppm_sum + "--------- met sum=" + met);
}
