private void toExcel(System.Data.DataTable dt, int i = 1)
{
Worksheet sheet = doc.Workbook.Worksheets.AddNamed(dt.TableName.IfNullOrEmpty("Sheet" + i.ToString()).Trim("$"));
cells = sheet.Cells;
int rows = dt.Rows.Count, cols = dt.Columns.Count;
for (int k = 1; k <= cols; k++)
{
cells.AddValueCell(1, k, dt.Columns[k - 1].Caption).Font.Bold = true;
}
for (int j = 2; j <= rows + 1; j++)
{
for (int k = 1; k <= cols; k++)
{
cells.AddValueCell(j, k, dt.Rows[j - 2][k - 1]);
}
}
}
/// <summary>
/// 写第i个工作表的row,column位置的数据
/// </summary>
/// <param name="i">第i个工作表</param>
/// <param name="row">行</param>
/// <param name="column">列</param>
/// <returns>值</returns>
public void Cells(int row, int column, object value)
{
cells.AddValueCell(row, column, value);
}
protected void Page_Load(object sender, EventArgs e)
{
org.in2bits.MyXls.XlsDocument doc = new XlsDocument();
doc.FileName = "TestingAgain.xls";
for (int s = 1; s <= 5; s++)
{
string sheetName;
//if (this.IsPostBack == true)
//{
sheetName = Request.Form["txtSheet" + s].Replace(",", string.Empty);
//}
//else sheetName = string.Empty;
if (sheetName.Trim() == string.Empty)
{
continue;
}
int rowMin, rowCount, colMin, colCount;
try
{
rowMin = int.Parse(Request.Form["txtRowMin" + s]) + 1;
rowCount = int.Parse(Request.Form["txtRows" + s]);
colMin = int.Parse(Request.Form["txtColMin" + s]);
colCount = int.Parse(Request.Form["txtCols" + s]);
}
catch
{
continue;
}
if (rowCount > 5000)
{
rowCount = 5000;
}
if (rowCount < 0)
{
rowCount = 0;
}
if (rowMin < 1)
{
rowMin = 1;
}
if (rowMin > 32767)
{
rowMin = 32767;
}
if (colCount > 50)
{
colCount = 50;
}
if (colCount < 1)
{
colCount = 1;
}
if (colMin < 1)
{
colMin = 1;
}
if (colMin > 100)
{
colMin = 100;
}
if (sheetName.Length > 35)
{
sheetName = sheetName.Substring(0, 35);
}
Worksheet sheet = doc.Workbook.Worksheets.AddNamed(sheetName);
Cells cells = sheet.Cells;
Cell miCelda;
ColumnInfo cinfo;
cinfo = new ColumnInfo(doc, sheet);
cinfo.ColumnIndexStart = 1;
cinfo.ColumnIndexEnd = 1;
cinfo.Width = 200;
miCelda = cells.AddValueCell(1, 1, "TU ABUELA");
sheet.AddColumnInfo(cinfo);
for (int row = 0; row <= rowCount; row++)
{
if (row == 0)
{
for (int col = 1; col <= colCount; col++)
{
cells.AddValueCell((ushort)(rowMin + row), (ushort)(colMin + col - 1), "Fld" + col);
}
}
else
{
for (int col = 1; col <= colCount; col++)
{
cells.AddValueCell((ushort)(rowMin + row), (ushort)(colMin + col - 1), row + col);
}
}
}
}
doc.Send();
Response.Flush();
Response.End();
}