public void createRowsInstance(int rowCount)
{
for (int counter = 0; counter < rowCount + 100; counter++)
{
sheet.CreateRow(counter);
}
}
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"));
}
private void renderTotalSheet()
{
XSSFSheet totalST = newwb.CreateSheet("汇总") as XSSFSheet;
XSSFRow newRow = totalST.CreateRow(0) as XSSFRow;
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue("老师");
newRow.CreateCell(1).SetCellValue("手机号");
newRow.CreateCell(2).SetCellValue("差值和");
newRow.CreateCell(3).SetCellValue("班级数");
newRow.CreateCell(4).SetCellValue("平均值");
DataView dvClass = dtTotal.DefaultView;
DataTable dataTableClasses = dvClass.ToTable(true, "teacher", "mobile");
for (var j = 0; j < dataTableClasses.Rows.Count; j++)
{
newRow = totalST.CreateRow(j + 1) as XSSFRow;
string teacher = dataTableClasses.Rows[j]["teacher"].ToString();
string mobile = dataTableClasses.Rows[j]["mobile"].ToString();
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue(teacher);
newRow.CreateCell(1).SetCellValue(mobile);
dvClass.RowFilter = "teacher='" + teacher + "' and mobile='" + mobile + "'";
DataTable newTable = dvClass.ToTable(false, "dvalue");
Single total = Single.Parse(newTable.Compute("sum(dvalue)", "").ToString());
int totalCount = newTable.Rows.Count;
newRow.CreateCell(2).SetCellValue(Math.Round(total, 2));
newRow.CreateCell(3).SetCellValue(totalCount);
newRow.CreateCell(4).SetCellValue(Math.Round(total / totalCount, 2));
}
}
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("Email");
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].電話);
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].Email);
u_sheet.GetRow(i + 1).CreateCell(6).SetCellValue(list[i].客戶分類);
}
MemoryStream ms = new MemoryStream();
workbook.Write(ms);
return(File(ms.ToArray(), "application/vnd.ms-excel", "客戶.xlsx"));
}
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 static void ExportDataTableToExcel(DataTable dtSource, string strFileName)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workbook.CreateSheet("mySheet");
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(0);
foreach (DataColumn column in dtSource.Columns)
{
// create the cells in the first row, and add data into these cells circularly
dataRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
//create rows on the basis of data from datatable(not including table header), and add data into cells in every row
for (int i = 0; i < dtSource.Rows.Count; i++)
{
dataRow = (XSSFRow)sheet.CreateRow(i + 1);
for (int j = 0; j < dtSource.Columns.Count; j++)
{
dataRow.CreateCell(j).SetCellValue(dtSource.Rows[i][j].ToString());
}
}
using (MemoryStream ms = new MemoryStream())
{
using (FileStream fs = new FileStream(strFileName, FileMode.Create, FileAccess.Write))
{
workbook.Write(fs);
}
}
}
private void renderLastTestSheet()
{
XSSFSheet totalST = newwb.CreateSheet("最后一次测试") as XSSFSheet;
XSSFRow newRow = totalST.CreateRow(0) as XSSFRow;
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue("老师");
newRow.CreateCell(1).SetCellValue("手机号");
newRow.CreateCell(3).SetCellValue("课程名");
newRow.CreateCell(4).SetCellValue("测试名");
newRow.CreateCell(2).SetCellValue("差值");
for (var j = 0; j < dtTotal.Rows.Count; j++)
{
newRow = totalST.CreateRow(j + 1) as XSSFRow;
string teacher = dtTotal.Rows[j]["teacher"].ToString();
string mobile = dtTotal.Rows[j]["mobile"].ToString();
string dvalue = dtTotal.Rows[j]["dvalue"].ToString();
string className = dtTotal.Rows[j]["className"].ToString();
string testName = dtTotal.Rows[j]["testName"].ToString();
// 老师 手机号 优等人数 良等人数 有效人数
newRow.CreateCell(0).SetCellValue(teacher);
newRow.CreateCell(1).SetCellValue(mobile);
newRow.CreateCell(2).SetCellValue(dvalue);
newRow.CreateCell(3).SetCellValue(className);
newRow.CreateCell(4).SetCellValue(testName);
}
}
public void TestBug55843b()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet("test") as XSSFSheet;
XSSFRow row = sheet.CreateRow(0) as XSSFRow;
XSSFRow row2 = sheet.CreateRow(1) as XSSFRow;
XSSFCell cellA2 = row2.CreateCell(0, CellType.Formula) as XSSFCell;
XSSFCell cellB1 = row.CreateCell(1, CellType.Numeric) as XSSFCell;
cellB1.SetCellValue(10);
XSSFFormulaEvaluator formulaEvaluator = wb.GetCreationHelper().CreateFormulaEvaluator() as XSSFFormulaEvaluator;
cellA2.SetCellFormula("IF(B1=0,\"\",((ROW())))");
CellValue Evaluate = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(Evaluate);
Assert.AreEqual("2", Evaluate.FormatAsString());
cellA2.CellFormula = (/*setter*/ "IF(NOT(B1=0),((ROW())),\"\")");
CellValue EvaluateN = formulaEvaluator.Evaluate(cellA2);
System.Console.WriteLine(EvaluateN);
Assert.AreEqual(Evaluate.ToString(), EvaluateN.ToString());
Assert.AreEqual("2", EvaluateN.FormatAsString());
}
finally
{
wb.Close();
}
}
public void CreateSheet(DataTable data, XSSFSheet sheet, ICollection <LookUpColumns> columns)
{
//CreateHeader
var header = sheet.CreateRow(0);
header.CreateCell(0);
int i = 0, j = 0;
columns.Add(new LookUpColumns {
Id = 0, ColumnName = "Id", Unique = true, DataType = "int", Nullable = false
});
foreach (LookUpColumns col in columns.OrderBy(c => c.Id))
{
var cell = header.CreateCell(i);
cell.SetCellValue(col.ColumnName);
sheet.AutoSizeColumn(i);
i++;
}
//CreateRows
i = 0;
foreach (DataRow dr in data.Rows)
{
var row = sheet.CreateRow(i + 1);
j = 0;
foreach (LookUpColumns col in columns.OrderBy(c => c.Id))
{
row.CreateCell(j).SetCellValue(dr[col.ColumnName].ToString());
j++;
}
i++;
}
}
static void Main(string[] args)
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet1 = (XSSFSheet)workbook.CreateSheet("Sheet1");
sheet1.CreateRow(0).CreateCell(0).SetCellValue("This is a Sample");
int x = 1;
for (int i = 1; i <= 15; i++)
{
IRow row = sheet1.CreateRow(i);
for (int j = 0; j < 15; j++)
{
row.CreateCell(j).SetCellValue(x++);
}
}
XSSFTable table = sheet1.CreateTable();
table.Name = "Tabella1";
table.DisplayName = "Tabella1";
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}
/// <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);
}
}
public static XSSFWorkbook BuildSwitchData <T>(string SheetName, List <T> list, Dictionary <string, string> FiedNames)
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet(SheetName); //创建工作表
sheet.CreateFreezePane(0, 1); //冻结列头行
XSSFRow row_Title = (XSSFRow)sheet.CreateRow(0); //创建列头行
#region 生成列头
int ii = 0;
foreach (string key in FiedNames.Keys)
{
XSSFCell cell_Title = (XSSFCell)row_Title.CreateCell(ii); //创建单元格
//cell_Title.CellStyle = cs_Title; //将样式绑定到单元格
cell_Title.SetCellValue(key);
//sheet.SetColumnWidth(ii, 25 * 256);//设置列宽
ii++;
}
#endregion
//获取 实体类 类型对象
Type t = typeof(T); // model.GetType();
//获取 实体类 所有的 公有属性
List <PropertyInfo> proInfos = t.GetProperties(BindingFlags.Instance | BindingFlags.Public).ToList();
//创建 实体属性 字典集合
Dictionary <string, PropertyInfo> dictPros = new Dictionary <string, PropertyInfo>();
//将 实体属性 中要修改的属性名 添加到 字典集合中 键：属性名 值：属性对象
proInfos.ForEach(p =>
{
if (FiedNames.Values.Contains(p.Name))
{
dictPros.Add(p.Name, p);
}
});
for (int i = 0; i < list.Count; i++)
{
XSSFRow row_Content = (XSSFRow)sheet.CreateRow(i + 1); //创建行
row_Content.HeightInPoints = 20;
int jj = 0;
foreach (string proName in FiedNames.Values)
{
if (dictPros.ContainsKey(proName))
{
XSSFCell cell_Conent = (XSSFCell)row_Content.CreateCell(jj); //创建单元格
//如果存在，则取出要属性对象
PropertyInfo proInfo = dictPros[proName];
//获取对应属性的值
object value = proInfo.GetValue(list[i], null); //object newValue = model.uName;
string cell_value = value == null ? "" : value.ToString();
cell_Conent.SetCellValue(cell_value);
jj++;
}
}
}
return(wb);
}
static public void ExportToXLSX(SpreadsheetComponentData spreadsheet, string path)
{
CultureInfo cc = Thread.CurrentThread.CurrentCulture, cuc = Thread.CurrentThread.CurrentUICulture;
CultureInfo ci = new CultureInfo("en-US");
Thread.CurrentThread.CurrentCulture = ci;
Thread.CurrentThread.CurrentUICulture = ci;
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sh = (XSSFSheet)wb.CreateSheet("Sheet1");
// column header
var headerFont = wb.CreateFont();
headerFont.Boldweight = (short)FontBoldWeight.Bold;
var headerStyle = wb.CreateCellStyle();
headerStyle.SetFont(headerFont);
bool hasHeader = false;
if (spreadsheet.HasCustomLabeledColumnHeader())
{
var r = sh.CreateRow(0);
for (int col = 0; col < spreadsheet.columnDatas.Count; ++col)
{
var cell = r.CreateCell(col);
cell.CellStyle = headerStyle;
cell.SetCellValue(spreadsheet.columnDatas[col].HasCustomLabel() ? spreadsheet.columnDatas[col].label : SpreadsheetComponentData.GetDefaultColumnName(col));
}
hasHeader = true;
}
for (int row = 0; row < spreadsheet.rowDatas.Count; ++row)
{
var r = sh.CreateRow(row + (hasHeader ? 1 : 0));
for (int col = 0; col < spreadsheet.columnDatas.Count; ++col)
{
var cell = r.CreateCell(col);
SetCellValue(cell, spreadsheet.cells[row][col]);
}
}
try
{
using (var fs = new FileStream(path, FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
}
catch (Exception e)
{
throw new Exception("Cannot write to Excel file. Another program might be using it.");
}
finally
{
Thread.CurrentThread.CurrentCulture = cc;
Thread.CurrentThread.CurrentUICulture = cuc;
}
}
public void WriteExcel(Dictionary <string, LocText> arg)
{
arg = arg.OrderBy(o => o.Key).ToDictionary(o => o.Key, p => p.Value);
XSSFWorkbook workbook = new XSSFWorkbook(); //新建xlsx工作簿
workbook.CreateSheet("Sheet1");
XSSFSheet sheet = workbook.GetSheet("Sheet1") as XSSFSheet;
string[] keys = arg.Keys.ToArray();
int header_length = 1;
IRow field = sheet.CreateRow(0);
ICellStyle wrapCellStyle = workbook.CreateCellStyle();
wrapCellStyle.WrapText = true;
wrapCellStyle.Alignment = HorizontalAlignment.Left;
wrapCellStyle.VerticalAlignment = VerticalAlignment.Top;
ICellStyle jpCellStyle = workbook.CreateCellStyle();
jpCellStyle.Alignment = HorizontalAlignment.Left;
jpCellStyle.VerticalAlignment = VerticalAlignment.Top;
jpCellStyle.WrapText = true;
field.CreateCell(0).SetCellValue("Key");
field.CreateCell(1).SetCellValue("日本語");
field.CreateCell(2).SetCellValue("简体中文");
field.CreateCell(3).SetCellValue("繁體中文");
for (int i = 0; i < keys.Length; i++)
{
IRow row = sheet.CreateRow(header_length + i);
ICell cellKey = row.CreateCell(0);
cellKey.SetCellType(CellType.String);
cellKey.SetCellValue(keys[i]);
ICell celljp = row.CreateCell(1);
celljp.SetCellType(CellType.String);
celljp.SetCellValue(arg[keys[i]].ja_JP);
celljp.CellStyle = wrapCellStyle;
ICell cellcn = row.CreateCell(2);
cellcn.SetCellType(CellType.String);
cellcn.SetCellValue(arg[keys[i]].zh_CN);
cellcn.CellStyle = wrapCellStyle;
}
//列宽自适应
for (int i = 0; i <= keys.Length % 1000; i++)
{
sheet.AutoSizeColumn(i);
}
using (FileStream fs = File.Create(mFilePath))
{
workbook.Write(fs);
workbook.Close();
}
}
/// <summary>
/// 导出xls结尾的Excel数据，每个实体必须含有ExcelColNameAttr
/// </summary>
/// <param name="dataList"></param>
/// <param name="outputStream"></param>
public static void ListToExcelXlsx <T>(List <T>[] dataListArrays, Stream outputStream, string groupName = null)
{
XSSFWorkbook xssfWorkbook = new XSSFWorkbook();
ICellStyle dateCellStyle = xssfWorkbook.CreateCellStyle();
ICreationHelper creationHelper = xssfWorkbook.GetCreationHelper();
dateCellStyle.DataFormat = creationHelper.CreateDataFormat().GetFormat("yyyy-MM-dd hh:mm:ss");
foreach (List <T> dataList in dataListArrays)
{
if (dataList == null || dataList.Count == 0)
{
throw new Exception("数据列表为空，未能导出数据！");
}
List <ExcelColInfo> excelColInfoList = ColFilter(typeof(T), groupName);
if (excelColInfoList.Count == 0)
{
throw new Exception("字段列表为空，不能导出数据！");
}
XSSFSheet xssfSheet = (XSSFSheet)xssfWorkbook.CreateSheet(GetSheetName(typeof(T), groupName));
XSSFRow xssfRow = (XSSFRow)xssfSheet.CreateRow(0);
ExcelCol excelCol;
int width;
int[] colWidths = new int[excelColInfoList.Count];
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(xssfRow.CreateCell(excelCol.ColIndex), excelCol.ColName);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
object temp;
for (int i = 0, len = dataList.Count; i < len; i++)
{
temp = dataList[i];
xssfRow = (XSSFRow)xssfSheet.CreateRow(i + 1);
foreach (ExcelColInfo excelColInfo in excelColInfoList)
{
excelCol = excelColInfo.ExcelCol;
width = SetCellValue(
(XSSFCell)xssfRow.CreateCell(excelCol.ColIndex),
excelColInfo.PropertyInfo.GetValue(temp),
dateCellStyle
);
if (width > colWidths[excelCol.ColIndex])
{
colWidths[excelCol.ColIndex] = width;
}
}
}
UpdateColWidth(xssfSheet, colWidths);
}
xssfWorkbook.Write(outputStream);
}
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 void SetUp()
{
IWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
IRow row1 = sheet.CreateRow(0);
// Create a cell and Put a value in it.
ICell cell = row1.CreateCell(0);
cell.SetCellValue("Names");
ICell cell2 = row1.CreateCell(1);
cell2.SetCellValue("#");
ICell cell7 = row1.CreateCell(2);
cell7.SetCellValue("Data");
ICell cell10 = row1.CreateCell(3);
cell10.SetCellValue("Value");
IRow row2 = sheet.CreateRow(1);
ICell cell3 = row2.CreateCell(0);
cell3.SetCellValue("Jan");
ICell cell4 = row2.CreateCell(1);
cell4.SetCellValue(10);
ICell cell8 = row2.CreateCell(2);
cell8.SetCellValue("Apa");
ICell cell11 = row1.CreateCell(3);
cell11.SetCellValue(11.11);
IRow row3 = sheet.CreateRow(2);
ICell cell5 = row3.CreateCell(0);
cell5.SetCellValue("Ben");
ICell cell6 = row3.CreateCell(1);
cell6.SetCellValue(9);
ICell cell9 = row3.CreateCell(2);
cell9.SetCellValue("Bepa");
ICell cell12 = row1.CreateCell(3);
cell12.SetCellValue(12.12);
AreaReference source = new AreaReference("A1:C2");
pivotTable = sheet.CreatePivotTable(source, new CellReference("H5"));
}
/// <summary>
/// Se encarga de poner el titulo de las tablas y de definil el estilo que tendra cada columna por defecto
/// </summary>
private void Addcabeceras()
{
var row = _currentsheet.GetRow(_rowInicial - 1) ?? _currentsheet.CreateRow(_rowInicial - 1);
var cell = 0;
var item = _informacion.FirstOrDefault();
if (item == null)
{
return;
}
foreach (var prop in item.GetType().GetProperties().Where(p => !p.GetGetMethod().GetParameters().Any()))
{
var celda = row.GetCell(cell) ?? row.CreateCell(cell);
if (_mascaras)
{
var hfont = _excel.CreateFont();
hfont.FontHeightInPoints = 12;
hfont.Color = IndexedColors.Black.Index;
hfont.FontName = "Century Gothic";
if (prop.PropertyType == typeof(DateTime))
{
var style = _excel.CreateCellStyle();
style.DataFormat = _excel.CreateDataFormat().GetFormat("m/d/yyyy");
style.SetFont(hfont);
_currentsheet.SetDefaultColumnStyle(cell, style);
}
else if (prop.PropertyType == typeof(decimal))
{
var style = _excel.CreateCellStyle();
style.DataFormat = _excel.CreateDataFormat().GetFormat("[$$-409]#,##0.00");
style.SetFont(hfont);
_currentsheet.SetDefaultColumnStyle(cell, style);
celda.SetCellType(CellType.Numeric);
}
else
{
var style = _excel.CreateCellStyle();
style.SetFont(hfont);
_currentsheet.SetDefaultColumnStyle(cell, style);
celda.SetCellType(CellType.Numeric);
}
}
cell++;
celda.SetCellValue(prop.Name);
}
}
/// <summary>
/// Render DataTable to NPOI Excel 2003 MemoryStream
/// NOTE: Limitation of 65,536 rows suppored by XLS
/// </summary>
/// <param name="sourceTable">Source DataTable</param>
/// <returns>MemoryStream containing NPOI Excel workbook</returns>
public static Stream RenderDataTableToExcelXSSF(DataTable sourceTable)
{
XSSFWorkbook xssfworkbook = new XSSFWorkbook();
MemoryStream memoryStream = new MemoryStream();
// By default NPOI creates "Sheet0" which is inconsistent with Excel using "Sheet1"
XSSFSheet sheet = (XSSFSheet)xssfworkbook.CreateSheet("Sheet1");
XSSFRow headerRow = (XSSFRow)sheet.CreateRow(0);
// Header Row
foreach (DataColumn column in sourceTable.Columns)
{
headerRow.CreateCell(column.Ordinal).SetCellValue(column.ColumnName);
}
// Detail Rows
int rowIndex = 1;
// 建立儲存格樣式。
XSSFCellStyle style1 = (XSSFCellStyle)xssfworkbook.CreateCellStyle();//workbook.CreateCellStyle();
style1.FillForegroundColor = NPOI.HSSF.Util.HSSFColor.Blue.Index2;
style1.FillBackgroundColor = NPOI.HSSF.Util.HSSFColor.Red.Index;
style1.FillPattern = NPOI.SS.UserModel.FillPattern.SolidForeground;//HSSFCellStyle.SOLID_FOREGROUND;
XSSFFont f = (XSSFFont)xssfworkbook.CreateFont();
f.Color = NPOI.HSSF.Util.HSSFColor.Red.Index;
f.FontName = "宋体";
foreach (DataRow row in sourceTable.Rows)
{
XSSFRow dataRow = (XSSFRow)sheet.CreateRow(rowIndex);
foreach (DataColumn column in sourceTable.Columns)
{
//HSSFCell cell1 = dataRow.CreateCell(column.Ordinal).SetCellValue(row[column].ToString());
XSSFCell cell = (XSSFCell)dataRow.CreateCell(column.Ordinal);
cell.CellStyle = style1;
cell.CellStyle.SetFont(f);
cell.SetCellValue(row[column].ToString());
}
rowIndex++;
}
xssfworkbook.Write(memoryStream);
memoryStream.Flush();
memoryStream.Position = 0;
return(memoryStream);
}
public void TestCopyRowFrom()
{
XSSFWorkbook workbook = new XSSFWorkbook();
XSSFSheet sheet = workbook.CreateSheet("test") as XSSFSheet;
XSSFRow srcRow = sheet.CreateRow(0) as XSSFRow;
srcRow.CreateCell(0).SetCellValue("Hello");
XSSFRow destRow = sheet.CreateRow(1) as XSSFRow;
destRow.CopyRowFrom(srcRow, new CellCopyPolicy());
Assert.IsNotNull(destRow.GetCell(0));
Assert.AreEqual("Hello", destRow.GetCell(0).StringCellValue);
workbook.Close();
}
private void exportXLSButton_Click(object sender, EventArgs e)
{
if (personListBox.SelectedItem == null)
{
return;
}
saveFileDialog2.ShowDialog();
if (saveFileDialog2.FileName != "")
{
IWorkbook workbook = new XSSFWorkbook();
workbook.CreateSheet("Sheet A1");
XSSFSheet sheet = workbook.GetSheetAt(0) as XSSFSheet;
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("Дата");
row.CreateCell(1).SetCellValue("Начислено");
row.CreateCell(2).SetCellValue("Оплачено");
int curRow = 1;
row = sheet.CreateRow(curRow);
foreach (Event curEvent in ((Person)personListBox.SelectedItem).events.Where(
x => { return(x.date <= curDateCalendar.SelectionStart); }))
{
if (curEvent.ToRow(row))
{
curRow++;
row = sheet.CreateRow(curRow);
}
}
Person person = (Person)personListBox.SelectedItem;
Report report = person.Simulate(curDateCalendar.SelectionStart);
sheet.AutoSizeColumn(0);
sheet.AutoSizeColumn(1);
sheet.AutoSizeColumn(2);
row.CreateCell(0).SetCellValue("Итого на " + curDateCalendar.SelectionStart.ToString().Substring(0, 11));
row.CreateCell(1).SetCellValue("Долг:");
row.CreateCell(2).SetCellValue(report.totalDepth);
row.CreateCell(3).SetCellValue("Пеня:");
row.CreateCell(4).SetCellValue(report.totalPenalty);
row.CreateCell(5).SetCellValue("Долг+пеня:");
row.CreateCell(6).SetCellValue(report.totalDepth + report.totalPenalty);
for (int i = 0; i < 7; i++)
{
sheet.AutoSizeColumn(i);
}
FileStream sw = File.Create(saveFileDialog2.FileName);
workbook.Write(sw);
sw.Close();
}
}
public void Test47278()
{
XSSFWorkbook wb = (XSSFWorkbook)_testDataProvider.CreateWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
IRow row = sheet.CreateRow(0);
SharedStringsTable sst = wb.GetSharedStringSource();
Assert.AreEqual(0, sst.Count);
//case 1. cell.SetCellValue(new XSSFRichTextString((String)null));
ICell cell_0 = row.CreateCell(0);
XSSFRichTextString str = new XSSFRichTextString((String)null);
Assert.IsNull(str.String);
cell_0.SetCellValue(str);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(CellType.Blank, cell_0.CellType);
//case 2. cell.SetCellValue((String)null);
ICell cell_1 = row.CreateCell(1);
cell_1.SetCellValue((String)null);
Assert.AreEqual(0, sst.Count);
Assert.AreEqual(CellType.Blank, cell_1.CellType);
}
private void Button_Click_1(object sender, RoutedEventArgs e)
{
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);
}
sh.AutoSizeColumn(j);
}
}
if (!File.Exists("d:\\vuzkazahstan.xlsx"))
{
File.Delete("d:\\vuzkazahstan.xlsx");
}
using (var fs = new FileStream("d:\\vuzkazahstan.xlsx", FileMode.Create, FileAccess.Write))
{
wb.Write(fs);
}
Process.Start("d:\\vuzkazahstan.xlsx");
Liste.Clear();
}
/// <summary>
/// Копипрование содержимого листа
/// </summary>
/// <param name="source"></param>
/// <param name="destination"></param>
private void CopySheet(HSSFSheet source, XSSFSheet destination)
{
var maxColumnNum = 0;
var mergedRegions = new List <CellRangeAddress>();
for (int i = source.FirstRowNum; i <= source.LastRowNum; i++)
{
var srcRow = (HSSFRow)source.GetRow(i);
var destRow = (XSSFRow)destination.CreateRow(i);
if (srcRow != null)
{
CopyRow(source, destination, srcRow, destRow, mergedRegions);
// поиск максимального номера ячейки в строке для копирования ширины столбцов
if (srcRow.LastCellNum > maxColumnNum)
{
maxColumnNum = srcRow.LastCellNum;
}
}
}
// копируем ширину столбцов исходного документа
for (int i = 0; i <= maxColumnNum; i++)
{
destination.SetColumnWidth(i, source.GetColumnWidth(i));
}
}
private void write2Excel(int t, float tmp, float tar)
{
if (!File.Exists(cfg.AppSettings.Settings["logPath"].Value))
{
XSSFWorkbook workBook = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)workBook.CreateSheet(); //创建一个sheet
IRow frow0 = sheet.CreateRow(0); // 添加一行（一般第一行是表头）
frow0.CreateCell(0).SetCellValue("时间");
frow0.CreateCell(1).SetCellValue("实际温度");
frow0.CreateCell(2).SetCellValue("目标温度"); //表头内容
FileStream sw = File.OpenWrite(cfg.AppSettings.Settings["logPath"].Value);
workBook.Write(sw);
workBook.Close();
}
else
{
FileStream rs = new FileStream(cfg.AppSettings.Settings["logPath"].Value, FileMode.Open, FileAccess.Read);
XSSFWorkbook workBook = new XSSFWorkbook(rs);
ISheet sheet = (XSSFSheet)workBook.GetSheetAt(0);
IRow row = sheet.CreateRow(sheet.LastRowNum + 1);
row.CreateCell(0).SetCellValue(t);
row.CreateCell(1).SetCellValue(tmp);
row.CreateCell(2).SetCellValue(tar);
rs.Close();
rs.Dispose();
FileStream sw = new FileStream(cfg.AppSettings.Settings["logPath"].Value, FileMode.Create, FileAccess.Write);
workBook.Write(sw);
workBook.Close();
sw.Close();
sw.Dispose();
}
}
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 void TestMissingRAttribute()
{
XSSFWorkbook wb = new XSSFWorkbook();
XSSFSheet sheet = (XSSFSheet)wb.CreateSheet();
XSSFRow row = (XSSFRow)sheet.CreateRow(0);
XSSFCell a1 = (XSSFCell)row.CreateCell(0);
a1.SetCellValue("A1");
XSSFCell a2 = (XSSFCell)row.CreateCell(1);
a2.SetCellValue("B1");
XSSFCell a4 = (XSSFCell)row.CreateCell(4);
a4.SetCellValue("E1");
XSSFCell a6 = (XSSFCell)row.CreateCell(5);
a6.SetCellValue("F1");
assertCellsWithMissingR(row);
a2.GetCTCell().unsetR();
a6.GetCTCell().unsetR();
assertCellsWithMissingR(row);
wb = (XSSFWorkbook)_testDataProvider.WriteOutAndReadBack(wb);
row = (XSSFRow)wb.GetSheetAt(0).GetRow(0);
assertCellsWithMissingR(row);
}
/// <summary>
/// 取得ExcelTemplate寫入新資料回傳
/// 固定拷貝表頭一列，第二列拷貝樣式重複帶資料寫入
/// </summary>
/// <typeparam name="T">泛型使用</typeparam>
/// <param name="workbook">excel物件</param>
/// <param name="CellNum">Cell欄位數量</param>
/// <param name="ListData">要寫入的資料(泛型)</param>
/// <param name="PropertyArr">輸入泛型資料屬性，需要依照Cell順序放入</param>
/// <param name="SheetPage">要拷貝及寫入的Sheet</param>
/// <returns></returns>
public XSSFWorkbook ExcelByTemplate <T>(XSSFWorkbook workbook, int CellNum, List <T> ListData, string[] PropertyArr, int SheetPage = 0)
{
XSSFSheet sheet = (XSSFSheet)workbook.GetSheetAt(SheetPage);
//輸出起始列，拷貝Style列
int copyFormatRow = 1;
//設定各cell的style
Dictionary <int, ICellStyle> cellStyleDictionary = new Dictionary <int, ICellStyle>();
for (int i = 0; i < CellNum; i++)
{
cellStyleDictionary.Add(i, sheet.GetRow(copyFormatRow).GetCell(i).CellStyle);
}
//資料寫入 Row
for (var i = 0; i < ListData.Count; i++)
{
IRow row = sheet.CreateRow(copyFormatRow + i);
//處理Cell
for (var j = 0; j < CellNum; j++)
{
//填值
SetCellValueAndStyle(row.CreateCell(j), cellStyleDictionary[j], GetPropertyValue(ListData[i], PropertyArr[j]));
}
}
return(workbook);
}
public void ExcelImportStart(DataTable datable, string sheetname, int rowint)
{
XSSFSheet ws = (XSSFSheet)hssfworkbook.GetSheet(sheetname);
//添加或修改WorkSheet里的数据
System.Data.DataTable dt = datable;
#region
if (dt.Rows.Count > 0)
{
var rowIndex = rowint;
foreach (DataRow row in dt.Rows)
{
XSSFRow dataRow = ws.CreateRow(rowIndex) as XSSFRow;
foreach (DataColumn column in dt.Columns)
{
XSSFCell newCell = dataRow.CreateCell(column.Ordinal) as XSSFCell;
string drValue = row[column].ToString();
newCell.SetCellValue(drValue);
}
rowIndex++;
}
}
#endregion
ws.ForceFormulaRecalculation = true;
}
public void Test53965()
{
XSSFWorkbook wb = new XSSFWorkbook();
try
{
XSSFSheet sheet = wb.CreateSheet() as XSSFSheet;
List <IDataValidation> lst = sheet.GetDataValidations(); //<-- works
Assert.AreEqual(0, lst.Count);
//create the cell that will have the validation applied
sheet.CreateRow(0).CreateCell(0);
IDataValidationHelper dataValidationHelper = sheet.GetDataValidationHelper();
IDataValidationConstraint constraint = dataValidationHelper.CreateCustomConstraint("SUM($A$1:$A$1) <= 3500");
CellRangeAddressList addressList = new CellRangeAddressList(0, 0, 0, 0);
IDataValidation validation = dataValidationHelper.CreateValidation(constraint, addressList);
sheet.AddValidationData(validation);
// this line caused XmlValueOutOfRangeException , see Bugzilla 3965
lst = sheet.GetDataValidations();
Assert.AreEqual(1, lst.Count);
}
finally
{
wb.Close();
}
}