public void AddDateRangetoExcelSheet(HSSFWorkbook workbook, ISheet sheet)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue("Date Range");
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
var value = string.Format("Start Date: {0}", StartDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
value = string.Format("End Date: {0}", EndDate.ToString("MM-dd-yyyy"));
cell.SetCellValue(value);
}
public NpoiWorksheet(HSSFWorkbook book, ISheet sheet) {
sheet.ForceFormulaRecalculation = true;
Book = book;
Sheet = sheet;
Index = book.GetSheetIndex(sheet);
Name = book.GetSheetName(Index);
}
//シートの読み込み
static StringGrid ReadSheet(ISheet sheet, string path)
{
int lastRowNum = sheet.LastRowNum;
StringGrid grid = new StringGrid(path + ":" + sheet.SheetName, CsvType.Tsv);
for (int rowIndex = sheet.FirstRowNum; rowIndex <= lastRowNum; ++rowIndex)
{
IRow row = sheet.GetRow(rowIndex);
List<string> stringList = new List<string>();
if (row != null)
{
foreach (var cell in row.Cells)
{
for (int i = stringList.Count; i < cell.ColumnIndex; ++i)
{
stringList.Add("");
}
stringList.Add(cell.ToString());
}
}
grid.AddRow(stringList);
}
grid.ParseHeader();
return grid;
}
private ChambrePA GetChambrePMZ(ISheet sheet)
{
var geofibrePMZ = sheet.GetRow(10).GetCell(11).ToString();
var identifiantPMZ = sheet.GetRow(10).GetCell(13).ToString();
return new ChambrePA(geofibrePMZ, identifiantPMZ);
}
private ChambrePointAboutement GetChambrePA(ISheet sheet)
{
var geofibrePA = sheet.GetRow(11).GetCell(11).ToString();
var chambrePA = sheet.GetRow(13).GetCell(10).ToString();
return new ChambrePointAboutement(geofibrePA, chambrePA);
}
private static void OutputDateRow(ISheet sheet, IEnumerable<string> distinctDates, int rowIndex)
{
var colSpan = 3;
var startCol = 1;
var dateRow = sheet.CreateRow(rowIndex);
var headerRow = sheet.CreateRow(rowIndex + 1);
dateRow.CreateCell(0).SetCellValue("");
headerRow.CreateCell(0).SetCellValue("");
for (var i = 0; i < distinctDates.Count(); i++)
{
var cell = dateRow.CreateCell(startCol);
cell.SetCellValue(distinctDates.ElementAt(i));
sheet.AddMergedRegion(new CellRangeAddress(rowIndex, rowIndex, startCol, colSpan * (i + 1)));
headerRow.CreateCell(startCol).SetCellValue(ValueHeader);
headerRow.CreateCell(startCol + 1).SetCellValue(PrefixHeader);
headerRow.CreateCell(startCol + 2).SetCellValue(DlHeader);
startCol = startCol + colSpan;
}
}
static void WriteHeaderRow(IWorkbook wb, ISheet sheet)
{
sheet.SetColumnWidth(0, 6000);
sheet.SetColumnWidth(1, 6000);
sheet.SetColumnWidth(2, 3600);
sheet.SetColumnWidth(3, 3600);
sheet.SetColumnWidth(4, 2400);
sheet.SetColumnWidth(5, 2400);
sheet.SetColumnWidth(6, 2400);
sheet.SetColumnWidth(7, 2400);
sheet.SetColumnWidth(8, 2400);
IRow row = sheet.CreateRow(0);
ICellStyle style = wb.CreateCellStyle();
IFont font = wb.CreateFont();
font.Boldweight = (short)FontBoldWeight.BOLD;
style.SetFont(font);
WriteHeaderCell(row, 0, "Raw Long Bits A", style);
WriteHeaderCell(row, 1, "Raw Long Bits B", style);
WriteHeaderCell(row, 2, "Value A", style);
WriteHeaderCell(row, 3, "Value B", style);
WriteHeaderCell(row, 4, "Exp Cmp", style);
WriteHeaderCell(row, 5, "LT", style);
WriteHeaderCell(row, 6, "EQ", style);
WriteHeaderCell(row, 7, "GT", style);
WriteHeaderCell(row, 8, "Check", style);
}
public PositionnementEtudeCreator(ISheet sheet, XSSFWorkbook workbook)
{
_sheet = sheet;
_workbook = workbook;
_cadreCreator = new Cadre(workbook, sheet);
_lineCreator = new DataLineStyle(workbook, sheet);
}
public CadrePA(ISheet sheet, XSSFWorkbook workbook)
{
_sheet = sheet;
_workbook = workbook;
_lineCreator = new DataLineStyle(workbook, sheet);
_cadreCreator = new Cadre(workbook, sheet);
}
static void CreateChart(IDrawing drawing, ISheet sheet, IClientAnchor anchor, string serie1, string serie2)
{
IChart chart = drawing.CreateChart(anchor);
IChartLegend legend = chart.GetOrCreateLegend();
legend.Position = LegendPosition.TopRight;
ILineChartData<double, double> data = chart.ChartDataFactory.CreateLineChartData<double, double>();
// Use a category axis for the bottom axis.
IChartAxis bottomAxis = chart.ChartAxisFactory.CreateCategoryAxis(AxisPosition.Bottom);
IValueAxis leftAxis = chart.ChartAxisFactory.CreateValueAxis(AxisPosition.Left);
leftAxis.Crosses = AxisCrosses.AutoZero;
IChartDataSource<double> xs = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(0, 0, 0, NUM_OF_COLUMNS - 1));
IChartDataSource<double> ys1 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(1, 1, 0, NUM_OF_COLUMNS - 1));
IChartDataSource<double> ys2 = DataSources.FromNumericCellRange(sheet, new CellRangeAddress(2, 2, 0, NUM_OF_COLUMNS - 1));
var s1 = data.AddSeries(xs, ys1);
s1.SetTitle(serie1);
var s2 = data.AddSeries(xs, ys2);
s2.SetTitle(serie2);
chart.Plot(data, bottomAxis, leftAxis);
}
public static double GetCellNumic(ISheet sheet, char x, int y)
{
double _r = double.MinValue;
ICell cell = sheet.GetRow(y - 1).GetCell(GetCellIntFromChar(x) - 1);
if (cell != null)
switch (cell.CellType)
{
case CellType.String:
{
_r = double.Parse(cell.StringCellValue.Trim());
}
break;
case CellType.Numeric:
{
_r = cell.NumericCellValue;
}
break;
default:
{
}
break;
}
return _r;
}
internal WorkSheet(IWorkbook bookHandler, ISheet sheetHandler)
{
this.bookHandler = bookHandler;
this.sheetHandler = sheetHandler;
this.Cells = new CellCollection() { Sheet = this };
}
private static void SheetImport(Tk5ListMetaData metaInfos, DataTable dataTable, ISheet sheet,
ResultHolder resultHolder)
{
if (sheet != null)
{
// resultHolder.SheetName = metaInfos.Table.TableDesc;
Dictionary<string, Tk5FieldInfoEx> dicOfInfo = new Dictionary<string, Tk5FieldInfoEx>();
foreach (Tk5FieldInfoEx info in metaInfos.Table.TableList)
{
dicOfInfo.Add(info.DisplayName, info);
}
IRow headerRow = sheet.GetRow(0);
for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
DataRow dataRow = dataTable.NewRow();
for (int j = row.FirstCellNum; j < row.LastCellNum; j++)
{
string columnName = headerRow.GetCell(j).ToString();
string strValue = row.GetCell(j).ToString();
ImportResult imResult = TablePadding(dataRow, columnName, dicOfInfo, strValue, i);
if (imResult != null)
{
resultHolder.Add(imResult);
}
}
dataTable.Rows.Add(dataRow);
}
}
}
// 由微信类生成Excel
private static void ObjectExport(ExportExcelPageMaker configData,
Dictionary<string, ICellStyle> contentStyles, ISheet sheet, ObjectListModel objectLM)
{
int rowIndex = 1;
foreach (ObjectContainer container in objectLM.List)
{
IRow dataRow = sheet.CreateRow(rowIndex);
int columnIndex = 0;
object receiver = container.MainObject;
string strValue = string.Empty;
foreach (Tk5FieldInfoEx fieldInfo in configData.fMetaData.Table.TableList)
{
ICell cell = dataRow.CreateCell(columnIndex);
if (fieldInfo != null)
{
if (fieldInfo.Decoder == null || fieldInfo.Decoder.Type == DecoderType.None)
{
strValue = receiver.MemberValue(fieldInfo.NickName).ConvertToString();
CellPadding(strValue, cell, fieldInfo);
}
else
strValue = container.Decoder.GetNameString(fieldInfo.NickName);
cell.CellStyle = contentStyles[fieldInfo.NickName];
}
columnIndex++;
}
rowIndex++;
}
}
static void CreateHeaderRow(ISheet _sheet)
{
IRow header = _sheet.CreateRow(1);
int cellIdx = 1;
// name
ICell nameCell = header.CreateCell(cellIdx++);
nameCell.SetCellValue("name");
// hp
ICell hpCell = header.CreateCell(cellIdx++);
hpCell.SetCellValue("hp");
// x
ICell posxCell = header.CreateCell(cellIdx++);
posxCell.SetCellValue("x");
// y
ICell posyCell = header.CreateCell(cellIdx++);
posyCell.SetCellValue("y");
// z
ICell poszCell = header.CreateCell(cellIdx++);
poszCell.SetCellValue("z");
}
static void AddToExcel(ISheet _sheet, CharaData _data, int _rowIdx)
{
IRow row = _sheet.CreateRow(_rowIdx);
int cellIdx = 1;
// name
ICell nameCell = row.CreateCell(cellIdx++);
nameCell.SetCellValue(_data.m_name);
// hp
ICell hpCell = row.CreateCell(cellIdx++);
hpCell.SetCellValue(_data.m_hp);
// x
ICell posxCell = row.CreateCell(cellIdx++);
posxCell.SetCellValue(_data.m_position.x);
// y
ICell posyCell = row.CreateCell(cellIdx++);
posyCell.SetCellValue(_data.m_position.y);
// z
ICell poszCell = row.CreateCell(cellIdx++);
poszCell.SetCellValue(_data.m_position.z);
}
public bool Init()
{
if (!File.Exists(m_StrategyName))
{
m_StrategyWorkBook = new XSSFWorkbook();
m_StrategySheet = (ISheet)m_StrategyWorkBook.CreateSheet("Sheet1");
IRow Row = m_StrategySheet.CreateRow(0);
Row.CreateCell(0).SetCellValue("-500");
Row.CreateCell(1).SetCellValue("-450");
Row.CreateCell(2).SetCellValue("-400");
Row.CreateCell(3).SetCellValue("-350");
Row.CreateCell(4).SetCellValue("-300");
Row.CreateCell(5).SetCellValue("-250");
Row.CreateCell(6).SetCellValue("-200");
Row.CreateCell(7).SetCellValue("-150");
Row.CreateCell(8).SetCellValue("-100");
Row.CreateCell(9).SetCellValue("-50");
Row.CreateCell(10).SetCellValue("0");
Row.CreateCell(11).SetCellValue("50");
Row.CreateCell(12).SetCellValue("100");
Row.CreateCell(13).SetCellValue("150");
Row.CreateCell(14).SetCellValue("200");
Row.CreateCell(15).SetCellValue("250");
Row.CreateCell(16).SetCellValue("300");
Row.CreateCell(17).SetCellValue("350");
Row.CreateCell(18).SetCellValue("400");
Row.CreateCell(19).SetCellValue("450");
Row.CreateCell(20).SetCellValue("500");
return true;
}
return false;
}
/**
* Highlight cells based on their values
*/
static void SameCell(ISheet sheet)
{
sheet.CreateRow(0).CreateCell(0).SetCellValue(84);
sheet.CreateRow(1).CreateCell(0).SetCellValue(74);
sheet.CreateRow(2).CreateCell(0).SetCellValue(50);
sheet.CreateRow(3).CreateCell(0).SetCellValue(51);
sheet.CreateRow(4).CreateCell(0).SetCellValue(49);
sheet.CreateRow(5).CreateCell(0).SetCellValue(41);
ISheetConditionalFormatting sheetCF = sheet.SheetConditionalFormatting;
// Condition 1: Cell Value Is greater than 70 (Blue Fill)
IConditionalFormattingRule rule1 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.GreaterThan, "70");
IPatternFormatting fill1 = rule1.CreatePatternFormatting();
fill1.FillBackgroundColor = (IndexedColors.Blue.Index);
fill1.FillPattern = (short)FillPattern.SolidForeground;
// Condition 2: Cell Value Is less than 50 (Green Fill)
IConditionalFormattingRule rule2 = sheetCF.CreateConditionalFormattingRule(ComparisonOperator.LessThan, "50");
IPatternFormatting fill2 = rule2.CreatePatternFormatting();
fill2.FillBackgroundColor = (IndexedColors.Green.Index);
fill2.FillPattern= (short)FillPattern.SolidForeground;
CellRangeAddress[] regions = {
CellRangeAddress.ValueOf("A1:A6")
};
sheetCF.AddConditionalFormatting(regions, rule1, rule2);
sheet.GetRow(0).CreateCell(2).SetCellValue("<== Condition 1: Cell Value is greater than 70 (Blue Fill)");
sheet.GetRow(4).CreateCell(2).SetCellValue("<== Condition 2: Cell Value is less than 50 (Green Fill)");
}
/// <summary>
/// 加边框
/// </summary>
/// <param Name="rowindex">1开始</param>
/// <param Name="cellIndex">1开始</param>
public void AddBorder( ISheet sheet, HSSFWorkbook workbook)
{
ICellStyle styel = workbook.CreateCellStyle();
styel.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; // ------------------
IFont font1 = workbook.CreateFont();
font1.FontHeightInPoints = 11;
font1.Boldweight = 600;
font1.FontName = "宋体";
styel.SetFont(font1);
for (int rowindex=1;rowindex<sheet.LastRowNum+1;rowindex++)
{
for (int cellIndex =0; cellIndex < dcs.Count;cellIndex++ )
{
sheet.GetRow(rowindex).RowStyle = styel;
ICell cell = sheet.GetRow(rowindex ).GetCell(cellIndex );
HSSFCellStyle Style = workbook.CreateCellStyle() as HSSFCellStyle;
Style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center;
Style.VerticalAlignment = VerticalAlignment.Center;
Style.BorderTop = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderRight = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderLeft = NPOI.SS.UserModel.BorderStyle.Thin;
Style.BorderBottom = NPOI.SS.UserModel.BorderStyle.Thin;
Style.DataFormat = 0;
Style.SetFont(font1);
cell.CellStyle = Style;
}
}
}
public void AddListToExcelSheet(HSSFWorkbook workbook, ISheet sheet, string Title, Dictionary<string, bool> list)
{
//Create a Title row
var titleFont = workbook.CreateFont();
titleFont.Boldweight = (short)NPOI.SS.UserModel.FontBoldWeight.Bold;
titleFont.FontHeightInPoints = 11;
titleFont.Underline = NPOI.SS.UserModel.FontUnderlineType.Single;
var titleStyle = workbook.CreateCellStyle();
titleStyle.SetFont(titleFont);
var row = sheet.CreateRow(rowCount++);
row = sheet.CreateRow(rowCount++);
var cell = row.CreateCell(0);
cell.CellStyle = titleStyle;
cell.SetCellValue(Title);
foreach (var org in list)
{
if (org.Value == true)
{
row = sheet.CreateRow(rowCount++);
cell = row.CreateCell(0);
cell.SetCellValue(org.Key);
}
}
}
private void FillRows(HSSFWorkbook workbook, ISheet workSheet, List<Cell> cells, dynamic rows)
{
var dateStyle = workbook.CreateCellStyle();
var format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-mm-dd");
var currentRow = 1;
if (rows == null)
{
return;
}
foreach (var row in rows)
{
var workRow = workSheet.CreateRow(currentRow);
var currentCol = 0;
var type = row.GetType();
foreach (var cell in cells)
{
var workCell = workRow.CreateCell(currentCol);
SetCellValue(workbook, workCell, type, row, cell);
currentCol++;
}
currentRow++;
}
}
public void WriteData(ISheet sheet, object model, IList<Property> properties)
{
var singleVals = new List<Property>();
var listVals = new List<Property>();
foreach (var p in properties)
{
if (p.IsArray)
{
listVals.Add(p);
}
else
{
singleVals.Add(p);
}
}
foreach (var singleProp in singleVals)
{
WriteSingle(sheet, singleProp.RowIndex, singleProp.ColumnIndex, GetPropertyVal(model, singleProp.Name));
}
foreach (var listProp in listVals)
{
WriteArray(sheet, listProp.RowIndex, listProp.ColumnIndex, GetPropertyVals(model, listProp.Name));
}
}
private void LoadContacts(ISheet sheet, int rowStartIndex, List<Contact> contacts)
{
ICell organizationCell = sheet.GetRow(rowStartIndex).GetCell(0);
ICell assignmentCell = sheet.GetRow(rowStartIndex).GetCell(1);
ICell nameCell = sheet.GetRow(rowStartIndex).GetCell(2);
ICell emailCell = sheet.GetRow(rowStartIndex).GetCell(3);
ICell phoneCell = sheet.GetRow(rowStartIndex).GetCell(4);
ICell skypeCell = sheet.GetRow(rowStartIndex).GetCell(5);
while (GetCellValue(organizationCell) != null)
{
Contact contact = new Contact();
contact.Organization = GetCellValue(organizationCell);
while (GetCellValue(assignmentCell) != null)
{
ContactAssignment contactAssignment= new ContactAssignment();
contactAssignment.Assignment = GetCellValue(assignmentCell);
while (GetCellValue(nameCell) != null)
{
ContactItem contactItem = new ContactItem();
contactItem.Name = GetCellValue(nameCell);
contactItem.Email = GetCellValue(emailCell);
contactItem.Phone = GetCellValue(phoneCell);
contactItem.Skype = GetCellValue(skypeCell);
contactAssignment.ContactList.Add(contactItem);
rowStartIndex++;
IRow rowObj = sheet.GetRow(rowStartIndex);
if (rowObj == null)
{
nameCell = null;
break;
}
organizationCell = sheet.GetRow(rowStartIndex).GetCell(0);
assignmentCell = sheet.GetRow(rowStartIndex).GetCell(1);
nameCell = sheet.GetRow(rowStartIndex).GetCell(2);
emailCell = sheet.GetRow(rowStartIndex).GetCell(3);
phoneCell = sheet.GetRow(rowStartIndex).GetCell(4);
skypeCell = sheet.GetRow(rowStartIndex).GetCell(5);
if (GetCellValue(assignmentCell) != null)
{
break;
}
}
contact.ContactAssignment.Add(contactAssignment);
if (GetCellValue(organizationCell) != null)
{
break;
}
}
contacts.Add(contact);
}
}
internal static void columnMerge(ISheet sheet, ICellStyle cellStyle, int row, int startColumn, int endColumn)
{
sheet.AddMergedRegion(new NPOI.SS.Util.CellRangeAddress(row, row, startColumn, endColumn));
cellStyle.BorderBottom = BorderStyle.Thin;
cellStyle.BorderTop = BorderStyle.Thin;
cellStyle.BorderLeft = BorderStyle.Thin;
cellStyle.BorderRight = BorderStyle.Thin;
}
public void SetSheet(ISheet sheet)
{
Controls.Clear();
Controls.Add(sheet as UserControl);
(Controls[0] as ISheet).ToggleControls();
if (!Visible) Show();
else Refresh();
}
/**
*
* Param templateFilePath 模版文件路径
*
*/
protected virtual void init(String templateFilePath)
{
FileStream file = new FileStream(templateFilePath, FileMode.Open, FileAccess.Read);
this.workbook = new HSSFWorkbook(file);
this.sheet = workbook.GetSheetAt(0);
this.sheet.ForceFormulaRecalculation = true;
}
public MainMenuController(MainMenuModel model, ISheet menuSheet)
: base(model, menuSheet)
{
model.ReturnItem.RequestSheet(SheetRequester, Sheets.ReturnItemLanding);
model.RemoveItemRequest.RequestSheet(SheetRequester, Sheets.RemoveItemLanding);
model.InventoryRequest.RequestSheet(SheetRequester, Sheets.InventoryLanding);
model.ConfigurationRequest.RequestSheet(SheetRequester, Sheets.ConfigurationLanding);
}
// convenient access to namespace
//private static ErrorEval EE = null;
private static void CreateDataRow(ISheet sheet, int rowIndex, params double[] vals)
{
IRow row = sheet.CreateRow(rowIndex);
for (int i = 0; i < vals.Length; i++)
{
row.CreateCell(i).SetCellValue(vals[i]);
}
}
public RestockHistoryController(RestockHistoryModel model,
ISheet sheet,
IFetchRestockHistory fetchHistory)
: base(model, sheet)
{
_model = model;
_fetchHistory = fetchHistory;
}
private void CloseAndCleanUp()
{
mSheet = null;
mWorkbook = null;
GC.Collect();
GC.WaitForPendingFinalizers();
}
public void Init(object data)
{
workbook = new HSSFWorkbook();
sheet = workbook.CreateSheet("sheet1");
sheet.DefaultRowHeight = 200 * 20;
}
private void generate_my_data()
{
ISheet sheet0 = hssfworkbook.CreateSheet("Ruch spraw");//*
DataView view = (DataView)dane_do_tabeli_1.Select(DataSourceSelectArguments.Empty);
DataTable table = view.ToTable();
DataTable dT = (DataTable)Session["header_01"];
table.TableName = "Załatwienia";
table.Columns.Remove("id_");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
var crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 0, 0);
IRow row0 = sheet0.CreateRow(0);
#region tabela1
foreach (DataRow dR in dr.getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(1);
foreach (DataRow dR in dr.getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row0 = sheet0.CreateRow(2);
foreach (DataRow dR in dr.getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row0.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
int rol = 3;
foreach (DataRow rowik in table.Rows)
{
row0 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row0.CreateCell(i).SetCellValue(ji);
row0.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row0.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
#endregion tabela1
#region drugi arkusz
// druga tabela
view = (DataView)dane_do_tabeli_2.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.TableName = "Załatwienia";
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
//
//robienie
int ro = 2;
//-----------------
IDataFormat format = hssfworkbook.CreateDataFormat();
ISheet sheet1 = hssfworkbook.CreateSheet("Załatwienia");
IRow row2 = sheet1.CreateRow(0);
dT.Clear();
dT = (DataTable)Session["header_02"];
//===========
foreach (DataRow dR in dr.getData(dT, "Column1=3"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1, exc2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(1);
foreach (DataRow dR in dr.getData(dT, "Column1=2"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 1, exc2 + 1, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
row2 = sheet0.CreateRow(2);
foreach (DataRow dR in dr.getData(dT, "Column1=1"))
{
string text = dR[1].ToString().Trim();
int exc1 = int.Parse(dR[4].ToString().Trim());
int exc2 = int.Parse(dR[5].ToString().Trim());
int exc3 = int.Parse(dR[6].ToString().Trim());
int exc4 = int.Parse(dR[7].ToString().Trim());
row2.CreateCell(exc3).SetCellValue(text);
if ((exc1 != exc2) || (exc3 != exc4))
{
crs = new NPOI.SS.Util.CellRangeAddress(exc1 + 2, exc2 + 2, exc3, exc4);
sheet0.AddMergedRegion(crs);
}
}
rol = 3;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet0.CreateRow(rol);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
rol++;
}// end foreach
foreach (DataRow rowik in table.Rows)
{
row2 = sheet1.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
#endregion drugi arkusz
// trzeci sheet
view = (DataView)tabela_3.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
sheet1.AutoSizeColumn(0, true);
sheet1.AutoSizeColumn(1, true);
ISheet sheet2 = hssfworkbook.CreateSheet("Wyznaczenia");
row2 = sheet2.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet2.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet2.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Wyznaczenia");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet2.AddMergedRegion(crs);
row2 = sheet2.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
row2.CreateCell(13).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet2.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
// czwarty sheet
view = (DataView)tabela_4.Select(DataSourceSelectArguments.Empty);
table = view.ToTable();
table.Columns.Remove("ident");
table.Columns.Remove("sesja");
table.Columns.Remove("id_sedziego");
table.Columns.Remove("id_tabeli");
//table.Columns.Remove("id_dzialu");
table.Columns.Remove("d_09");
table.Columns.Remove("d_10");
table.Columns.Remove("d_11");
table.Columns.Remove("d_12");
table.Columns.Remove("d_13");
table.Columns.Remove("d_14");
table.Columns.Remove("d_15");
table.Columns.Remove("d_16");
table.Columns.Remove("d_17");
table.Columns.Remove("d_18");
table.Columns.Remove("d_19");
table.Columns.Remove("d_20");
table.Columns.Remove("d_21");
table.Columns.Remove("d_22");
ISheet sheet3 = hssfworkbook.CreateSheet("Stan referatów sędziów");
row2 = sheet3.CreateRow(0);
row2.CreateCell(0).SetCellValue("L.p.");
row2.CreateCell(1).SetCellValue("Nazwisko");
row2.CreateCell(2).SetCellValue("Imię");
row2.CreateCell(3).SetCellValue("Funkcja");
row2.CreateCell(4).SetCellValue("Stanowisko");
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 0, 0);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 1, 1);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 2, 2);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 3, 3);
sheet3.AddMergedRegion(crs);
crs = new NPOI.SS.Util.CellRangeAddress(0, 1, 4, 4);
sheet3.AddMergedRegion(crs);
row2.CreateCell(5).SetCellValue("Pozostało w referatach spraw kategorii");
crs = new NPOI.SS.Util.CellRangeAddress(0, 0, 5, 12);
sheet3.AddMergedRegion(crs);
row2 = sheet3.CreateRow(1);
row2.CreateCell(5).SetCellValue("GU bez ''of''");
row2.CreateCell(6).SetCellValue("C-GC");
row2.CreateCell(7).SetCellValue("GU ''of''");
row2.CreateCell(8).SetCellValue("GU Razem");
row2.CreateCell(9).SetCellValue("GUp bez '''of'");
row2.CreateCell(10).SetCellValue("GUp ''of''");
row2.CreateCell(11).SetCellValue("WSC");
row2.CreateCell(12).SetCellValue("Razem");
// row2.CreateCell(12).SetCellValue("Odroczenia liczba spraw odroczonych");
ro = 2;
foreach (DataRow rowik in table.Rows)
{
row2 = sheet3.CreateRow(ro);
for (int i = 0; i < rowik.ItemArray.Length; i++)
{
try
{
int ji = int.Parse(rowik[i].ToString().Trim());
ICellStyle cellStyle = hssfworkbook.CreateCellStyle();
cellStyle.DataFormat = HSSFDataFormat.GetBuiltinFormat("0");
row2.CreateCell(i).SetCellValue(ji);
row2.Cells[i].CellStyle = cellStyle;
}
catch (Exception)
{
row2.CreateCell(i).SetCellValue(rowik[i].ToString().Trim());
}
}
ro++;
}// end foreach
}
/// <summary>
/// 每日个签送钱客人情况表
/// </summary>
/// <param name="visaList"></param>
/// <param name="visaInfoList"></param>
/// <returns></returns>
public static bool GetEverydayExcel(List <Model.Visa> visaList, List <List <VisaInfo> > visaInfoList)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("每日送签客人情况");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("");
row.CreateCell(1).SetCellValue("姓名");
row.CreateCell(2).SetCellValue("签发地");
row.CreateCell(3).SetCellValue("居住地");
row.CreateCell(4).SetCellValue("签证类型");
row.CreateCell(5).SetCellValue("归国时间");
row.CreateCell(6).SetCellValue("关系");
row.CreateCell(7).SetCellValue("");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256);
sheet.SetColumnWidth(1, 10 * 256);
sheet.SetColumnWidth(2, 10 * 256);
sheet.SetColumnWidth(3, 10 * 256);
sheet.SetColumnWidth(4, 10 * 256);
sheet.SetColumnWidth(5, 13 * 256);
sheet.SetColumnWidth(6, 10 * 256);
sheet.SetColumnWidth(7, 35 * 256);
//3.插入行和单元格
int rowNum = 0;
for (int i = 0; i != visaList.Count; ++i)
{
for (int j = 0; j < visaInfoList[i].Count; j++)
{
++rowNum;
row = sheet.CreateRow(rowNum);
row.CreateCell(0).SetCellValue(rowNum);
row.CreateCell(1).SetCellValue(visaInfoList[i][j].Name);
row.CreateCell(2).SetCellValue(visaInfoList[i][j].IssuePlace);
string residence = visaInfoList[i][j].Residence;
if (visaInfoList[i][j].Residence.Contains(" "))
{
residence = visaInfoList[i][j].Residence.Split(' ')[0];
if (residence.EndsWith("省") || residence.EndsWith("市"))
{
residence = residence.Substring(0, residence.Length - 1);
}
}
row.CreateCell(3).SetCellValue(residence);
row.CreateCell(4).SetCellValue(visaList[i].DepartureType);
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(visaInfoList[i][j].ReturnTime)); //归国时间先不设置
row.CreateCell(6).SetCellValue(visaList[i].Remark);
row.CreateCell(7).SetCellValue(visaInfoList[i][j].Identification);
}
//创建单元格
//设置行高
//row.HeightInPoints = 50;
//设置值
}
HSSFFont font = (HSSFFont)wkbook.CreateFont();
font.FontName = "宋体";
font.FontHeightInPoints = 11;
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.SetFont(font);
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
//4.2合并单元格
int dp = 1;
for (int i = 0; i != visaList.Count; ++i)
{
sheet.AddMergedRegion(new CellRangeAddress(dp, dp + visaInfoList[i].Count - 1, 6, 6));
//单独处理合并区域的单元格格式
ICellStyle mergeStyle = wkbook.CreateCellStyle();
mergeStyle.SetFont(font);
mergeStyle.VerticalAlignment = VerticalAlignment.Center;
mergeStyle.Alignment = HorizontalAlignment.Center;
mergeStyle.BorderTop = BorderStyle.Thin;
mergeStyle.BorderBottom = BorderStyle.Thin;
mergeStyle.BorderLeft = BorderStyle.Thin;
mergeStyle.BorderRight = BorderStyle.Thin;
sheet.GetRow(dp).Cells[6].CellStyle = mergeStyle;
dp += visaInfoList[i].Count;
}
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg("每日送签客人情况表.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
/// <summary>
/// 导出数据到excel文件
/// </summary>
/// <param name="titleList">特殊处理的数据的标题集合</param>
/// <returns></returns>
public Stream CommonToExcel(List <string> titleList)
{
int rowIndex = 0;
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow row = sheet.CreateRow(rowIndex);
row.Height = 200 * 3;
//表头样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;//居中对齐
//表头单元格背景色
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//表头单元格边框
style.BorderTop = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.VerticalAlignment = VerticalAlignment.Center;
//表头字体设置
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12; //字号
font.Boldweight = 600; //加粗
//font.Color = HSSFColor.WHITE.index;//颜色
style.SetFont(font);
//数据样式
ICellStyle datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Left;//左对齐
//数据单元格的边框
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.TopBorderColor = HSSFColor.Black.Index;
datastyle.RightBorderColor = HSSFColor.Black.Index;
datastyle.BottomBorderColor = HSSFColor.Black.Index;
datastyle.LeftBorderColor = HSSFColor.Black.Index;
//数据的字体
IFont datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
datastyle.SetFont(datafont);
//设置列宽
for (int i = 0; i < columsWidth.Length; i++)
{
//sheet.SetColumnWidth(i, columsWidth[i] * 256);
sheet.SetColumnWidth(i, columsWidth[i] * 58);
}
sheet.DisplayGridlines = false;
try {
//表头数据
for (int i = 0; i < titles.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(titles[i]);
cell.CellStyle = style;
}
for (int k = 0; k < data.Count; k++)
{
row = sheet.CreateRow(k + 1);
row.Height = 200 * 2;
T t = data[k];
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
for (int j = 0; j < colums.Length; j++)
{
var temp = propertys.ToList().Where(p => p.Name == colums[j]).Single();
ICell cell = row.CreateCell(j);
var value = temp.GetValue(t, null);
if (value == null)
{
value = "";
}
else
{
if (value.GetType() == Type.GetType("System.DateTime"))
{
if (titleList != null && titleList.Contains(colums[j]))
{
value = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd HH:mm");
}
else
{
value = DateTime.Parse(value.ToString()).ToString("yyyy-MM-dd");
}
}
}
cell.SetCellValue(value.ToString());
cell.CellStyle = datastyle;
}
}
} catch (Exception ex) {
} finally {
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
}
return(ms);
}
public static int DataTableToExcel1(DataTable dt, string fileName, string sheetName, bool isColumnWritten)
{
IWorkbook workbook = null;
FileStream fileStream = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0)
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls") > 0)
{
workbook = new HSSFWorkbook();
}
int result;
if (workbook != null)
{
ISheet sheet = workbook.CreateSheet(sheetName);
int num;
if (isColumnWritten)
{
IRow row = sheet.CreateRow(0);
for (int i = 0; i < dt.Columns.Count; i++)
{
row.CreateCell(i).SetCellValue(dt.Columns[i].ColumnName);
}
num = 1;
}
else
{
num = 0;
}
for (int j = 0; j < dt.Rows.Count; j++)
{
IRow row = sheet.CreateRow(num);
for (int i = 0; i < dt.Columns.Count; i++)
{
if ((dt.Columns[i].ColumnName.Contains("数") || dt.Columns[i].ColumnName.Contains("价") || dt.Columns[i].ColumnName.Contains("量") || dt.Columns[i].ColumnName.Contains("面积") || dt.Columns[i].ColumnName.Contains("长") || dt.Columns[i].ColumnName.Contains("宽") || dt.Columns[i].ColumnName.Contains("宽") || dt.Columns[i].ColumnName.Contains("重") || dt.Columns[i].ColumnName.Contains("度")) && dt.Rows[j][i] != DBNull.Value)
{
try
{
row.CreateCell(i).SetCellValue(Convert.ToDouble(dt.Rows[j][i]));
}
catch
{
row.CreateCell(i).SetCellValue(dt.Rows[j][i].ToString());
}
}
else
{
row.CreateCell(i).SetCellValue(dt.Rows[j][i].ToString());
}
}
num++;
}
workbook.Write(fileStream);
fileStream.Close();
workbook.Close();
result = num;
}
else
{
result = -1;
}
return(result);
}
/// <summary>
/// 将excel中的数据导入到DataTable中
/// </summary>
/// <param name="sheetName">excel工作薄sheet的名称</param>
/// <param name="isFirstRowColumn">第一行是否是DataTable的列名</param>
/// <returns>返回的DataTable</returns>
public DataTable ExcelToDataTable(string sheetName, bool isFirstRowColumn, int coulmnNum)
{
ISheet sheet = null;
DataTable data = new DataTable();
int startRow = coulmnNum;
try
{
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.GetSheet(sheetName);
if (sheet == null) //如果没有找到指定的sheetName对应的sheet，则尝试获取第一个sheet
{
sheet = workbook.GetSheetAt(0);
}
}
else
{
sheet = workbook.GetSheetAt(0);
}
if (sheet != null)
{
//row 默认从0开始，此处-1同Excel表格数据
IRow firstRow = sheet.GetRow(coulmnNum - 1);
int cellCount = firstRow.LastCellNum; //一行最后一个cell的编号 即总的列数
if (isFirstRowColumn)
{
for (int i = firstRow.FirstCellNum; i < cellCount; ++i)
{
ICell cell = firstRow.GetCell(i);
if (cell != null)
{
string cellValue = cell.StringCellValue;
if (cellValue != null)
{
//默认加入列号
//取消列号
//DataColumn column = new DataColumn(cellValue + i.ToString());
DataColumn column = new DataColumn(cellValue);
data.Columns.Add(column);
}
}
}
//默认-1,此处不需要+1，备注掉
// startRow+=1;
}
else
{
startRow = sheet.FirstRowNum;
}
//最后一列的标号
int rowCount = sheet.LastRowNum;
for (int i = startRow; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue; //没有数据的行默认是null
}
DataRow dataRow = data.NewRow();
//
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
if (row.GetCell(j) != null) //同理，没有数据的单元格都默认是null
{
dataRow[j] = row.GetCell(j).ToString();
}
}
data.Rows.Add(dataRow);
}
}
return(data);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(null);
}
}
public static int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
string path = AppDomain.CurrentDomain.BaseDirectory;
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
IWorkbook workbook = null;
FileStream fs = null;
// bool disposed;
SaveFileDialog sfd = new SaveFileDialog();
sfd.Filter = "xlsx|*.xls|xlsx|*.xlsx";
sfd.Title = "Excel文件导出";
string fileName = path + DateTime.Now.ToShortDateString().Replace("/","-")+ ".xlsx";
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
workbook = new XSSFWorkbook();
else if (fileName.IndexOf(".xls") > 0) // 2003版本
workbook = new HSSFWorkbook();
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
ICellStyle style = workbook.CreateCellStyle();
style.FillPattern = FillPattern.SolidForeground;
}
else
{
return -1;
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
workbook.Close();
fs.Close();
System.Diagnostics.Process[] Proc = System.Diagnostics.Process.GetProcessesByName("");
return 0;
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return -1;
}
}
/// <summary>
/// List导出到Excel的MemoryStream
/// </summary>
/// <param name="list">数据源</param>
/// <param name="sHeaderText">表头文本</param>
/// <param name="columns">需要导出的属性</param>
private MemoryStream CreateExportMemoryStream(List <T> list, string sHeaderText, string[] columns)
{
HSSFWorkbook workbook = new HSSFWorkbook();
ISheet sheet = workbook.CreateSheet();
Type type = typeof(T);
PropertyInfo[] properties = ReflectionHelper.GetProperties(type, columns);
ICellStyle dateStyle = workbook.CreateCellStyle();
IDataFormat format = workbook.CreateDataFormat();
dateStyle.DataFormat = format.GetFormat("yyyy-MM-dd");
#region 取得每列的列宽（最大宽度）
int[] arrColWidth = new int[properties.Length];
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
//GBK对应的code page是CP936
arrColWidth[columnIndex] = properties[columnIndex].Name.Length;
}
#endregion
for (int rowIndex = 0; rowIndex < list.Count; rowIndex++)
{
#region 新建表，填充表头，填充列头，样式
if (rowIndex == 65535 || rowIndex == 0)
{
if (rowIndex != 0)
{
sheet = workbook.CreateSheet();
}
#region 表头及样式
{
IRow headerRow = sheet.CreateRow(0);
headerRow.HeightInPoints = 25;
headerRow.CreateCell(0).SetCellValue(sHeaderText);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 20;
font.Boldweight = 700;
headStyle.SetFont(font);
headerRow.GetCell(0).CellStyle = headStyle;
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, properties.Length - 1));
}
#endregion
#region 列头及样式
{
IRow headerRow = sheet.CreateRow(1);
ICellStyle headStyle = workbook.CreateCellStyle();
headStyle.Alignment = HorizontalAlignment.Center;
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 10;
font.Boldweight = 700;
headStyle.SetFont(font);
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
// 类属性如果有Description就用Description当做列名
DescriptionAttribute customAttribute = (DescriptionAttribute)Attribute.GetCustomAttribute(properties[columnIndex], typeof(DescriptionAttribute));
string description = properties[columnIndex].Name;
if (customAttribute != null)
{
description = customAttribute.Description;
}
headerRow.CreateCell(columnIndex).SetCellValue(description);
headerRow.GetCell(columnIndex).CellStyle = headStyle;
//设置列宽
sheet.SetColumnWidth(columnIndex, (arrColWidth[columnIndex] + 1) * 256);
}
}
#endregion
}
#endregion
#region 填充内容
ICellStyle contentStyle = workbook.CreateCellStyle();
contentStyle.Alignment = HorizontalAlignment.Left;
IRow dataRow = sheet.CreateRow(rowIndex + 2); // 前面2行已被占用
for (int columnIndex = 0; columnIndex < properties.Length; columnIndex++)
{
ICell newCell = dataRow.CreateCell(columnIndex);
newCell.CellStyle = contentStyle;
string drValue = properties[columnIndex].GetValue(list[rowIndex], null).ParseToString();
switch (properties[columnIndex].PropertyType.ToString())
{
case "System.String":
newCell.SetCellValue(drValue);
break;
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
newCell.SetCellValue(drValue.ParseToDateTime());
newCell.CellStyle = dateStyle; //格式化显示
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
newCell.SetCellValue(drValue.ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
case "System.Int16":
case "System.Nullable`1[System.Int16]":
case "System.Int32":
case "System.Nullable`1[System.Int32]":
newCell.SetCellValue(drValue.ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
newCell.SetCellValue(drValue.ParseToString());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
newCell.SetCellValue(drValue.ParseToDouble());
break;
case "System.DBNull":
newCell.SetCellValue(string.Empty);
break;
default:
newCell.SetCellValue(string.Empty);
break;
}
}
#endregion
}
using (MemoryStream ms = new MemoryStream())
{
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
return(ms);
}
}
public List <DateTime> GetTimeList(ISheet sheet, string anchorKey)
{
var datestring = "";
for (int i = 0; i <= sheet.LastRowNum; i++)
{
var cell = sheet.GetRow(i).Cells.Where(x => x.CellType == CellType.String && x.StringCellValue.ToLower().Contains(anchorKey.ToLower())).LastOrDefault();
if (cell != null)
{
datestring = cell.StringCellValue;
break;
}
}
var date = datestring.Split(new string[] { anchorKey }, StringSplitOptions.None).LastOrDefault();
List <DateTime> TimeList = new List <DateTime>();
DateTime startday = new DateTime(1990, 1, 1);
DateTime lastday = new DateTime(1990, 1, 1);
var format = "d/M/yyyy";
var provider = new CultureInfo("fr-FR");
if (date.Contains('-'))
{
var datelist = date.Split('-');
startday = DateTime.ParseExact(datelist.FirstOrDefault().Trim(), format, provider);
lastday = DateTime.ParseExact(datelist.LastOrDefault().Trim(), format, provider);
}
else if ((date.Contains(',') || date.Contains(';')) && !date.Contains('&'))
{
var datelist = date.Split(new char[] { ';', ',' });
lastday = DateTime.ParseExact(datelist.LastOrDefault().Trim(), format, provider);
startday = new DateTime(lastday.Year, lastday.Month, Convert.ToInt32(datelist.FirstOrDefault().Trim()));
}
else
{
List <string> datelist = new List <string>();
if (date.Contains("đến"))
{
datelist = date.Split(new string[] { "đến" }, StringSplitOptions.None).ToList();
var start = Convert.ToDateTime(datelist.FirstOrDefault().ToString(), provider);
var end = Convert.ToDateTime(datelist.Last().ToString(), provider);
while (start <= end)
{
TimeList.Add(start);
start = start.AddDays(1);
}
return(TimeList);
}
else
{
datelist = date.Split(new char[] { ';', ',', '&' }).ToList();
}
if (datelist.Count() == 1)
{
lastday = DateTime.ParseExact(datelist.LastOrDefault().Trim(), format, provider);
startday = DateTime.ParseExact(datelist.FirstOrDefault().Trim(), format, provider);
}
else if (datelist.Count() > 2)
{
lastday = DateTime.ParseExact(datelist.LastOrDefault().Trim(), format, provider);
if (lastday.Month != 1)
{
startday = new DateTime(lastday.Year, lastday.Month - 1, Convert.ToInt32(datelist.FirstOrDefault().Trim()));
}
else
{
startday = new DateTime(lastday.Year - 1, 12, Convert.ToInt32(datelist.FirstOrDefault().Trim()));
}
}
else
{
}
}
if (startday.Year != 1990)
{
while (startday <= lastday)
{
TimeList.Add(startday);
startday = startday.AddDays(1);
}
}
return(TimeList);
}
public async Task <ActionResult> ExportToExcel(MaterialReceiptDetailQueryViewModel model)
{
IList <MaterialReceiptDetail> lst = new List <MaterialReceiptDetail>();
using (MaterialReceiptServiceClient client = new MaterialReceiptServiceClient())
{
await Task.Run(() =>
{
PagingConfig cfg = new PagingConfig()
{
IsPaging = false,
OrderBy = "CreateTime Desc,Key.ReceiptNo,Key.ItemNo",
Where = GetWhereCondition(model)
};
MethodReturnResult <IList <MaterialReceiptDetail> > result = client.GetDetail(ref cfg);
if (result.Code == 0)
{
lst = result.Data;
}
});
}
//创建工作薄。
IWorkbook wb = new HSSFWorkbook();
//设置EXCEL格式
ICellStyle style = wb.CreateCellStyle();
style.FillForegroundColor = 10;
//有边框
style.BorderBottom = BorderStyle.THIN;
style.BorderLeft = BorderStyle.THIN;
style.BorderRight = BorderStyle.THIN;
style.BorderTop = BorderStyle.THIN;
IFont font = wb.CreateFont();
font.Boldweight = 10;
style.SetFont(font);
ICell cell = null;
IRow row = null;
ISheet ws = null;
for (int j = 0; j < lst.Count; j++)
{
if (j % 65535 == 0)
{
ws = wb.CreateSheet();
row = ws.CreateRow(0);
#region //列名
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptViewModel_ReceiptDate); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("物料名称"); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(LSMResources.StringResource.MaterialReceiptDetailViewModel_SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("供应商名称"); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("描述"); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑人"); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue("编辑时间"); //编辑时间
#endregion
font.Boldweight = 5;
}
MaterialReceiptDetail obj = lst[j];
MaterialReceipt mrObj = model.GetMaterialReceipt(obj.Key.ReceiptNo);
Material m = model.GetMaterial(obj.MaterialCode);
Supplier s = model.GetSupplier(obj.SupplierCode);
row = ws.CreateRow(j + 1);
#region //数据
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ReceiptNo); //领料号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : mrObj.OrderNumber); //工单号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(mrObj == null ? string.Empty : string.Format("{0:yyyy-MM-dd}", mrObj.ReceiptDate)); //领料日期
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Key.ItemNo); //项目号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.LineStoreName); //线别仓
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialCode); //物料编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(m == null ? string.Empty : m.Name); //物料名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.MaterialLot); //物料批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Qty); //数量
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierMaterialLot); //供应商批号
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.SupplierCode); //供应商编码
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(s == null ? string.Empty : s.Name); //供应商名称
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Description); //描述
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(obj.Editor); //编辑人
cell = row.CreateCell(row.Cells.Count);
cell.CellStyle = style;
cell.SetCellValue(string.Format("{0:yyyy-MM-dd HH:mm:ss}", obj.EditTime)); //编辑时间
#endregion
}
MemoryStream ms = new MemoryStream();
wb.Write(ms);
ms.Flush();
ms.Position = 0;
return(File(ms, "application/vnd.ms-excel", "MaterialReceiptData.xls"));
}
/// <summary>
/// 复制源行到目标行(忽略合并单元格等信息)
/// </summary>
/// <param name="sourceRow"></param>
/// <param name="targetRowIndex"></param>
/// <returns></returns>
public static IRow CopyTo(this IRow sourceRow, int targetRowIndex)
{
if (targetRowIndex == sourceRow.RowNum)
{
throw new ArgumentException("目标行标不能等于源行标。");
}
ISheet currentSheet = sourceRow.Sheet;
IRow targetRow = currentSheet.GetRow(targetRowIndex);
if (targetRow != null)
{
currentSheet.ShiftRows(targetRowIndex, currentSheet.LastRowNum, 1);
}
else
{
targetRow = currentSheet.CreateRow(targetRowIndex);
}
targetRow.Height = sourceRow.Height;
targetRow.ZeroHeight = sourceRow.ZeroHeight;
#region 制单元格
foreach (var sourceCell in sourceRow.Cells)
{
ICell targetCell = targetRow.GetCell(sourceCell.ColumnIndex);
if (null == targetCell)
{
targetCell = targetRow.CreateCell(sourceCell.ColumnIndex);
}
if (null != sourceCell.CellStyle)
{
targetCell.CellStyle = sourceCell.CellStyle;
}
if (null != sourceCell.CellComment)
{
targetCell.CellComment = sourceCell.CellComment;
}
if (null != sourceCell.Hyperlink)
{
targetCell.Hyperlink = sourceCell.Hyperlink;
}
var cfrs = sourceCell.GetConditionalFormattingRules(); //复制条件样式
if (null != cfrs && cfrs.Length > 0)
{
targetCell.AddConditionalFormattingRules(cfrs);
}
targetCell.SetCellType(sourceCell.CellType);
#region 制值
switch (sourceCell.CellType)
{
case CellType.Numeric:
targetCell.SetCellValue(sourceCell.NumericCellValue);
break;
case CellType.String:
targetCell.SetCellValue(sourceCell.RichStringCellValue);
break;
case CellType.Formula:
targetCell.SetCellFormula(sourceCell.CellFormula);
break;
case CellType.Blank:
targetCell.SetCellValue(sourceCell.StringCellValue);
break;
case CellType.Boolean:
targetCell.SetCellValue(sourceCell.BooleanCellValue);
break;
case CellType.Error:
targetCell.SetCellErrorValue(sourceCell.ErrorCellValue);
break;
}
#endregion
}
#endregion
return(targetRow);
}
private void ExportClass(string fileName, string exportDir)
{
XSSFWorkbook xssfWorkbook;
using (FileStream file = new FileStream(fileName, FileMode.Open, FileAccess.Read, FileShare.ReadWrite))
{
xssfWorkbook = new XSSFWorkbook(file);
}
string protoName = Path.GetFileNameWithoutExtension(fileName);
Log.Info($"{protoName}生成class开始");
string exportPath = Path.Combine(exportDir, $"{protoName}.cs");
using (FileStream txt = new FileStream(exportPath, FileMode.Create))
using (StreamWriter sw = new StreamWriter(txt))
{
StringBuilder sb = new StringBuilder();
ISheet sheet = xssfWorkbook.GetSheetAt(0);
sb.Append("namespace Model\n{\n");
sb.Append("\t[Config(AppType.Client)]\n");
sb.Append($"\tpublic partial class {protoName}Category : ACategory<{protoName}>\n");
sb.Append("\t{}\n\n");
sb.Append($"\tpublic class {protoName}: AConfig\n");
sb.Append("\t{\n");
int cellCount = sheet.GetRow(3).LastCellNum;
for (int i = 2; i < cellCount; i++)
{
string fieldDesc = GetCellString(sheet, 2, i);
if (fieldDesc.StartsWith("#"))
{
continue;
}
// s开头表示这个字段是服务端专用
if (fieldDesc.StartsWith("s") && this.isClient)
{
continue;
}
string fieldName = GetCellString(sheet, 3, i);
if (fieldName == "Id" || fieldName == "_id")
{
continue;
}
string fieldType = GetCellString(sheet, 4, i);
if (fieldType == "" || fieldName == "")
{
continue;
}
sb.Append($"\t\tpublic {fieldType} {fieldName};\n");
}
sb.Append("\t}\n");
sb.Append("}\n");
sw.Write(sb.ToString());
}
}
/// <summary>
/// 将DataTable数据导入到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public HSSFWorkbook DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten, HSSFWorkbook sfworkbook = null)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
if (File.Exists(fileName))
{
fs = File.OpenWrite(fileName);
}
else
{
fs = new FileStream(fileName, FileMode.Create, FileAccess.Write);
}
//if (fileName.IndexOf(".xlsx") > 0) // 2007版本
// workbook = new XSSFWorkbook();
if (fileName.IndexOf(".xls") > 0) // 2003版本
{
if (sfworkbook == null)
{
workbook = new HSSFWorkbook();
}
else
{
workbook = sfworkbook;
}
}
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
workbook.Write(fs); //写入到excel
fs.Close();
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
}
return(workbook);
}
static void CreateDashboardLegendHeader(ISheet dashboard, IFont bold, int rowIndex, string title)
{
var style = dashboard.Workbook.CreateCellStyle();
var row = dashboard.GetRow(rowIndex);
ICell cell;
if (style is XSSFCellStyle)
{
((XSSFCellStyle)style).SetFillForegroundColor(Style.CustomLightBlue);
}
else
{
style.FillForegroundColor = Style.LightBlue;
}
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.SetFont(bold);
cell = row.CreateCell((int)LegendColumn.Fund, CellType.String);
cell.SetCellValue(title);
cell.CellStyle = style;
cell = row.CreateCell((int)LegendColumn.Name, CellType.String);
cell.CellStyle = style;
cell = row.CreateCell((int)LegendColumn.Name + 1, CellType.String);
cell.CellStyle = style;
var region = new CellRangeAddress(rowIndex, rowIndex, (int)LegendColumn.Fund, (int)LegendColumn.Name + 1);
dashboard.AddMergedRegion(region);
style = dashboard.Workbook.CreateCellStyle();
style.FillForegroundColor = Style.LightGrey;
style.FillPattern = FillPattern.SolidForeground;
style.Alignment = HorizontalAlignment.Center;
style.BorderBottom = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderTop = BorderStyle.Thin;
style.SetFont(bold);
row = dashboard.GetRow(rowIndex + 1);
foreach (LegendColumn column in Enum.GetValues(typeof(LegendColumn)))
{
cell = row.CreateCell((int)column, CellType.String);
cell.SetCellValue(column.ToString());
cell.CellStyle = style;
}
cell = row.CreateCell((int)LegendColumn.Name + 1, CellType.String);
cell.CellStyle = style;
region = new CellRangeAddress(rowIndex + 1, rowIndex + 1, (int)LegendColumn.Name, (int)LegendColumn.Name + 1);
dashboard.AddMergedRegion(region);
}
private void ExportSheet(ISheet sheet, StreamWriter sw)
{
int cellCount = sheet.GetRow(3).LastCellNum;
CellInfo[] cellInfos = new CellInfo[cellCount];
for (int i = 2; i < cellCount; i++)
{
string fieldDesc = GetCellString(sheet, 2, i);
string fieldName = GetCellString(sheet, 3, i);
string fieldType = GetCellString(sheet, 4, i);
cellInfos[i] = new CellInfo()
{
Name = fieldName, Type = fieldType, Desc = fieldDesc
};
}
for (int i = 5; i <= sheet.LastRowNum; ++i)
{
if (GetCellString(sheet, i, 2) == "")
{
continue;
}
StringBuilder sb = new StringBuilder();
sb.Append("{");
IRow row = sheet.GetRow(i);
for (int j = 2; j < cellCount; ++j)
{
string desc = cellInfos[j].Desc.ToLower();
if (desc.StartsWith("#"))
{
continue;
}
// s开头表示这个字段是服务端专用
if (desc.StartsWith("s") && this.isClient)
{
continue;
}
// c开头表示这个字段是客户端专用
if (desc.StartsWith("c") && !this.isClient)
{
continue;
}
string fieldValue = GetCellString(row, j);
if (fieldValue == "")
{
throw new Exception($"sheet: {sheet.SheetName} 中有空白字段 {i},{j}");
}
if (j > 2)
{
sb.Append(",");
}
string fieldName = cellInfos[j].Name;
if (fieldName == "Id" || fieldName == "_id")
{
if (this.isClient)
{
fieldName = "Id";
}
else
{
fieldName = "_id";
}
}
string fieldType = cellInfos[j].Type;
sb.Append($"\"{fieldName}\":{Convert(fieldType, fieldValue)}");
}
sb.Append("}");
sw.WriteLine(sb.ToString());
}
}
static void OnPostprocessAllAssets(string[] importedAssets, string[] deletedAssets, string[] movedAssets, string[] movedFromAssetPaths)
{
foreach (string asset in importedAssets)
{
if (!filePath.Equals(asset))
{
continue;
}
EnemyData data = (EnemyData)AssetDatabase.LoadAssetAtPath(exportPath, typeof(EnemyData));
if (data == null)
{
data = ScriptableObject.CreateInstance <EnemyData> ();
AssetDatabase.CreateAsset((ScriptableObject)data, exportPath);
data.hideFlags = HideFlags.NotEditable;
}
data.sheets.Clear();
using (FileStream stream = File.Open(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite)) {
IWorkbook book = null;
if (Path.GetExtension(filePath) == ".xls")
{
book = new HSSFWorkbook(stream);
}
else
{
book = new XSSFWorkbook(stream);
}
foreach (string sheetName in sheetNames)
{
ISheet sheet = book.GetSheet(sheetName);
if (sheet == null)
{
Debug.LogError("[QuestData] sheet not found:" + sheetName);
continue;
}
EnemyData.Sheet s = new EnemyData.Sheet();
s.name = sheetName;
for (int i = 1; i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
ICell cell = null;
EnemyData.Param p = new EnemyData.Param();
cell = row.GetCell(0); p.ID = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(1); p.Name = (cell == null ? "" : cell.StringCellValue);
cell = row.GetCell(2); p.AttackType = (int)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(3); p.Heleth = (float)(cell == null ? 0 : cell.NumericCellValue);
cell = row.GetCell(4); p.Speed = (float)(cell == null ? 0 : cell.NumericCellValue);
s.list.Add(p);
}
data.sheets.Add(s);
}
}
ScriptableObject obj = AssetDatabase.LoadAssetAtPath(exportPath, typeof(ScriptableObject)) as ScriptableObject;
EditorUtility.SetDirty(obj);
}
}
public Stream ToExcel()
{
int rowIndex = 0;
//创建workbook
HSSFWorkbook workbook = new HSSFWorkbook();
MemoryStream ms = new MemoryStream();
ISheet sheet = workbook.CreateSheet("sheet1");
IRow row = sheet.CreateRow(rowIndex);
row.Height = 200 * 3;
//表头样式
ICellStyle style = workbook.CreateCellStyle();
style.Alignment = HorizontalAlignment.Left;//居中对齐
//表头单元格背景色
style.FillForegroundColor = HSSFColor.Grey25Percent.Index;
style.FillPattern = FillPattern.SolidForeground;
//表头单元格边框
style.BorderBottom = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.TopBorderColor = HSSFColor.Black.Index;
style.RightBorderColor = HSSFColor.Black.Index;
style.BottomBorderColor = HSSFColor.Black.Index;
style.LeftBorderColor = HSSFColor.Black.Index;
style.VerticalAlignment = VerticalAlignment.Center;
//表头字体设置
IFont font = workbook.CreateFont();
font.FontHeightInPoints = 12; //字号
font.Boldweight = 600; //加粗
//font.Color = HSSFColor.WHITE.index;//颜色
style.SetFont(font);
//数据样式
ICellStyle datastyle = workbook.CreateCellStyle();
datastyle.Alignment = HorizontalAlignment.Left;//左对齐
//数据单元格的边框
datastyle.BorderTop = BorderStyle.Thin;
datastyle.BorderRight = BorderStyle.Thin;
datastyle.BorderBottom = BorderStyle.Thin;
datastyle.BorderLeft = BorderStyle.Thin;
datastyle.TopBorderColor = HSSFColor.Black.Index;
datastyle.RightBorderColor = HSSFColor.Black.Index;
datastyle.BottomBorderColor = HSSFColor.Black.Index;
datastyle.LeftBorderColor = HSSFColor.Black.Index;
//数据的字体
IFont datafont = workbook.CreateFont();
datafont.FontHeightInPoints = 11;//字号
datastyle.SetFont(datafont);
//设置列宽
sheet.SetColumnWidth(0, 20 * 256);
int colWidth0 = sheet.GetColumnWidth(0);
sheet.SetColumnWidth(1, 20 * 256);
int colWidth1 = sheet.GetColumnWidth(1);
sheet.SetColumnWidth(2, 20 * 256);
int colWidth2 = sheet.GetColumnWidth(2);
sheet.SetColumnWidth(9, 40 * 256);
sheet.DisplayGridlines = false;
try {
//表头数据
for (int i = 0; i < titles.Length; i++)
{
ICell cell = row.CreateCell(i);
cell.SetCellValue(titles[i]);
cell.CellStyle = style;
}
for (int k = 0; k < data.Count; k++)
{
row = sheet.CreateRow(k + 1);
row.Height = 200 * 2;
T t = data[k];
// 获得此模型的公共属性
PropertyInfo[] propertys = t.GetType().GetProperties();
for (int j = 0; j < propertys.Length; j++)
{
var value = propertys[j].GetValue(null, null).ToString();
ICell cell = row.CreateCell(j);
cell.SetCellValue(value);
cell.CellStyle = datastyle;
}
}
} catch (Exception ex) {
} finally {
workbook.Write(ms);
ms.Flush();
ms.Position = 0;
workbook = null;
sheet = null;
row = null;
}
return(ms);
}
public static void DataTable_To_Excel(DataTable pDatos, string pFilePath)
{
try
{
if (pDatos != null && pDatos.Rows.Count > 0)
{
IWorkbook workbook = null;
ISheet worksheet = null;
using (FileStream stream = new FileStream(pFilePath, FileMode.Create, FileAccess.ReadWrite))
{
string Ext = System.IO.Path.GetExtension(pFilePath); //<-Extension del archivo
switch (Ext.ToLower())
{
case ".xls":
HSSFWorkbook workbookH = new HSSFWorkbook();
NPOI.HPSF.DocumentSummaryInformation dsi = NPOI.HPSF.PropertySetFactory.CreateDocumentSummaryInformation();
dsi.Company = "Cutcsa"; dsi.Manager = "Departamento Informatico";
workbookH.DocumentSummaryInformation = dsi;
workbook = workbookH;
break;
case ".xlsx": workbook = new XSSFWorkbook(); break;
}
worksheet = workbook.CreateSheet(pDatos.TableName); //<-Usa el nombre de la tabla como nombre de la Hoja
//CREAR EN LA PRIMERA FILA LOS TITULOS DE LAS COLUMNAS
int iRow = 0;
if (pDatos.Columns.Count > 0)
{
int iCol = 0;
IRow fila = worksheet.CreateRow(iRow);
foreach (DataColumn columna in pDatos.Columns)
{
ICell cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(columna.ColumnName);
iCol++;
}
iRow++;
}
//FORMATOS PARA CIERTOS TIPOS DE DATOS
ICellStyle _dateCellStyle = workbook.CreateCellStyle();
_dateCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd/MM/yyyy");
ICellStyle _dateTimeCellStyle = workbook.CreateCellStyle();
_dateTimeCellStyle.DataFormat = workbook.CreateDataFormat().GetFormat("dd-MM-yyyy HH:mm:ss");
//AHORA CREAR UNA FILA POR CADA REGISTRO DE LA TABLA
foreach (DataRow row in pDatos.Rows)
{
IRow fila = worksheet.CreateRow(iRow);
int iCol = 0;
foreach (DataColumn column in pDatos.Columns)
{
ICell cell = null; //<-Representa la celda actual
object cellValue = row[iCol]; //<- El valor actual de la celda
switch (column.DataType.ToString())
{
case "System.Boolean":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Boolean);
if (Convert.ToBoolean(cellValue))
{
cell.SetCellFormula("TRUE()");
}
else
{
cell.SetCellFormula("FALSE()");
}
//cell.CellStyle = _boolCellStyle;
}
break;
case "System.String":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.String);
cell.SetCellValue(Convert.ToString(cellValue));
}
break;
case "System.Int32":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt32(cellValue));
//cell.CellStyle = _intCellStyle;
}
break;
case "System.Int64":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToInt64(cellValue));
//cell.CellStyle = _intCellStyle;
}
break;
case "System.Decimal":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
//cell.CellStyle = _doubleCellStyle;
}
break;
case "System.Double":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDouble(cellValue));
//cell.CellStyle = _doubleCellStyle;
}
break;
case "System.DateTime":
if (cellValue != DBNull.Value)
{
cell = fila.CreateCell(iCol, CellType.Numeric);
cell.SetCellValue(Convert.ToDateTime(cellValue));
//Si No tiene valor de Hora, usar formato dd-MM-yyyy
DateTime cDate = Convert.ToDateTime(cellValue);
if (cDate != null && cDate.Hour > 0)
{
cell.CellStyle = _dateTimeCellStyle;
}
else
{
cell.CellStyle = _dateCellStyle;
}
}
break;
default:
break;
}
iCol++;
}
iRow++;
}
workbook.Write(stream);
stream.Close();
}
}
}
catch (Exception ex)
{
throw ex;
}
}
/// <summary>
/// 导出日本签证时间表
/// </summary>
/// <param name="list"></param>
/// <param name="remark"></param>
/// <param name="groupNo"></param>
/// <returns></returns>
public static bool GetAllCountExcel(List <TravelAgency.Model.Visa> list)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("日本签证时间表");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("团号");
row.CreateCell(1).SetCellValue("送签日期");
row.CreateCell(2).SetCellValue("出签日期");
row.CreateCell(3).SetCellValue("送签社担当");
row.CreateCell(4).SetCellValue("人数");
row.CreateCell(5).SetCellValue("资料寄出时间");
row.CreateCell(6).SetCellValue("销售人员");
row.CreateCell(7).SetCellValue("客户");
row.CreateCell(8).SetCellValue("其他备注");
//2.2设置列宽度
sheet.SetColumnWidth(0, 60 * 256); //团号");
sheet.SetColumnWidth(1, 15 * 256); //送签日期");
sheet.SetColumnWidth(2, 15 * 256); //出签日期");
sheet.SetColumnWidth(3, 15 * 256); //送签社担当")
sheet.SetColumnWidth(4, 8 * 256); //人数");
sheet.SetColumnWidth(5, 12 * 256); //资料寄出时间
sheet.SetColumnWidth(6, 17 * 256); //销售人员");
sheet.SetColumnWidth(7, 10 * 256); //客户");
sheet.SetColumnWidth(8, 10 * 256); //其他备注");
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].GroupNo);
row.CreateCell(1).SetCellValue(DateTimeFormator.DateTimeToString1(list[i].InTime));
row.CreateCell(2).SetCellValue(DateTimeFormator.DateTimeToString1(list[i].OutTime));
row.CreateCell(3).SetCellValue(list[i].Person);
row.CreateCell(4).SetCellValue(list[i].Number.ToString());
row.CreateCell(5).SetCellValue("");//资料寄出时间先不设置
row.CreateCell(6).SetCellValue(list[i].SalesPerson);
row.CreateCell(7).SetCellValue(list[i].Client);
row.CreateCell(8).SetCellValue(""); //其他备注先不设置
}
////4.2合并单元格
//sheet.AddMergedRegion(new CellRangeAddress(2, sheet.LastRowNum, 12, 12));
//sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15));
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Left;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg("日本签证时间表.xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
public void SaveLoadNew()
{
XSSFWorkbook workbook = new XSSFWorkbook();
//check that the default date system is Set to 1900
CT_WorkbookPr pr = workbook.GetCTWorkbook().workbookPr;
Assert.IsNotNull(pr);
Assert.IsTrue(pr.IsSetDate1904());
Assert.IsFalse(pr.date1904, "XSSF must use the 1900 date system");
ISheet sheet1 = workbook.CreateSheet("sheet1");
ISheet sheet2 = workbook.CreateSheet("sheet2");
workbook.CreateSheet("sheet3");
IRichTextString rts = workbook.GetCreationHelper().CreateRichTextString("hello world");
sheet1.CreateRow(0).CreateCell((short)0).SetCellValue(1.2);
sheet1.CreateRow(1).CreateCell((short)0).SetCellValue(rts);
sheet2.CreateRow(0);
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
FileInfo file = TempFile.CreateTempFile("poi-", ".xlsx");
Stream out1 = File.OpenWrite(file.Name);
workbook.Write(out1);
out1.Close();
// Check the namespace Contains what we'd expect it to
OPCPackage pkg = OPCPackage.Open(file.ToString());
PackagePart wbRelPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/_rels/workbook.xml.rels"));
Assert.IsNotNull(wbRelPart);
Assert.IsTrue(wbRelPart.IsRelationshipPart);
Assert.AreEqual(ContentTypes.RELATIONSHIPS_PART, wbRelPart.ContentType);
PackagePart wbPart =
pkg.GetPart(PackagingUriHelper.CreatePartName("/xl/workbook.xml"));
// Links to the three sheets, shared strings and styles
Assert.IsTrue(wbPart.HasRelationships);
Assert.AreEqual(5, wbPart.Relationships.Size);
// Load back the XSSFWorkbook
workbook = new XSSFWorkbook(pkg);
Assert.AreEqual(3, workbook.NumberOfSheets);
Assert.IsNotNull(workbook.GetSheetAt(0));
Assert.IsNotNull(workbook.GetSheetAt(1));
Assert.IsNotNull(workbook.GetSheetAt(2));
Assert.IsNotNull(workbook.GetSharedStringSource());
Assert.IsNotNull(workbook.GetStylesSource());
Assert.AreEqual(0, workbook.GetSheetAt(0).FirstRowNum);
Assert.AreEqual(1, workbook.GetSheetAt(0).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(1).LastRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).FirstRowNum);
Assert.AreEqual(0, workbook.GetSheetAt(2).LastRowNum);
sheet1 = workbook.GetSheetAt(0);
Assert.AreEqual(1.2, sheet1.GetRow(0).GetCell(0).NumericCellValue, 0.0001);
Assert.AreEqual("hello world", sheet1.GetRow(1).GetCell(0).RichStringCellValue.String);
pkg.Close();
}
public static bool GetIndividualVisaExcel(List <TravelAgency.Model.VisaInfo> list, string remark, string groupNo)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("签证申请人名单");
//2.1创建表头
IRow rowHeader = sheet.CreateRow(0);
rowHeader.CreateCell(0).SetCellValue("签证申请人名单");
rowHeader.HeightInPoints = 50;
IRow row = sheet.CreateRow(1);
row.CreateCell(0).SetCellValue("编号");
row.CreateCell(1).SetCellValue("姓名(中文)");
row.CreateCell(2).SetCellValue("姓名(英文)");
row.CreateCell(3).SetCellValue("性别");
row.CreateCell(4).SetCellValue("护照发行地");
row.CreateCell(5).SetCellValue("居住地点");
row.CreateCell(6).SetCellValue("出生年月日");
row.CreateCell(7).SetCellValue("职业");
row.CreateCell(8).SetCellValue("出境记录");
row.CreateCell(9).SetCellValue("婚姻");
row.CreateCell(10).SetCellValue("身份确认");
row.CreateCell(11).SetCellValue("经济能力确认");
row.CreateCell(12).SetCellValue("备注");
row.CreateCell(13).SetCellValue("旅行社意见");
row.CreateCell(14).SetCellValue("护照号");
//row.CreateCell(15).SetCellValue("手机号");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256); //编号
sheet.SetColumnWidth(1, 15 * 256); //姓名(中文)
sheet.SetColumnWidth(2, 20 * 256); //姓名(英文)
sheet.SetColumnWidth(3, 5 * 256); //性别
sheet.SetColumnWidth(4, 10 * 256); //护照发行地
sheet.SetColumnWidth(5, 25 * 256); //居住地点
sheet.SetColumnWidth(6, 15 * 256); //出生年月日
sheet.SetColumnWidth(7, 10 * 256); //职业
sheet.SetColumnWidth(8, 10 * 256); //出境记录
sheet.SetColumnWidth(9, 10 * 256); //婚姻
sheet.SetColumnWidth(10, 20 * 256); //身份确认
sheet.SetColumnWidth(11, 25 * 256); //经济能力确认
sheet.SetColumnWidth(12, 10 * 256); //备注
sheet.SetColumnWidth(13, 10 * 256); //旅行社意见
sheet.SetColumnWidth(14, 15 * 256); //护照号
//sheet.SetColumnWidth(15, 15 * 256);//手机号
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 2);
//设置行高
row.HeightInPoints = 100;
//设置值
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].EnglishName);
row.CreateCell(3).SetCellValue(list[i].Sex);
row.CreateCell(4).SetCellValue(list[i].IssuePlace);
row.CreateCell(5).SetCellValue(list[i].Residence);
row.CreateCell(6).SetCellValue(DateTimeFormator.DateTimeToString(list[i].Birthday));
row.CreateCell(7).SetCellValue(list[i].Occupation);
row.CreateCell(8).SetCellValue(list[i].DepartureRecord);
row.CreateCell(9).SetCellValue(list[i].Marriaged);
row.CreateCell(10).SetCellValue(list[i].Identification);
row.CreateCell(11).SetCellValue(list[i].FinancialCapacity);
row.CreateCell(12).SetCellValue(remark);
row.CreateCell(13).SetCellValue(list[i].AgencyOpinion);
row.CreateCell(14).SetCellValue(list[i].PassportNo);
//row.CreateCell(15).SetCellValue(list[i].Phone);
}
//4.2合并单元格
sheet.AddMergedRegion(new CellRangeAddress(2, sheet.LastRowNum, 12, 12)); //备注列合并
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, 15)); //表头合并
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.WrapText = true; //文本自动换行
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
HSSFFont font = (HSSFFont)wkbook.CreateFont();
font.FontHeightInPoints = 12;
style.SetFont(font);
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
ICellStyle headerStyle = wkbook.CreateCellStyle();
headerStyle.VerticalAlignment = VerticalAlignment.Center;
headerStyle.Alignment = HorizontalAlignment.Center;
headerStyle.BorderTop = BorderStyle.Thin;
headerStyle.BorderBottom = BorderStyle.Thin;
headerStyle.BorderLeft = BorderStyle.Thin;
headerStyle.BorderRight = BorderStyle.Thin;
HSSFFont font1 = (HSSFFont)wkbook.CreateFont();
font1.FontHeightInPoints = 15;
headerStyle.SetFont(font1);
sheet.GetRow(0).GetCell(0).CellStyle = headerStyle;
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
/// <summary>
/// 导入 excel 文件的一个 sheet
/// </summary>
/// <param name="sheet"></param>
void ImportOneSheetData(ISheet sheet)
{
int rowCount = sheet.LastRowNum;//总行数
if (rowCount > 0)
{
IRow firstRow = sheet.GetRow(0); //第一行
IRow twoRow = sheet.GetRow(1); //第二行
int cellCount = firstRow.LastCellNum; //列数
string sheetName = firstRow.GetCell(0).StringCellValue; //表名
Enum_TableData += tabs + sheetName + "," + newline;
string sheetCsPath = CsPathDir + sheetName + ".cs";
DeleteFile(sheetCsPath);
DeclarationDescriptionInterface(firstRow, twoRow, sheetCsPath);
CreatDataFileStream(DataPathDir + sheetName + ".data");
DataBinaryWriter.Write(sheetName);
//填充行
for (int i = 2; i <= rowCount; ++i)
{
IRow row = sheet.GetRow(i);
if (row == null)
{
continue;
}
ImportOneLineData(cellCount, twoRow, row);
string strr = String.Empty;
for (int j = row.FirstCellNum; j < cellCount; ++j)
{
ICell cell = row.GetCell(j);
if (cell == null)
{
}
else
{
switch (cell.CellType)
{
case CellType.Unknown:
Console.WriteLine("未知");
break;
case CellType.Numeric:
strr += cell.NumericCellValue + "_";
break;
case CellType.String:
strr += cell.StringCellValue + "_";
break;
case CellType.Formula:
Console.WriteLine("公示");
break;
case CellType.Blank:
Console.WriteLine("空单元格");
break;
case CellType.Boolean:
Console.WriteLine("布尔");
break;
case CellType.Error:
Console.WriteLine("Error");
continue;
}
}
}
Console.WriteLine(strr);
}
int Table_End = DataUtil.DataKeywordToTypeNum(DataKeyword.Table_End);
DataBinaryWriter.Write(Table_End);
CloseDataFileStream();
}
}
public static bool GetTeamVisaExcelOfThailand(List <TravelAgency.Model.VisaInfo> list, string groupNo)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("签证申请名单");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("姓名");
row.CreateCell(1).SetCellValue("英文姓");
row.CreateCell(2).SetCellValue("英文名");
row.CreateCell(3).SetCellValue("性别");
row.CreateCell(4).SetCellValue("出生日期");
row.CreateCell(5).SetCellValue("护照号");
row.CreateCell(6).SetCellValue("签发日期");
row.CreateCell(7).SetCellValue("有效期至");
row.CreateCell(8).SetCellValue("出生地点拼音");
row.CreateCell(9).SetCellValue("签发地点拼音");
row.CreateCell(10).SetCellValue("英文姓名");
//2.2设置列宽度
sheet.SetColumnWidth(0, 20 * 256); //序号
sheet.SetColumnWidth(1, 20 * 256); //姓名
sheet.SetColumnWidth(2, 20 * 256); //英文姓名
sheet.SetColumnWidth(3, 20 * 256); //性别
sheet.SetColumnWidth(4, 20 * 256); //出生地
sheet.SetColumnWidth(5, 20 * 256); //出生日期
sheet.SetColumnWidth(6, 20 * 256); //护照号
sheet.SetColumnWidth(7, 20 * 256); //签发地
sheet.SetColumnWidth(8, 20 * 256); //签发日期
sheet.SetColumnWidth(9, 20 * 256); //有效期至
sheet.SetColumnWidth(10, 20 * 256); //职业
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(list[i].Name);
row.CreateCell(1).SetCellValue(list[i].EnglishName.Split(' ')[0]);
row.CreateCell(2).SetCellValue(list[i].EnglishName.Split(' ')[1]);
if (list[i].Sex == "男")
{
row.CreateCell(3).SetCellValue("F");
}
else
{
row.CreateCell(3).SetCellValue("M");
}
row.CreateCell(4).SetCellValue(DateTimeFormator.DateTimeToStringOfThailand(list[i].Birthday));
row.CreateCell(5).SetCellValue(list[i].PassportNo);
row.CreateCell(6).SetCellValue(DateTimeFormator.DateTimeToStringOfThailand(list[i].LicenceTime));
row.CreateCell(7).SetCellValue(DateTimeFormator.DateTimeToStringOfThailand(list[i].ExpiryDate));
List <string> pinyins = Common.PinyinParse.PinYinConverterHelp.GetTotalPingYin(list[i].Birthplace).TotalPingYin;
row.CreateCell(8).SetCellValue(pinyins[pinyins.Count - 1].ToUpper()); //TODO:这个地方拼音还有点问题，因为可能有多个
pinyins = Common.PinyinParse.PinYinConverterHelp.GetTotalPingYin(list[i].IssuePlace).TotalPingYin;
row.CreateCell(9).SetCellValue(pinyins[pinyins.Count - 1].ToUpper());
row.CreateCell(10).SetCellValue(list[i].EnglishName);
}
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Left;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
////4.2合并单元格
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 12, 12));
//sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 13, 13));
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
/// <summary>
/// 填充数据
/// </summary>
/// <param name="dt"></param>
/// <param name="sheetName">工作表名称</param>
/// <param name="tableName">表的标题</param>
/// <returns></returns>
public bool FillData(DataTable dt, string sheetName, string tableName)
{
if (dt == null)
{
return(false);
}
if (sheetName == "")
{
return(false);
}
if (!Loaded)
{
return(false);
}
ISheet sheet = null;
int index = workbook.GetSheetIndex(sheetName);
if (index < 0)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
sheet = workbook.GetSheetAt(index);
}
//获取表头
int columnSize = dt.Columns.Count;
if (columnSize < 1)
{
return(false);
}
//在A1创建一个单元格
IRow row = sheet.CreateRow(0);
ICell cell = row.CreateCell(0);
//设置标题
cell.SetCellValue(tableName);
//创建样式
ICellStyle style = workbook.CreateCellStyle( );
style.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Center; //设置居中
IFont font = workbook.CreateFont( );
font.FontHeight = 36;
font.FontName = "微软雅黑";
style.SetFont(font);
cell.CellStyle = style;
//合并单元格
sheet.AddMergedRegion(new CellRangeAddress(0, 0, 0, columnSize - 1));
//填充表头
ICellStyle headStyle = workbook.CreateCellStyle( );
headStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
IFont headFont = workbook.CreateFont( );
headFont.FontHeight = 13;
headFont.Boldweight = (short)FontBoldWeight.Bold;
headFont.FontName = "微软雅黑";
headStyle.SetFont(headFont);
IRow headRow = sheet.CreateRow(1);
for (int i = 0; i < dt.Columns.Count; i++)
{
ICell headCell = headRow.CreateCell(i);
headCell.CellStyle = headStyle;
headCell.SetCellValue(dt.Columns[i].ToString( ));
}
//填充数据
ICellStyle dataStyle = workbook.CreateCellStyle( );
dataStyle.Alignment = NPOI.SS.UserModel.HorizontalAlignment.Left;
IFont dataFont = workbook.CreateFont( );
dataFont.FontHeight = 11;
dataFont.FontName = "微软雅黑";
dataFont.Boldweight = (short)FontBoldWeight.Normal;
dataStyle.SetFont(dataFont);
for (int i = 0; i < dt.Rows.Count; i++)
{
IRow dataRow = sheet.CreateRow(i + 2);
for (int j = 0; j < dt.Columns.Count; j++)
{
ICell tmp_cell = dataRow.CreateCell(j);
tmp_cell.CellStyle = dataStyle;
Type t = dt.Columns[j].DataType;
string type = t.ToString( );
if (type.Contains("Int") || type.Contains("Double") || type.Contains("Long") || type.Contains("Shot") || type.Contains("Float"))
{
Double d = Convert.ToDouble(dt.Rows[i][j]);
if (d > 99999999999)
{
tmp_cell.SetCellType(CellType.String);
}
else
{
tmp_cell.SetCellType(CellType.Numeric);
}
}
else
{
tmp_cell.SetCellType(CellType.String);
}
tmp_cell.SetCellValue(dt.Rows[i][j].ToString());
}
}
//保存文件
return(WriteFile( ));
}
public static bool GetTeamVisaExcelOfJapan(List <TravelAgency.Model.VisaInfo> list, string groupNo)
{
//1.创建工作簿对象
IWorkbook wkbook = new HSSFWorkbook();
//2.创建工作表对象
ISheet sheet = wkbook.CreateSheet("签证申请名单");
//2.1创建表头
IRow row = sheet.CreateRow(0);
row.CreateCell(0).SetCellValue("序号");
row.CreateCell(1).SetCellValue("姓名");
row.CreateCell(2).SetCellValue("英文姓名");
row.CreateCell(3).SetCellValue("性别");
row.CreateCell(4).SetCellValue("出生地");
row.CreateCell(5).SetCellValue("出生日期");
row.CreateCell(6).SetCellValue("护照号");
row.CreateCell(7).SetCellValue("签发地");
row.CreateCell(8).SetCellValue("签发日期");
row.CreateCell(9).SetCellValue("有效期至");
row.CreateCell(10).SetCellValue("职业");
row.CreateCell(11).SetCellValue("联系电话");
row.CreateCell(12).SetCellValue("客户");
row.CreateCell(13).SetCellValue("销售");
//2.2设置列宽度
sheet.SetColumnWidth(0, 5 * 256); //序号
sheet.SetColumnWidth(1, 15 * 256); //姓名
sheet.SetColumnWidth(2, 25 * 256); //英文姓名
sheet.SetColumnWidth(3, 5 * 256); //性别
sheet.SetColumnWidth(4, 10 * 256); //出生地
sheet.SetColumnWidth(5, 20 * 256); //出生日期
sheet.SetColumnWidth(6, 20 * 256); //护照号
sheet.SetColumnWidth(7, 10 * 256); //签发地
sheet.SetColumnWidth(8, 20 * 256); //签发日期
sheet.SetColumnWidth(9, 20 * 256); //有效期至
sheet.SetColumnWidth(10, 20 * 256); //职业
sheet.SetColumnWidth(11, 20 * 256); //联系电话
sheet.SetColumnWidth(12, 20 * 256); //客户
sheet.SetColumnWidth(13, 20 * 256); //销售
//3.插入行和单元格
for (int i = 0; i != list.Count; ++i)
{
//创建单元格
row = sheet.CreateRow(i + 1);
////设置行高
//row.HeightInPoints = 50;
//设置值
row.CreateCell(0).SetCellValue(i + 1);
row.CreateCell(1).SetCellValue(list[i].Name);
row.CreateCell(2).SetCellValue(list[i].EnglishName);
row.CreateCell(3).SetCellValue(list[i].Sex);
row.CreateCell(4).SetCellValue(list[i].Birthplace);
row.CreateCell(5).SetCellValue(DateTimeFormator.DateTimeToString(list[i].Birthday));
row.CreateCell(6).SetCellValue(list[i].PassportNo);
row.CreateCell(7).SetCellValue(list[i].IssuePlace);
row.CreateCell(8).SetCellValue(DateTimeFormator.DateTimeToString(list[i].LicenceTime));
row.CreateCell(9).SetCellValue(DateTimeFormator.DateTimeToString(list[i].ExpiryDate));
row.CreateCell(10).SetCellValue(list[i].Occupation);
row.CreateCell(11).SetCellValue(list[i].Phone);
row.CreateCell(12).SetCellValue(list[i].Client);
row.CreateCell(13).SetCellValue(list[i].Salesperson);
}
//4.1设置对齐风格和边框
ICellStyle style = wkbook.CreateCellStyle();
style.VerticalAlignment = VerticalAlignment.Center;
style.Alignment = HorizontalAlignment.Center;
style.BorderTop = BorderStyle.Thin;
style.BorderBottom = BorderStyle.Thin;
style.BorderLeft = BorderStyle.Thin;
style.BorderRight = BorderStyle.Thin;
for (int i = 0; i <= sheet.LastRowNum; i++)
{
row = sheet.GetRow(i);
for (int c = 0; c < row.LastCellNum; ++c)
{
row.GetCell(c).CellStyle = style;
}
}
//4.2合并单元格
sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 12, 12));
sheet.AddMergedRegion(new CellRangeAddress(1, sheet.LastRowNum, 13, 13));
//5.执行写入磁盘
string dstName = GlobalUtils.OpenSaveFileDlg(groupNo + ".xls", "office 2003 excel|*.xls");
return(SaveFile(dstName, wkbook));
}
private static string GetCellString(ISheet sheet, int i, int j)
{
return(sheet.GetRow(i)?.GetCell(j)?.ToString() ?? "");
}
/// <summary>
/// 将DataTable数据导出到excel中
/// </summary>
/// <param name="data">要导入的数据</param>
/// <param name="isColumnWritten">DataTable的列名是否要导入</param>
/// <param name="sheetName">要导入的excel的sheet的名称</param>
/// <returns>导入数据行数(包含列名那一行)</returns>
public int DataTableToExcel(DataTable data, string sheetName, bool isColumnWritten)
{
int i = 0;
int j = 0;
int count = 0;
ISheet sheet = null;
fs = new FileStream(fileName, FileMode.OpenOrCreate, FileAccess.ReadWrite);
if (fileName.IndexOf(".xlsx") > 0) // 2007版本
{
workbook = new XSSFWorkbook();
}
else if (fileName.IndexOf(".xls") > 0) // 2003版本
{
workbook = new HSSFWorkbook();
}
try
{
if (workbook != null)
{
sheet = workbook.CreateSheet(sheetName);
}
else
{
return(-1);
}
if (isColumnWritten == true) //写入DataTable的列名
{
IRow row = sheet.CreateRow(0);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Columns[j].ColumnName);
}
count = 1;
}
else
{
count = 0;
}
for (i = 0; i < data.Rows.Count; ++i)
{
IRow row = sheet.CreateRow(count);
for (j = 0; j < data.Columns.Count; ++j)
{
row.CreateCell(j).SetCellValue(data.Rows[i][j].ToString());
}
++count;
}
////第一行自动筛选
//CellRangeAddress c = new CellRangeAddress(1, 65535, 0,9);
//sheet.SetAutoFilter(c);
//冻结窗口
sheet.CreateFreezePane(0, 1);
workbook.Write(fs); //写入到excel
return(count);
}
catch (Exception ex)
{
Console.WriteLine("Exception: " + ex.Message);
return(-1);
}
}
private void simpleButton1_Click(object sender, EventArgs e)
{
//读取各相机下的检测项目，读取各相机的统计结果
try
{
HSSFWorkbook wk = new HSSFWorkbook();
//创建一个Sheet
ISheet sheet = wk.CreateSheet("检测结果统计");
//在第一行创建行
IRow row = sheet.CreateRow(0);
//在第一行的第一列创建单元格
ICell cell = row.CreateCell(0);
cell.SetCellValue("检测项（Feature Item）");
cell = row.CreateCell(1);
cell.SetCellValue("修正(Calibration)");
cell = row.CreateCell(2);
cell.SetCellValue("上限(Max)");
cell = row.CreateCell(3);
cell.SetCellValue("下限(Min)");
cell = row.CreateCell(4);
cell.SetCellValue("OK");
cell = row.CreateCell(5);
cell.SetCellValue("NG");
cell = row.CreateCell(6);
cell.SetCellValue("合格率");
DataRowCollection drc = ds.Tables["detailTable"].Rows;
int totalindex = 0;
foreach (CCamera c in ccameras)
{
//获取c的检测项个数
int itemcount = c.goodcountlist.Count();
for (int i = 0; i < itemcount; i++)
{
//写入excel
int currentgood = c.goodcountlist[i];
int currentbad = c.badcountlist[i];
DataRow dr = drc[totalindex];
string s = dr[1].ToString();//检测项名称
double xz = (double)dr[2];
double sx = (double)dr[3];
double xx = (double)dr[4];
row = sheet.CreateRow(totalindex + 1);
cell = row.CreateCell(0);
cell.SetCellValue(s);
cell = row.CreateCell(1);
cell.SetCellValue(xz);
cell = row.CreateCell(2);
cell.SetCellValue(sx);
cell = row.CreateCell(3);
cell.SetCellValue(xx);
cell = row.CreateCell(4);
cell.SetCellValue(currentgood);
cell = row.CreateCell(5);
cell.SetCellValue(currentbad);
cell = row.CreateCell(6);
cell.SetCellValue(((double)currentgood / (double)Turntable.Instance.pn.totalCount));
totalindex++;
}
}
row = sheet.CreateRow(totalindex + 1);
row = sheet.CreateRow(totalindex + 2);
cell = row.CreateCell(0);
cell.SetCellValue("总产量");
cell = row.CreateCell(1);
cell.SetCellValue("OK");
cell = row.CreateCell(2);
cell.SetCellValue("NG");
cell = row.CreateCell(3);
cell.SetCellValue("合格率");
row = sheet.CreateRow(totalindex + 3);
cell = row.CreateCell(0);
cell.SetCellValue(Turntable.Instance.pn.totalCount);
cell = row.CreateCell(1);
cell.SetCellValue(Turntable.Instance.pn.goodNum);
cell = row.CreateCell(2);
cell.SetCellValue(Turntable.Instance.pn.badNum);
cell = row.CreateCell(3);
cell.SetCellValue(((double)Turntable.Instance.pn.goodNum / (double)Turntable.Instance.pn.totalCount));
using (FileStream fs = File.OpenWrite("统计报表.xls"))
{
wk.Write(fs);//向打开的这个xls文件中写入并保存。
}
}
catch
{
MessageBox.Show("报表生成发生异常");
}
}
/// <summary>
/// Excel导入
/// </summary>
/// <param name="filePath"></param>
/// <returns></returns>
public List <T> ImportFromExcel(string filePath)
{
string absoluteFilePath = GlobalContext.HostingEnvironment.ContentRootPath + filePath.Replace(Path.AltDirectorySeparatorChar, Path.DirectorySeparatorChar);
List <T> list = new List <T>();
HSSFWorkbook hssfWorkbook = null;
XSSFWorkbook xssWorkbook = null;
ISheet sheet = null;
using (FileStream file = new FileStream(absoluteFilePath, FileMode.Open, FileAccess.Read))
{
switch (Path.GetExtension(filePath))
{
case ".xls":
hssfWorkbook = new HSSFWorkbook(file);
sheet = hssfWorkbook.GetSheetAt(0);
break;
case ".xlsx":
xssWorkbook = new XSSFWorkbook(file);
sheet = xssWorkbook.GetSheetAt(0);
break;
default:
throw new Exception("不支持的文件格式");
}
}
IRow columnRow = sheet.GetRow(1); // 第二行为字段名
Dictionary <int, PropertyInfo> mapPropertyInfoDict = new Dictionary <int, PropertyInfo>();
for (int j = 0; j < columnRow.LastCellNum; j++)
{
ICell cell = columnRow.GetCell(j);
PropertyInfo propertyInfo = MapPropertyInfo(cell.ParseToString());
if (propertyInfo != null)
{
mapPropertyInfoDict.Add(j, propertyInfo);
}
}
for (int i = (sheet.FirstRowNum + 2); i <= sheet.LastRowNum; i++)
{
IRow row = sheet.GetRow(i);
T entity = new T();
for (int j = row.FirstCellNum; j < columnRow.LastCellNum; j++)
{
if (mapPropertyInfoDict.ContainsKey(j))
{
if (row.GetCell(j) != null)
{
PropertyInfo propertyInfo = mapPropertyInfoDict[j];
switch (propertyInfo.PropertyType.ToString())
{
case "System.DateTime":
case "System.Nullable`1[System.DateTime]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDateTime());
break;
case "System.Boolean":
case "System.Nullable`1[System.Boolean]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToBool());
break;
case "System.Byte":
case "System.Nullable`1[System.Byte]":
mapPropertyInfoDict[j].SetValue(entity, Byte.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int16":
case "System.Nullable`1[System.Int16]":
mapPropertyInfoDict[j].SetValue(entity, Int16.Parse(row.GetCell(j).ParseToString()));
break;
case "System.Int32":
case "System.Nullable`1[System.Int32]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToInt());
break;
case "System.Int64":
case "System.Nullable`1[System.Int64]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToLong());
break;
case "System.Double":
case "System.Nullable`1[System.Double]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDouble());
break;
case "System.Decimal":
case "System.Nullable`1[System.Decimal]":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString().ParseToDecimal());
break;
default:
case "System.String":
mapPropertyInfoDict[j].SetValue(entity, row.GetCell(j).ParseToString());
break;
}
}
}
}
list.Add(entity);
}
return(list);
}
static void Main(string[] args)
{
IWorkbook workbook = new XSSFWorkbook();
ISheet sheet1 = workbook.CreateSheet("Sheet1");
//font style1: underlined, italic, red color, fontsize=20
IFont font1 = workbook.CreateFont();
font1.Color = HSSFColor.Red.Index;
font1.IsItalic = true;
font1.Underline = FontUnderlineType.Double;
font1.FontHeightInPoints = 20;
//bind font with style 1
ICellStyle style1 = workbook.CreateCellStyle();
style1.SetFont(font1);
//font style2: strikeout line, green color, fontsize=15, fontname='宋体'
IFont font2 = workbook.CreateFont();
font2.Color = HSSFColor.OliveGreen.Index;
font2.IsStrikeout = true;
font2.FontHeightInPoints = 15;
font2.FontName = "宋体";
//bind font with style 2
ICellStyle style2 = workbook.CreateCellStyle();
style2.SetFont(font2);
//apply font styles
ICell cell1 = sheet1.CreateRow(1).CreateCell(1);
cell1.SetCellValue("Hello World!");
cell1.CellStyle = style1;
ICell cell2 = sheet1.CreateRow(3).CreateCell(1);
cell2.SetCellValue("早上好！");
cell2.CellStyle = style2;
////cell with rich text
ICell cell3 = sheet1.CreateRow(5).CreateCell(1);
XSSFRichTextString richtext = new XSSFRichTextString("Microsoft OfficeTM");
//apply font to "Microsoft Office"
IFont font4 = workbook.CreateFont();
font4.FontHeightInPoints = 12;
richtext.ApplyFont(0, 16, font4);
//apply font to "TM"
IFont font3 = workbook.CreateFont();
font3.TypeOffset = FontSuperScript.Super;
font3.IsItalic = true;
font3.Color = HSSFColor.Blue.Index;
font3.FontHeightInPoints = 8;
richtext.ApplyFont(16, 18, font3);
cell3.SetCellValue(richtext);
FileStream sw = File.Create("test.xlsx");
workbook.Write(sw);
sw.Close();
}